Getting last inserted ID into database.

Question

_{Tuesday, May 14, 2013 2:08 PM}

Hello, I need to retrieve ID of last record inserted into DB. I see, that generated adapter contains this insert command:

"INSERT INTO [dbo].[Sets] ([name]) VALUES (@name);\r\nSELECT id, name, time FROM Sets WHERE (id = SCOPE_IDENTITY())";

So, I am expecting, that I somehow receive last inserted row while executing MyAdapter.InsertCommand.ExecuteNonQuery() method wrapped by MyAdapter.Insert(val). In doc to SqlCommand.ExecuteNonQuery there is "Executes a Transact-SQL statement against the connection and returns the number of rows affected.". Where is my row then?

Thank you for answers.

All replies (2)

_{Tuesday, May 14, 2013 2:18 PM ✅Answered | 1 vote}

try

SqlConnection conn = new SqlConnection(connectionString);
conn.Open();

//Create the dataadapter
SqlDataAdapter da = new SqlDataAdapter();

//Assign the connection & Create and execute the Insert Command
da.InsertCommand = new SqlCommand("insert into table1......");
da.InsertCommand.Connection = conn;

da.InsertCommand.ExecuteNonQuery();

//Create,assign and Execute the Identity statement
da.SelectCommand = new SqlCommand("SELECT SCOPE_IDENTITY()",
da.InsertCommand.Connection);
//da.SelectCommand = new SqlCommand("SELECT @@IDENTITY",
da.InsertCommand.Connection);
int intID = Convert.ToInt32(da.SelectCommand.ExecuteScalar());
conn.Close();
return(intID);

_{Tuesday, May 14, 2013 2:21 PM}

Hi,

It likely uses ExecuteReader to return a result set rather than ExecuteNonQuery that doesn't return a resultset... Even ythough you have an INSERT Statement, it is followed by a SELECT statement sou you'll be able to read a result set exactly as if you had only done a SELECT query...

Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".