anthonypliu anthonypliu - 6 months ago 25
SQL Question

How to get last inserted id?

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?

gbn gbn
Answer

For SQL Server 2005+, if there is no insert trigger, then change the insert statement (all one line, split for clarity here) to this

INSERT INTO aspnet_GameProfiles(UserId,GameId)
OUTPUT INSERTED.ID
VALUES(@UserId, @GameId)

For SQL Server 2000, or if there is an insert trigger:

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

And then

 Int32 newId = (Int32) myCommand.ExecuteScalar();