Sign in to follow this  

[web] SQL Server IDENTITY using ADO.net

This topic is 3807 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

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;
SqlDataReader reader = Command.ExecuteReader();
if (reader.HasRows) //Returns True
{
reader.Read(); 
//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 this post


Link to post
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 this post


Link to 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.

Thanks for your reply!
ArchG

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

This topic is 3807 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this