how to get the next autoincrement value in sql

Question

I am creating a winform application in c#.and using sql database.

I have one table, employee_master, which has columns like Id, name, address and phone no. Id is auto increment and all other datatypes are varchar.

I am using this code to get the next auto increment value:

string s = "select max(id) as Id from Employee_Master";
SqlCommand cmd = new SqlCommand(s, obj.con);
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
int i = Convert.ToInt16(dr["Id"].ToString());
txtId.Text = (i + 1).ToString();

I am displaying on a textBox.

But when last row from table is deleted, still I get that value which is recently deleted in textbox

How should I get the next autoincrement value?

Answer 1

the max(id) will get you maximum number in the list pf employee_master

e.g. id = 10, 20, 100 so max will get you 100

But when you delete the record it must have been not 100

So you still get 100 back

One important reason for me to say this might be the issue because you are not using order by id in your query

Answer 2

If you are using Microsoft SQL Server. Use this statement to get current identity value of table. Then add your seed value which you have specified at time of designing table if you want to get next id.

SELECT IDENT_CURRENT(<TableName>)

Answer 3

Just a thought, if what you wanted was the last auto-number that you inserted on an already open connection try using:

SELECT @@IDENTITY FROM...

from that connection. That's the best way to keep track of what has just happened on a given connection and avoids race conditions w/ other connections. Getting the maximum identity is not generally feasible.

Answer 4

try this:

SELECT IDENT_CURRENT('tbl_name') + IDENT_INCR('tbl_name');

Answer 5

When you delete a row from the table the next number will stay the same as it doesnt decrement in any way.

So if you have 100 rows and you deleted row 100. You would have 99 rows but the next number is still going to be 101.

Answer 6

select isnull((max(AddressID)+1),1) from AddressDetails