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?

Horney

Why do you want to do this? You can't display to the user the next ID in a multi-user environment as anyone could grab it... – Salutation

What kind of SQL database? There are many varieties – Auvergne

There is no reliable way to determine the next value of an auto-increment (IDENTITY) column in SQL Server. The value is not guaranteed until after the INSERT has actually happened. – Grissel

Why do you need this value before you've decided to insert? What are you going to do with it and why? Why are you displaying it to users at all? End users shouldn't know or care what identity values have been assigned. I really have to caution you against the IDENT_CURRENT "solution" you've accepted, and encourage you to test it with multiple concurrent users and with rollbacks taking place. – Sphericity

There should be a rule against people with stupid non helpful reply's. If you can't help answer the question then just keep it to yourself. It shouldn't matter to you what this is needed for, but rather can it be done! GRRRRRR! – Drama

@Drama I think it helps to bring forth the actual problem at hand, preventing the wasting of time on a solution to a problem that may not even exist (helps to clarify). – Cymogene

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.

Tithing

This is just plain not true - the IDENTITY could have an increment of something different than 1 ! Fact is: you CANNOT get the next value for an IDENTITY column in SQL Server - there is no way to do this. The values only get set when the INSERT happens - not before. – Grissel

@marc_s: you might want to refer this article. Link .. the IDENT_CURRENT will return the last value generated by the column lets say 1510 for the ID column and then its obvious that the next generated value would be 1511 for the ID column...its not gonna be something like 45454 ... :) – Tithing

@patel.milanb: the increment vale of your IDENTITY could be five - so your next value would be 1515 (and NOT 1511!) - and also: if the next insert is rolled back, a value (like 1515) might be "lost" - and the real next value ends up being 1520. Just adding +1 to the IDENT_CURRENT is a GUESS at best..... – Grissel

+1 for your explanation ... i did not go into this detail... BUT you tell me what would be the best solution for this kind of situation if we have increment value set to 5 – Tithing

any little problem and the server jumps to the next number..if there are several problems then it jumps several numbers; sereral therefore being unpredictable so just doing )+1 is wrong – Anjelicaanjou

wee, if you can add a dummy row to check the next generated auto increment value and then compare it with the previous one and the i think you might get a auto increment value which might be different then 1 – Tithing

Also you will strike concurrency issues. If two users attempt to add employees at the same time, you will derive the same " next ID", however the second save will receive a different ID. – Choking

We came across this too as a bad idea. Even if the method to get the next value does work, there are many issues. We had an issue with high volume... In the time that the next value was retrieved and the record was inserted, another user was creating a record and the values would get mixed up. Like the others say, this is not a good method to use. – Latrishalatry

Answer 2

try this:

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

Yogini

As marc_s points out, the next auto-incremented identity cannot be guarenteed. So, if inserting into two tables, where one table has a foreign key (explicit or otherwise) to the other (codependency), then just include both operations in the same transaction (to prevent any race conditions). – Cymogene

Answer 3

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>)

Baritone

But same applies : there is no reliable way to determine the next value of an IDENTITY column. Yes - the IDENT_CURRENT plus the increment value defined is the most likely value - but it's not 100% certain to actually be the real next value. You just cannot determine the next value - until the INSERT has happened.... – Grissel

Answer 4

As for me, the best answer is:

dbcc checkident(table_name)

You will see two values (probably same) current identity value , current column value

Fronia

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.

Distributee

Answer 6

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

Isologous

Answer 7

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

Floorwalker

Answer 8

For MS SQL 2005 and greater:

Select Cast(IsNULL(last_value,seed_value) As Int) + Cast(increment_value As Int) As NextID
From sys.identity_columns
WHERE NAME = <Table_Name>

Labana

Answer 9

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.

Howland

Answer 10

 SqlConnection con = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=databasename;User ID=sa;Password=123");
 con.Open();
 SqlCommand cmd = new SqlCommand("SELECT TOP(1) UID FROM InvoiceDetails ORDER BY 1 DESC", con);

 SqlDataReader reader = cmd.ExecuteReader();

 //won't need a while since it will only retrieve one row
 while (reader.Read())
 {
     string data = reader["UID"].ToString();
     //txtuniqueno.Text = data;
     //here is your data
     //cal();
     //txtuniqueno.Text = data.ToString();
     int i = Int32.Parse(data);
     i++;
     txtuid.Text = i.ToString();
  }

Bibliogony