Retrieve Identifier from database insert

Ben Gannaway

I need to retrieve the identifier ID from an insert query so that I can associate it with another table. I'm using the scope_identity() method, but can't find out how to assign that to a variable so it can be used. So far I have:

 Console.WriteLine("Opening database");
                SqlCommand myCommand = new SqlCommand("INSERT INTO entries(text, name, screen_name, user_id, posi_score)"+"VALUES(@txt,@nm,@scrNm,@uid,@posi_score)select scope_identity() as @identity", myConnection);
                myCommand.Parameters.AddWithValue("@txt", txt);
                myCommand.Parameters.AddWithValue("@nm", nm);
                myCommand.Parameters.AddWithValue("@scrNm", scrNm);
                myCommand.Parameters.AddWithValue("@uid", uid);
                myCommand.Parameters.AddWithValue("@posi_score",posiScore);

                myConnection.Open();
                myCommand.ExecuteNonQuery();


                myConnection.Close();
                Console.WriteLine("Closing database connection");

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kirtan Patel17y ago
Accepted answer
Friend,

Here is Simple One

SqlCommand comm = new SqlCommand("select IDENT_CURRENT('table1')", con);
int LastInsertedValue = Convert.ToInt32(comm.ExecuteScalar());

Or If your Column is Integral then You can Also Use Max to get Last value

SqlCommand comm = new SqlCommand("select max(id_column) from tablename", con);
int LastInsertedValue = Convert.ToInt32(comm.ExecuteScalar());
Ben Gannaway17y ago
Perfect. Thanks a lot

Ben Gannaway17y ago

Ok now I have:

 int entryId;
                Console.WriteLine("Opening database");
                SqlCommand myCommand = new SqlCommand("INSERT INTO entries(text, name, screen_name, user_id, posi_score)"+"VALUES(@txt,@nm,@scrNm,@uid,@posi_score)select scope_identity() as @identity", myConnection);
                myCommand.Parameters.AddWithValue("@txt", txt);
                myCommand.Parameters.AddWithValue("@nm", nm);
                myCommand.Parameters.AddWithValue("@scrNm", scrNm);
                myCommand.Parameters.AddWithValue("@uid", uid);
                myCommand.Parameters.AddWithValue("@posi_score",posiScore);
                myCommand.Parameters.Add("@identity", SqlDbType.Int);
                myCommand.Parameters["@identity"].Direction = ParameterDirection.ReturnValue;

                
                
                
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                entryId = (int)myCommand.Parameters["@identity"].Value;

But I'm getting a syntax error close to @identity

Ben Gannaway17y ago
Many thanks Raji
Rajeswari nathan17y ago
Just add one parameter in the SP
@ValueOut int OUTPUT
After inserting the data assign th identity in to that Out put parameter.
Like wise add one output parameter for the SP in C# code.
you can get the value..
as follows
aSqlServiceCommandL.Parameters.Add("@Ret", SqlDbType.Int);
aSqlServiceCommandL.Parameters["@Ret"].Direction = ParameterDirection.ReturnValue;
aSqlServiceCommandL.ExecuteNonQuery();
int nServiceIdL = (int)aSqlServiceCommandL.Parameters["@Ret"].Value;
Ben Gannaway17y ago
Thanks for your reply. How can I create an output parameter?
Rajeswari nathan17y ago
Yes, you are correct. use the scope_identity.
You can create one out put parameter in the SP and access the identity using that output parameter