0

i have auto generate number create automatically in C#. this sample like this PPP-150500001 , PPP-150500002 => PPP- is a string never change, 15 is a year, 05 is a date, and 00001 is auto generate number. How Can i reset auto number after get a new year like PPP-160100001 .

this is my method:

public void NomerUrut()
    {
        long hitung;
        string urut;
        OracleCommand cmd = new OracleCommand();
        OracleDataReader dr;
        cmd.CommandText = @"SELECT NOPERMOHONAN FROM PERMOHONAN WHERE NOPERMOHONAN IN (SELECT MAX(NOPERMOHONAN) 
                            FROM PERMOHONAN) ORDER BY NOPERMOHONAN DESC";
        cmd.Connection = koneksi_manual.con;
        koneksi_manual.con.Open(); //open connection
        dr = cmd.ExecuteReader();
        dr.Read();
        if (dr.HasRows)
        {
            hitung = Convert.ToInt64(dr[0].ToString().Substring(dr["NOPERMOHONAN"].ToString().Length - 5, 5)) + 1;
            string joinstr = "00000" + hitung;
            DateTime dt = DateTime.Now; // take years and date in autonumber
            urut = "PPP-" + dt.ToString("yy") + dt.ToString("MM") + joinstr.Substring(joinstr.Length - 5, 5); 
            //it will show PPP-150500002, PPP-150500003, etc
            //how can i reset this autonumber after get new years like PPP-160100001
        }
        else
        {
            urut = "PPP-150500001"; // first form load will display this default autonumber
        }
        dr.Close();
        txtNoPermohonan.Text = urut; //display auto generate number in a textbox
        koneksi_manual.con.Close(); //close connection
    }

Solved.

i have update for this question and i can finish it using another query to solve my problem. i can reset it every years.. this my update code:

public static string GenerateKodeUrut()
    {
        string nomor = "";
        string date = DateTime.Now.ToString("yyyy/MM/dd").Substring(2, 2);
        DateTime dt = DateTime.Now;

        OracleCommand cmd = new OracleCommand();
        OracleDataReader dr;
        cmd.CommandText = (@"SELECT NOPERMOHONAN from PERMOHONAN 
                             where substr(NOPERMOHONAN, 5,2) ='" + date + "' ORDER BY cast(substr(NOPERMOHONAN, 9,5) as number) DESC");
        cmd.Connection = koneksi_manual.con;
        dr = cmd.ExecuteReader();
        dr.Read();
        if (dr.HasRows)
        {
            string nmrTerakhir = (dr["NOPERMOHONAN"]).ToString().Remove(0, 8);
            nomor = "PPP-" + date + dt.ToString("MM") + (Convert.ToInt32(nmrTerakhir) + 1).ToString("0000#");
        }
        else
        {
            nomor = "PPP-" + date + dt.ToString("MM") + "00001";
        }
        return nomor;
    }
Cœur
  • 37,241
  • 25
  • 195
  • 267
aminvincent
  • 553
  • 1
  • 12
  • 43
  • 1
    parse the [substring](https://msdn.microsoft.com/en-us/library/8kb3ddd4%28v=vs.110%29.aspx). check the month and then reset, What have you attempted? – lloyd May 22 '15 at 01:29
  • i wanna reset it auto generate number after i got PPP-150500500 in a last year , it will be reseted when in a new year like this PPP-160100001. can u give like a sample code based on above? – aminvincent May 22 '15 at 01:37

1 Answers1

1

you can make a flage in your db to check it && your date.

i hope that code give you my idea

        bool flage = false;
        int checkdate = Convert.ToInt16(dt.ToString("MM"));
        if (checkdate == 12) {
            flage = true;
        }
        if (flage == true && checkdate == 1) { 
        //Write Your Code Here 
            flage = false;
        }
A.Rashwan
  • 21
  • 5