How to retrieve the Identity (@@IDENTITY) of a record you just inserted into a table.
Posted
by Edward Boyle
on Techlexic
See other posts from Techlexic
or by Edward Boyle
Published on Wed, 24 Nov 2010 16:43:37 +0000
Indexed on
2010/12/06
17:00 UTC
Read the original article
Hit count: 514
SELECT @@IDENTITY will retrive that last generated @@IDENTITY from the current connection.
int thisid = (int)cmd.ExecuteScalar("SELECT @@IDENTITY",conn);
If there is another write in another connection you do not have to worry. Again, @@IDENTITY will retrieve last generated @@IDENTITY from the current connection. Null if no @@IDENTITY was generated on this connection.
Another method is to append ;SELECT @@IDENTITY to your SQL Insert and use ExecuteScalar()
What was:
INSERT INTO STUFF(Field) VALUES(1) ... cmd.ExecuteNonQuery();
Becomes:
string cstring= "INSERT INTO STUFF(Field) VALUES(1);SELECT @@IDENTITY"; int thisid = (int)cmd.ExecuteScalar(cstring, conn);
In SQL Server Compact Edition you must send your commands in one at a time, you can not append
;SELECT @@IDENTITY to an insert.© Techlexic or respective owner
