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

To get the next auto-increment value from SQLServer :

This will fetch the present auto-increment value.

SELECT IDENT_CURRENT('table_name');

Next auto-increment value.

SELECT IDENT_CURRENT('table_name')+1; 

------> This will work even if you add a row and then delete it because IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.