Auto Increment number in textbox using c#

See more:

SQL-Server-2005

C#

.NET

, +

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);
            con.Open();
            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";
                }
                else
                {
                    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 !!!