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);

            try
            {
                newtix = Convert.ToInt32(myCommand.ExecuteScalar());
            }
            finally
            {
                myReader.Close();
            }

            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

Name

Email *

Message *