# [web] SQL Server IDENTITY using ADO.net

## Recommended Posts

Sortof an embaressing question I guess, i've been coding in c# and databases for quite awhile now, and i've never come across a scenerio where I need to grab the Primary Key of the last row inserted...and I'm having a tough time figuring out how to do it.. I am inserting my rows like this
string myStatement = "INSERT INTO myTable (strData, intData) VALUES ('test', 1)";
SqlCommand Command = new SqlCommand(Connection, myStatement);
Command.ExecuteNonQuery();

to try to get the IDENTITY, i've been trying stuff like
string myStatment = "SELECT @@IDENTITY FROM myTable";
Command.CommandText = myStatment;
{
//returns a DBNull...or something like that
}

So to put the question simple...How do I get the Primary Key of a row I just inserted in MSSQL using C#? Thanks, ArchG

##### Share on other sites
You do it just like that... i mean your SQL command should contain both statements:

QuerySTR = "INSERT INTO mytable (a,b,c) VALUES(x,y,z); "+ "SELECT @@IDENTITY;";

And then you get the first row of the resultset and you're done... I don't remember exactly how it was done but that's the trick. YOu can also use a Stored Procedure that returns the ID of the new inserted row, but that's beyond the scope of this post.

##### Share on other sites
Aye,
Turned out I was closing the connection and re-opening it inbetween parts...I was using the
using{}

block...and didn't even think about it.

ArchG

##### Share on other sites
Normally it's done by executing a batch of queries, the last one of which is SELECT @@IDENTITY. You can then fetch the result in the normal way (If this is the only output of the command, you can use executeScalar, IIRC)

If you insert more than one row however, the IDs of the intermediate ones may be lost. This is basically, too bad - you would need a more complex method to deal with this.

Mark

## Create an account

Register a new account

• ## Partner Spotlight

• ### Forum Statistics

• Total Topics
627651
• Total Posts
2978397

• 10
• 12
• 22
• 13
• 33