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

[John6272]

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.

Continue reading...