How to pass a SCOPE_IDENTITY back to my C# app

Does anyone know the easiest way to get the last inserted id from my table back to my C# application so I can use it in another query on another page.

I have been search for a solution and everything I read seems to point to using SCOPE_IDENTITY (I am using SQL SVR 2005). I am just not sure how to pass it back to my code( C# 2.0)

Thanks!

Just add

SELECT ID = SCOPE_IDENTITY()

to your command or stored procedure, and call it by


(Insert all of your normal connection and command logic here Create connection;
create command; add parameters to command; open connection.. )
int myReturnedID = int.Parse(YOURCOMMAND.ExecuteScalar().ToString());

You need to remember that that method requires you to execute the command within the same scope using a transaction.

It is probably easier to include it as part of your command:


cmd.CommandText="INSERT INTO Foo (Bar) ('val'); DECLARE @ID INT; SET @ID=SCOPE_IDENTITY()";
SqlParameter p=new SqlParameter();
p.Name="@ID";
p.Size=4;
p.Direction=ParameterDirection.Output;
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();
int idOut=(int)p.Value;

That adds an output parameter (@ID) to your command, then executes it and recovers the value of the parameter.

Thanks for your replies. First I should have clairfied that I am using a stored procedure to do the insert. So I am not sure how I can capture it after the insert from the SP.

Ok, then it is not too hard. Basically you do what I did. Create a proc:


CREATE PROC Foo_Insert
(
     @name VARCHAR(50),
     @id INT OUTPUT
)

AS 
    INSERT INTO Foo (Bar) VALUES (@name)
    SET @id=SCOPE_IDENTITY()

And then call it like:


cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandText="Foo_Insert";
SqlParameter p=new SqlParameter();
p.Name="@ID";
p.Size=4;
p.Direction=ParameterDirection.Output;
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();
int idOut=(int)p.Value;

Thank you for the code sample, but I must be missing something here b/c nothing I do seems to get this to work!

I added this line at the end of my stored procedure
SET @tim_id=SCOPE_IDENTITYCOLOR=#000000[/COLOR]

I modified the code to work for my page.


[SIZE=2][COLOR=#008000]// Time collect the data and submit it to the database[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]void[/COLOR][/SIZE][SIZE=2] submitButton_Click([/SIZE][SIZE=2][COLOR=#0000ff]object[/COLOR][/SIZE][SIZE=2] sender, [/SIZE][SIZE=2][COLOR=#2b91af]EventArgs[/COLOR][/SIZE][SIZE=2] e)[/SIZE]
[SIZE=2]{[/SIZE]
[SIZE=2][COLOR=#008000]// Define Data Objects[/COLOR][/SIZE]
[SIZE=2][COLOR=#2b91af]SqlConnection[/COLOR][/SIZE][SIZE=2] conn;[/SIZE]
[SIZE=2][COLOR=#2b91af]SqlCommand[/COLOR][/SIZE][SIZE=2] comm;[/SIZE]
[SIZE=2][COLOR=#2b91af]SqlParameter[/COLOR][/SIZE][SIZE=2] p = [/SIZE][SIZE=2][COLOR=#0000ff]new[/COLOR][/SIZE][SIZE=2][COLOR=#2b91af]SqlParameter[/COLOR][/SIZE][SIZE=2](); ;[/SIZE]
[SIZE=2]p.Value = [/SIZE][SIZE=2][COLOR=#a31515]"@tim_id"[/COLOR][/SIZE][SIZE=2];[/SIZE]
[SIZE=2]p.Size = 4;[/SIZE]
[SIZE=2]p.Direction = [/SIZE][SIZE=2][COLOR=#2b91af]ParameterDirection[/COLOR][/SIZE][SIZE=2].Output;[/SIZE]
[SIZE=2][COLOR=#008000]// Read connection string in from web.config file[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]string[/COLOR][/SIZE][SIZE=2] connectionString = [/SIZE][SIZE=2][COLOR=#2b91af]ConfigurationManager[/COLOR][/SIZE][SIZE=2].ConnectionStrings[[/SIZE][SIZE=2][COLOR=#a31515]"ConnectionString"[/COLOR][/SIZE][SIZE=2]].ConnectionString;[/SIZE]
[SIZE=2][COLOR=#008000]// Initialize connection[/COLOR][/SIZE]
[SIZE=2]conn = [/SIZE][SIZE=2][COLOR=#0000ff]new[/COLOR][/SIZE][SIZE=2][COLOR=#2b91af]SqlConnection[/COLOR][/SIZE][SIZE=2](connectionString);[/SIZE]
[SIZE=2][COLOR=#008000]// Create command[/COLOR][/SIZE]
[SIZE=2]comm = [/SIZE][SIZE=2][COLOR=#0000ff]new[/COLOR][/SIZE][SIZE=2][COLOR=#2b91af]SqlCommand[/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#a31515]"STORED_PROC"[/COLOR][/SIZE][SIZE=2], conn);[/SIZE]
[SIZE=2]comm.CommandType = [/SIZE][SIZE=2][COLOR=#2b91af]CommandType[/COLOR][/SIZE][SIZE=2].StoredProcedure;[/SIZE]
[SIZE=2][COLOR=#008000]// Add command params[/COLOR][/SIZE]
[SIZE=2]comm.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@dateOfTI"[/COLOR][/SIZE][SIZE=2], System.Data.[/SIZE][SIZE=2][COLOR=#2b91af]SqlDbType[/COLOR][/SIZE][SIZE=2].DateTime, 10);[/SIZE]
[SIZE=2]comm.Parameters[[/SIZE][SIZE=2][COLOR=#a31515]"@dateOfTI"[/COLOR][/SIZE][SIZE=2]].Value = dateOfTITextBox.Text;[/SIZE]
[SIZE=2]comm.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@pocName"[/COLOR][/SIZE][SIZE=2], System.Data.[/SIZE][SIZE=2][COLOR=#2b91af]SqlDbType[/COLOR][/SIZE][SIZE=2].VarChar, 30);[/SIZE]
[SIZE=2]comm.Parameters[[/SIZE][SIZE=2][COLOR=#a31515]"@pocName"[/COLOR][/SIZE][SIZE=2]].Value = pocNameTextBox.Text;[/SIZE]
[SIZE=2]comm.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@pocPhone"[/COLOR][/SIZE][SIZE=2], System.Data.[/SIZE][SIZE=2][COLOR=#2b91af]SqlDbType[/COLOR][/SIZE][SIZE=2].VarChar, 14);[/SIZE]
[SIZE=2]comm.Parameters[[/SIZE][SIZE=2][COLOR=#a31515]"@pocPhone"[/COLOR][/SIZE][SIZE=2]].Value = pocPhoneTextBox.Text;[/SIZE]
[SIZE=2]comm.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@mdl_id"[/COLOR][/SIZE][SIZE=2], System.Data.[/SIZE][SIZE=2][COLOR=#2b91af]SqlDbType[/COLOR][/SIZE][SIZE=2].Int);[/SIZE]
[SIZE=2]comm.Parameters[[/SIZE][SIZE=2][COLOR=#a31515]"@mdl_id"[/COLOR][/SIZE][SIZE=2]].Value = mdl_idLabel.Text;[/SIZE]
[SIZE=2]comm.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@workOrderNumber"[/COLOR][/SIZE][SIZE=2], System.Data.[/SIZE][SIZE=2][COLOR=#2b91af]SqlDbType[/COLOR][/SIZE][SIZE=2].VarChar, 30);[/SIZE]
[SIZE=2]comm.Parameters[[/SIZE][SIZE=2][COLOR=#a31515]"@workOrderNumber"[/COLOR][/SIZE][SIZE=2]].Value = workOrderNumberTextBox.Text;[/SIZE]
[SIZE=2]comm.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@genAhours"[/COLOR][/SIZE][SIZE=2], System.Data.[/SIZE][SIZE=2][COLOR=#2b91af]SqlDbType[/COLOR][/SIZE][SIZE=2].Int);[/SIZE]
[SIZE=2]comm.Parameters[[/SIZE][SIZE=2][COLOR=#a31515]"@genAhours"[/COLOR][/SIZE][SIZE=2]].Value = genAhoursTextBox.Text;[/SIZE]
[SIZE=2]comm.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@genBhours"[/COLOR][/SIZE][SIZE=2], System.Data.[/SIZE][SIZE=2][COLOR=#2b91af]SqlDbType[/COLOR][/SIZE][SIZE=2].Int);[/SIZE]
[SIZE=2]comm.Parameters[[/SIZE][SIZE=2][COLOR=#a31515]"@genBhours"[/COLOR][/SIZE][SIZE=2]].Value = genBhoursTextBox.Text;[/SIZE]
[SIZE=2]comm.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@tim_id"[/COLOR][/SIZE][SIZE=2], System.Data.[/SIZE][SIZE=2][COLOR=#2b91af]SqlDbType[/COLOR][/SIZE][SIZE=2].Int);[/SIZE]
[SIZE=2]comm.Parameters[[/SIZE][SIZE=2][COLOR=#a31515]"@tim_id"[/COLOR][/SIZE][SIZE=2]].Value = p.Value;[/SIZE]
 
 
 
 
[SIZE=2][COLOR=#0000ff]try[/COLOR][/SIZE]
[SIZE=2]{[/SIZE]
[SIZE=2]conn.Open();[/SIZE]
[SIZE=2]comm.ExecuteNonQuery();[/SIZE]
[SIZE=2][COLOR=#0000ff]int[/COLOR][/SIZE][SIZE=2] tim_id = p.Value;[/SIZE]
[SIZE=2][COLOR=#008000]// Move to the next page where users will enter deficiecies for parts[/COLOR][/SIZE]
[SIZE=2]Response.Redirect([/SIZE][SIZE=2][COLOR=#a31515]"next_page.aspx?tim_id="[/COLOR][/SIZE][SIZE=2] + tim_id);[/SIZE]

hope its just something I overlooked. Thanks for your help with this.

You need to set the ParameterDirection property on the @tim_id parameter to ParameterDirection.Output.

Ok, I finally got this sucka working!

Hope this can help someone else after spending the last 14 hours trying get it work!yAy
Thanks for your help wwb_99.

This is what I did:

Here is my sp:


[SIZE=2]create procedure getid[/SIZE]
[SIZE=2]([/SIZE]
[SIZE=2]@tiDate datetime,[/SIZE]
[SIZE=2]@tiHours int[/SIZE]
[SIZE=2]@tim_id [/SIZE][SIZE=2][COLOR=#0000ff]int OUTPUT[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff])[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]AS[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]insert into tiTable(tiDate, tiHours, tim_id) [/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]values(@tiDate, @tiHours)[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]SET [/COLOR][/SIZE][SIZE=2][COLOR=#000000]@tim_id = [/COLOR][COLOR=#0000ff]scope_identity[/COLOR][COLOR=#000000]()[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]RETURN[/COLOR][/SIZE]

C#


[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#2b91af]SqlParameter[/COLOR][/SIZE][SIZE=2][COLOR=#000000] pID;[/COLOR][/SIZE]
[SIZE=2][SIZE=2][COLOR=#0000ff]string[/COLOR][/SIZE][SIZE=2] connectionString = [/SIZE][SIZE=2][COLOR=#2b91af]ConfigurationManager[/COLOR][/SIZE][SIZE=2].ConnectionStrings[[/SIZE][SIZE=2][COLOR=#a31515]"ConnectionString"[/COLOR][/SIZE][SIZE=2]].ConnectionString;[/SIZE]
[SIZE=2][COLOR=#008000]// Initialize connection[/COLOR][/SIZE]
[SIZE=2]conn = [/SIZE][SIZE=2][COLOR=#0000ff]new[/COLOR][/SIZE][SIZE=2][COLOR=#2b91af]SqlConnection[/COLOR][/SIZE][SIZE=2](connectionString);[/SIZE]
[SIZE=2][COLOR=#008000]// Create command[/COLOR][/SIZE]
[SIZE=2]comm = [/SIZE][SIZE=2][COLOR=#0000ff]new[/COLOR][/SIZE][SIZE=2][COLOR=#2b91af]SqlCommand[/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#a31515]"getID"[/COLOR][/SIZE][SIZE=2], conn);[/SIZE]
[SIZE=2]comm.CommandType = [/SIZE][SIZE=2][COLOR=#2b91af]CommandType[/COLOR][/SIZE][SIZE=2].StoredProcedure;[/SIZE]
[SIZE=2][COLOR=#008000]// Add command params[/COLOR][/SIZE]
[SIZE=2]comm.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@dateOfTI"[/COLOR][/SIZE][SIZE=2], System.Data.[/SIZE][SIZE=2][COLOR=#2b91af]SqlDbType[/COLOR][/SIZE][SIZE=2].DateTime);[/SIZE]
[SIZE=2]comm.Parameters[[/SIZE][SIZE=2][COLOR=#a31515]"@dateOfTI"[/COLOR][/SIZE][SIZE=2]].Value = dateOfTITextBox.Text;[/SIZE]
[SIZE=2]comm.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@mdl_id"[/COLOR][/SIZE][SIZE=2], System.Data.[/SIZE][SIZE=2][COLOR=#2b91af]SqlDbType[/COLOR][/SIZE][SIZE=2].Int);[/SIZE]
[SIZE=2]comm.Parameters[[/SIZE][SIZE=2][COLOR=#a31515]"@mdl_id"[/COLOR][/SIZE][SIZE=2]].Value = mdl_idLabel.Text;[/SIZE]
[SIZE=2]comm.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@pocName"[/COLOR][/SIZE][SIZE=2], System.Data.[/SIZE][SIZE=2][COLOR=#2b91af]SqlDbType[/COLOR][/SIZE][SIZE=2].VarChar, 30);[/SIZE]
[SIZE=2]comm.Parameters[[/SIZE][SIZE=2][COLOR=#a31515]"@pocName"[/COLOR][/SIZE][SIZE=2]].Value = pocNameTextBox.Text;[/SIZE]
[SIZE=2]comm.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@pocPhone"[/COLOR][/SIZE][SIZE=2], System.Data.[/SIZE][SIZE=2][COLOR=#2b91af]SqlDbType[/COLOR][/SIZE][SIZE=2].VarChar, 14);[/SIZE]
[SIZE=2]comm.Parameters[[/SIZE][SIZE=2][COLOR=#a31515]"@pocPhone"[/COLOR][/SIZE][SIZE=2]].Value = pocPhoneTextBox.Text;[/SIZE]
[SIZE=2]comm.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@genAhours"[/COLOR][/SIZE][SIZE=2], System.Data.[/SIZE][SIZE=2][COLOR=#2b91af]SqlDbType[/COLOR][/SIZE][SIZE=2].VarChar, 14);[/SIZE]
[SIZE=2]comm.Parameters[[/SIZE][SIZE=2][COLOR=#a31515]"@genAhours"[/COLOR][/SIZE][SIZE=2]].Value = genAhoursTextBox.Text;[/SIZE]
[SIZE=2]comm.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@genBhours"[/COLOR][/SIZE][SIZE=2], System.Data.[/SIZE][SIZE=2][COLOR=#2b91af]SqlDbType[/COLOR][/SIZE][SIZE=2].VarChar, 14);[/SIZE]
[SIZE=2]comm.Parameters[[/SIZE][SIZE=2][COLOR=#a31515]"@genBhours"[/COLOR][/SIZE][SIZE=2]].Value = genBhoursTextBox.Text;[/SIZE]
 
[SIZE=2]pID = comm.Parameters.Add([/SIZE][SIZE=2][COLOR=#a31515]"@tim_id"[/COLOR][/SIZE][SIZE=2], System.Data.[/SIZE][SIZE=2][COLOR=#2b91af]SqlDbType[/COLOR][/SIZE][SIZE=2].Int);[/SIZE]
[SIZE=2]pID.Direction = System.Data.[/SIZE][SIZE=2][COLOR=#2b91af]ParameterDirection[/COLOR][/SIZE][SIZE=2].Output;[/SIZE]
 
[SIZE=2][COLOR=#0000ff]try[/COLOR][/SIZE]
[SIZE=2]{[/SIZE]
[SIZE=2]conn.Open();[/SIZE]
 
[SIZE=2]comm.ExecuteScalar();[/SIZE]
[SIZE=2][COLOR=#008000]//tim_id = objCmd.Parameters["tim_id"].Value.ToString();[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]string[/COLOR][/SIZE][SIZE=2] tim_id = comm.Parameters[[/SIZE][SIZE=2][COLOR=#a31515]"@tim_id"[/COLOR][/SIZE][SIZE=2]].Value.ToString(); [/SIZE]
 
[SIZE=2][COLOR=#008000]// Move to the next page where users will enter deficiecies for parts[/COLOR][/SIZE]
[SIZE=2]Response.Redirect([/SIZE][SIZE=2][COLOR=#a31515]"nextpage.aspx?tim_id="[/COLOR][/SIZE][SIZE=2] + tim_id);[/SIZE]
[SIZE=2]}[/SIZE]
 
 
[SIZE=2][COLOR=#0000ff]finally[/COLOR][/SIZE]
[SIZE=2]{[/SIZE]
[SIZE=2]conn.Close();[/SIZE]
[SIZE=2]}[/SIZE][/SIZE][/COLOR][/SIZE]