Click here to Skip to main content
15,667,847 members
Please Sign up or sign in to vote.
4.00/5 (2 votes)
See more: , +
I am working on windows application and want to auto generate transaction number.
I wrote the following code:-
private void Form1_Load(object sender, EventArgs e)
            int a;
            string cnstr = //Your connection string;
            SqlConnection con = new SqlConnection(cnstr);
            string query = "Select Max(Trans_No) from Auto_Number";
            SqlCommand cmd = new SqlCommand(query,con);            
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
                string val = dr[0].ToString();
                if (val == "")
                    textBox1.Text = "1";
                    a = Convert.ToInt32(dr[0].ToString());
                    a = a + 1;
                    textBox1.Text = a.ToString();

Sql Table Query is
Create Table Auto_Number
	Trans_No varchar(10)
	primary key (Trans_No)

I want to auto increment the trans number and show it in textbox. the above code will generate the number and display in textbox and the number generated is saved on button click. It works fine till the trans number is 9 and when the trans number 10 is generated and when I click on save it is saved, but in sql table I saw trans number 10 is saved at 2nd position or Row. and then the number is not generated.
I run the project in debug mode and the value returned from query was 9.
So I fired the same query as mentioned in above code in SQL server i.e.
Select Max(Trans_No) from Auto_Number
and the answer was same i.e 9.

Any help please !!!

Thanks in Advance !!!
BC @ CV 7-Dec-12 12:27pm    
Why are you making your Trans_No a varchar instead of an int?
Adam R Harris 7-Dec-12 13:07pm    
Why are you not just using AutoId's for the Trans_No? Let SQL worry about generating your ID's and there is very little value in the user seeing it before the change is committed. No to mention you will run into issues if you have multiple clients using the application at the same time with duplicated ids.

If the users need to see the transaction ids then i would just show them in a dialog after they click save.
Rohit Shrivastava 7-Dec-12 15:57pm    
I agree to Adam, otherwise in your approach you need to change data type of Trans_NO to int / Convert it to int while applying max().
Ank_ush 8-Dec-12 0:13am    
Thanks for the idea of converting from varchar to int while applying max().
Jibesh 7-Dec-12 20:50pm    
I second the usage of varchar to Int. finding max(varchar) will return a different result when using max(int) - max(varchar) works on the literals i guess. so it gives a wrong value to you. use max(int) instead

I changed the query to:-
select Max(Cast(Trans_No as Int)) from Auto_Number
Share this answer
Ajesh1986 22-May-14 11:31am    
why have you changed the query?......what is that cast for.....can you plz tell me..
select top 1 Trans_No from Auto_Number order by Trans_No Desc
Share this answer
Ank_ush 7-Dec-12 23:49pm    
I have saved 1 to 10 in trans_no ans I fired your query and the answer is 9 instead of 10. Can u plz check....
What I Figured out when Datatype of Trans_No is Varchar then after 9 it is inserting duplicate value means everytime when i'm saving the Value then instead of (10+1)=11
it is saving 10 instead of 11

then i just Changed the Trans_No Datatype To Int then it is Working Fine Dude

So U Must try to Change The Datatype Hope This Thing will Work as Mine is Working :)
Share this answer
Ank_ush 8-Dec-12 4:32am    
I changed the Datatype from varchar to int while using MAX(). You can see solution 2.
Member 12178247 17-Aug-16 3:39am    
But if you are using linq then how you handle null reference exception in this context: int maxId = 0;
maxId = context.Students.Max(r => r.Id)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900