How to get last inserted id?

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

int primaryKey;

using (SqlConnection myConnection = new SqlConnection(myConnectionString))
{
   myConnection.Open();

   SqlCommand myCommand = new SqlCommand(insertSql, myConnection);

   myCommand.Parameters.AddWithValue("@UserId", newUserId);
   myCommand.Parameters.AddWithValue("@GameId", newGameId);

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

   myConnection.Close();
}

This will work.

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

I had the same need and found this answer ..

This creates a record in the company table (comp), it the grabs the auto ID created on the company table and drops that into a Staff table (staff) so the 2 tables can be linked, MANY staff to ONE company. It works on my SQL 2008 DB, should work on SQL 2005 and above.

===========================

CREATE PROCEDURE [dbo].[InsertNewCompanyAndStaffDetails]

 @comp_name varchar(55) = 'Big Company',

 @comp_regno nchar(8) = '12345678',

 @comp_email nvarchar(50) = '[email protected]',

 @recID INT OUTPUT

-- The '@recID' is used to hold the Company auto generated ID number that we are about to grab

AS
 Begin

  SET NOCOUNT ON

  DECLARE @tableVar TABLE (tempID INT)

-- The line above is used to create a tempory table to hold the auto generated ID number for later use. It has only one field 'tempID' and its type INT is the same as the '@recID'.

  INSERT INTO comp(comp_name, comp_regno, comp_email) 

  OUTPUT inserted.comp_id INTO @tableVar

-- The 'OUTPUT inserted.' line above is used to grab data out of any field in the record it is creating right now. This data we want is the ID autonumber. So make sure it says the correct field name for your table, mine is 'comp_id'. This is then dropped into the tempory table we created earlier.

  VALUES (@comp_name, @comp_regno, @comp_email)

  SET @recID = (SELECT tempID FROM @tableVar)

-- The line above is used to search the tempory table we created earlier where the ID we need is saved. Since there is only one record in this tempory table, and only one field, it will only select the ID number you need and drop it into '@recID'. '@recID' now has the ID number you want and you can use it how you want like i have used it below.

  INSERT INTO staff(Staff_comp_id) 
  VALUES (@recID)

 End

-- So there you go. You can actually grab what ever you want in the 'OUTPUT inserted.WhatEverFieldNameYouWant' line and create what fields you want in your tempory table and access it to use how ever you want.

I was looking for something like this for ages, with this detailed break down, I hope this helps.

You can create a SqlCommand with CommandText equal to

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

and execute int id = (int)command.ExecuteScalar.

This MSDN article will give you some additional techniques.