none
How to add custom invoice number like "INNK-5001" with auto increment using Sqlite database in C# Windows Application RRS feed

  • Question

  • I'm using SQLITE Database. I want to create a custom invoice number like this "INNK-5001" with auto increment INNK-5002, INNK-5003... and so on. I write below code for this purpose. Below code working correctly only if i use only number like "5000" but when i use "INNK-5000" i got Error. Please help me how can i get this number "INNK-5000" with auto increment using Sqlite Database. This my code...

    // To Create Sqlite Database

    public void CreateDB()
        {
            if (!File.Exists("customid.db"))
            {
                SQLiteConnection.CreateFile("customid.db");
                using (SQLiteConnection conn = new SQLiteConnection("Data Source=customid.db;Version=3;"))
                {
                    string commandstring = "CREATE TABLE cusid (Id INTEGER PRIMARY KEY NOT NULL, FirstName NVARCHAR(250), LastName NVARCHAR(250))";
                    using (SQLiteCommand cmd = new SQLiteCommand(commandstring, conn))
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }

    // For Custom Invoice Number

    public void CustomId()
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection("Data Source=customid.db;Version=3;"))
                {
                    string CommandText = "SELECT Id FROM cusid";
                    using (SQLiteDataAdapter sda = new SQLiteDataAdapter(CommandText, conn))
                    {
                        conn.Open();
                        DataTable datat = new DataTable();
                        sda.Fill(datat);
    
                        if (datat.Rows.Count < 1)
                        {
                            textBox1.Text = "INNK-5000";
                        }
                        else
                        {
                            using (SQLiteConnection conn1 = new SQLiteConnection("Data Source=customid.db;Version=3;"))
                            {
                                string CommandString = "SELECT MAX(Id) FROM cusid";
                                using (SQLiteCommand cmd = new SQLiteCommand(CommandString, conn))
                                {
                                    conn1.Open();
                                    int a = Convert.ToInt32(cmd.ExecuteScalar());
                                    a = a + 1;
                                    textBox1.Text = a.ToString();
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

    // For Save a Record into Database

    private void savebtn_Click(object sender, EventArgs e)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection("Data Source=customid.db;Version=3;"))
                {
                    string CommandText = "INSERT INTO cusid ([Id], [FirstName], [LastName]) VALUES (@id,@firstname,@lastname)";
                    using (SQLiteCommand cmd = new SQLiteCommand(CommandText, conn))
                    {
                        cmd.Parameters.AddWithValue("@id", textBox1.Text);
                        cmd.Parameters.AddWithValue("@firstname", textBox2.Text);
                        cmd.Parameters.AddWithValue("@lastname", textBox3.Text);
    
                        conn.Open();
                        int a = cmd.ExecuteNonQuery();
                        if (a > 0)
                        {
                            MessageBox.Show("Data Saved!!");
                            CustomId();
                            BindDataGridView();
                        }
                        else
                        {
                            MessageBox.Show("Not Saved!!");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

    Please Help me... Thank You.

    Tuesday, November 20, 2018 7:17 PM

Answers

  • Hi John6272,

    You can query data from database order by Id, then get the last record, using string.split to get value like 5000.

    You can modify some code according to your situation.

      SQLiteConnection con;
           // SqlConnection con;
            //SqlCommand cmd;
            SQLiteCommand cmd;
            private void button1_Click(object sender, EventArgs e)
            {
                DataTable dt = new DataTable();
                using (con=new SQLiteConnection(str))
                {
                    con.Open();
                    string sql = "select * from test13 order by Id";
                    using (cmd=new SQLiteCommand(sql,con))
                    {
                        SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                        adapter.Fill(dt);
    
                    }
                }
                if(dt.Rows.Count<1)
                {
                    textBox1.Text = "INNK-5000";
                }
                else
                {
                    int count = dt.Rows.Count;
                    string oldId = dt.Rows[count - 1]["Id"].ToString();
                    string[] array = oldId.Split('-');
                    Int32 value =Convert.ToInt32(array[1]) + 1;
                    textBox1.Text = "INNK" + "-" + value.ToString();
                }
            }

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, November 21, 2018 6:29 AM

All replies

  • Hi 

    Use this function it may help you ..... it's in VB but you can add some modifications

    Please take care ,it generates random numbers and you have to make data type of the "ID" in database 

    as VarChar 

      Private Sub auto()
    
            txtVendorID.Text = "INNK" & GetUniqueKey(6)
        End Sub
        Public Shared Function GetUniqueKey(ByVal maxSize As Integer) As String
            Dim chars As Char() = New Char(61) {}
            chars = "123456789".ToCharArray()
            Dim data As Byte() = New Byte(0) {}
            Dim crypto As New RNGCryptoServiceProvider()
            crypto.GetNonZeroBytes(data)
            data = New Byte(maxSize - 1) {}
            crypto.GetNonZeroBytes(data)
            Dim result As New StringBuilder(maxSize)
            For Each b As Byte In data
                result.Append(chars(b Mod (chars.Length)))
            Next
            Return result.ToString()
        End Function

     And here is a function that make auto increment ....

    Private Function GenerateID(TableName As String, ColumnName As String) As String
            conn = New SqlLiteConnection(cs)
            Dim value As String = "0"
            Dim sql As String = String.Format("SELECT TOP 1 {0} FROM {1} ORDER BY {2} DESC", ColumnName, TableName, ColumnName)
            Try
                ' Fetch the latest ID from the database
                conn.Open()
                cmd = New SqlLiteCommand(sql, conn)
                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                If rdr.HasRows Then
                    rdr.Read()
                    value = rdr.Item(ColumnName)
                End If
                rdr.Close()
                ' Increase the ID by 1
                value += 1
    
               
                If conn.State = ConnectionState.Open Then
                    conn.Close()
                End If
                value = "0"
            End Try
            Return value
        End Function

    You can put

    "NNK-" + GenerateID("tablename","columnname") and this function will creates

    "NNK-1" ,"NNK-2" and so on

     ...but it creates numbers from "1" ,i think you can start from 500 by changing "0" to "500"

     you may need to experiment this function with data type varchar because i don't prefer to use it like you want .... i think product code must be an integer only .. Else use the function number one that creates random numbers with string 

    hope it helps


    Regards From Amr_Aly





    • Edited by Amr_Aly Tuesday, November 20, 2018 10:05 PM
    Tuesday, November 20, 2018 7:49 PM
  • Please post questions related to third party products like SQLite in their forums. This question seems related to inserting data into SQLite so it makes sense over there instead of the C# forums.

    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, November 20, 2018 9:54 PM
    Moderator
  • Hi John6272,

    You can query data from database order by Id, then get the last record, using string.split to get value like 5000.

    You can modify some code according to your situation.

      SQLiteConnection con;
           // SqlConnection con;
            //SqlCommand cmd;
            SQLiteCommand cmd;
            private void button1_Click(object sender, EventArgs e)
            {
                DataTable dt = new DataTable();
                using (con=new SQLiteConnection(str))
                {
                    con.Open();
                    string sql = "select * from test13 order by Id";
                    using (cmd=new SQLiteCommand(sql,con))
                    {
                        SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                        adapter.Fill(dt);
    
                    }
                }
                if(dt.Rows.Count<1)
                {
                    textBox1.Text = "INNK-5000";
                }
                else
                {
                    int count = dt.Rows.Count;
                    string oldId = dt.Rows[count - 1]["Id"].ToString();
                    string[] array = oldId.Split('-');
                    Int32 value =Convert.ToInt32(array[1]) + 1;
                    textBox1.Text = "INNK" + "-" + value.ToString();
                }
            }

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, November 21, 2018 6:29 AM
  • Thank you so much your code solve my problem. your code working correctly as i want. Thanks.
    Wednesday, November 21, 2018 8:16 AM