CODEWITHSUNDEEP
c#asp.netsql-serverasp.net-webpages
TimK
TimK

Reputation: 135

Trying to get SELECT SCOPE_IDENTITY() as c# variable

I am inserting a row into one table then want to get that new ID so I can add it in another variable where I have email address stored. 

 var db = Database.Open("myDB");
 var insertCommand1 = "INSERT INTO myDB (FirstName, LastName) Values(@0, @1)";                       
 db.Execute(insertCommand1, first, last);
 var lastInsertedId = db.QueryValue("SELECT SCOPE_IDENTITY()");

 var insertCommand2 = "INSERT INTO email (id_person, email) Values(@0, @1)";
 db.Execute(insertCommand2, lastInsertId, email);

where id_person is the id that is created in my first table. When I run the code I get lastInsertedId = {}. Any reason why it is not grabbing a value for id_person which is a primary key, int , not null for my first table? --Tim

Upvotes: 1

Views: 24984

Answers (3)

Rashmi Ranjan Ransingh
Rashmi Ranjan Ransingh

Reputation: 61

It is very easy to get the SCOPE_IDENTITY() value back from SQL Server. I will give an example where I was able to print the SCOPE_IDENTITY() data back in c# label.

  • My Code Snippet in a Submit from data insert
btnSubmit_Click()
{
    Random s = new Random();

    cmd = new SqlCommand("INSERT INTO [dbo].[tblMemberAccount] ([Userid], [User_pwd], [User_mobile], [User_referal], [UserWallet]) VALUES(@Userid, @User_pwd, @User_mobile, @User_referal, @UserWallet) ​select scope_identity()", cons); 

    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@Userid",s.Next(4506,9999));
    cmd.Parameters.AddWithValue("@User_pwd",txtPassword.Text);
    cmd.Parameters.AddWithValue("@User_mobile",txtPhoneNumber.Text);
    cmd.Parameters.AddWithValue("@User_referal",txtReferral.Text);
    cmd.Parameters.AddWithValue("@UserWallet",10);
    cons.Open();
    int g = Convert.ToInt32(cmd.ExecuteScalar());
    cons.Close();
    lblConfirm.Text = "Member " +g+ " added successfully!";
}

Here the value 'g' is returning the scope_identity value.

Upvotes: 3

JPil
JPil

Reputation: 301

If the database is in SQL SERVER , create a SQL parameter and set the direction to "Output". Please check this link :

Getting the identity of the most recently added record

Upvotes: 0

Scott Chamberlain
Scott Chamberlain

Reputation: 127593

From the documentation of SCOPE_IDENTITY(), emphasis mine:

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

Because you are using two queries they are considered two batches. You need to do your insert and your select in a single query.

I don't know what library you are using, so I am just guessing on the syntax but I beleive you need something like

 var db = Database.Open("myDB");
 var insertCommand1 = "INSERT INTO myDB (FirstName, LastName) Values(@0, @1); " +
                      "SELECT SCOPE_IDENTITY()";
 var lastInsertedId = db.QueryValue(insertCommand1, first, last);

 var insertCommand2 = "INSERT INTO email (id_person, email) Values(@0, @1)";
 db.Execute(insertCommand2, lastInsertId, email);

Upvotes: 3

Related Questions