Share This!

Sunday, September 23, 2012

Getting the Identity Of Inserted Row in Visual C#

This works to return the identity of an inserted row.  The highlighted portions are the important parts.

 private int insertnew()
            int newtix = 0;
            string SQL1 = "insert into delivery (date) values (GETDATE()); SELECT CAST(scope_identity() AS int)";
            SqlCommand myCommand = new SqlCommand(SQL1, myConnection);

                newtix = Convert.ToInt32(myCommand.ExecuteScalar());

            return newtix;
Note that
  1. there are 2 SQL commands in the one SQL string.
  2. I had to cast the Scope_Identity as an int in the SQL or it would not read.
  3. ExecuteScalar reads the FIRST column in the FIRST row only, but if there were a compound key, or multiple rows inserted, ExecuteReader can be used instead.

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

No comments:

Post a Comment

Contact Us


Email *

Message *