CODEWITHSUNDEEP
c#sqlasp.netsql-server
Siboniso Mabuza
Siboniso Mabuza

Reputation: 3

Auto generating invoice number

I'm trying to automatically generate invoice number using the last invoice number on database table. My sample invoice number is 'INV0000' Here is what I tried

SqlConnection scon1 = new SqlConnection(mycon1);
String myquery1 = "select max(Inv_num) from [dbo].[tblInv_info]";
SqlCommand cmd1 = new SqlCommand();
cmd1.CommandText = myquery1;
cmd1.Connection = con;
SqlDataAdapter da1 = new SqlDataAdapter();
da1.SelectCommand = cmd1;
DataSet ds1 = new DataSet();
da1.Fill(ds1);
txtInvoiceN.Text = ds1.Tables[0].Rows[0][0].ToString();
string a;
a = txtInvoiceN.Text;
a = a  + 1;
txtInvoiceN.Text = a.ToString();
con.Close();

The above code works fine but instead of invoice number being INV0001 the output becomes INV00001. I tried to change SQL query to:

SELECT MAX(CONVERT(INT, RIGHT(Inv_Num, LEN(Inv_Num)-3))+1) FROM tblInv_info;

The output is correct but the first three letters are missing, any help will be appreciated.

Upvotes: 0

Views: 2244

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I cannot really support what you are trying to do. If you want an incremental number, then you should use an identity column or perhaps a sequence. Attempting to assign the value this way can lead to problems -- notably, two different threads can produce the same number.

But your question is more about formatting values. You can do all the manipulation in the database. Here is one way to produce the number:

select concat('INV', format(convert(int, stuff(max(Inv_num), 1, 3, '')) + 1, '0000'))
from [dbo].[tblInv_info]

This extracts the maximum number, removes the first three characters, converts to an integer, adds 1 and reformats in your desired way.

Upvotes: 1

Related Questions