How to get last inserted id?

Question

I have this code: string insertSql = "INSERT INTO aspnet_GameProfiles(UserId,GameId) VALUES(@UserId, @GameId)"; using (SqlConnection myConnection = new SqlConnection(myConnectionString)) { myConnection.Open(); SqlCommand myCommand = new SqlCommand(insertSql, myConnection); myCommand.Parameters.AddWithValue("@UserId", newUserId); myCommand.Parameters.AddWithValue("@GameId", newGameId); myCommand.ExecuteNonQuery(); myConnection.Close(); } When I insert into this table, I have an auto_increment int primary key column called `GamesProfileId`, how can i get the last inserted one after this so I can use that id to insert into another table?

Answer 1

Two things:

1. You first need to return the value of the last inserted ID. You can do this by using the SCOPE_IDENTITY(). Add this to the last of the SQL statement:

"INSERT INTO aspnet_GameProfiles(UserId,GameId) VALUES(@UserId, @GameId); SELECT SCOPE_IDENTITY()";

2. You then need to change from ExecuteNonQuery() to ExecuteScalar(). ExecuteNonQuery() only returns back the number of rows affected, which is not what you want in this case.

myCommand.ExecuteScalar();