Executing an insert statement and put id in a local variable?

Question

SqlDataSource myQuiz = new SqlDataSource();

myQuiz.ConnectionString = ConfigurationManager.ConnectionStrings["yafnet"].ToString();
myQuiz.InsertCommand = "INSERT INTO [QuizTable]([Quiz_Title],[Quiz_Description]) VALUES (@Quiz_Title,@Quiz_Description)";

myQuiz.InsertParameters.Add("Quiz_Title",Quiz_Title.Text);
myQuiz.InsertParameters.Add("Quiz_Description",Quiz_Description.Text);

myQuiz.Insert();
Response.Redirect("QuizQuests.aspx");

Is there a way to get back the automatically generated ID of the new row and put it in a local variable?

Thanks a lot in advance, Adrian

execute [SELECT SCOPE_IDENTITY()](http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/) in your C# code: http://stackoverflow.com/questions/3913115/scope-identity-in-c-sharp-range — Luiggi Mendoza, Apr 28 '12 at 06:28

score 2 · Answer 1 · answered Apr 28 '12 at 06:55

2

Use

SELECT IDENT_CURRENT(‘QuizTable’)

It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value. IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

answered Apr 28 '12 at 06:55

Nikhil Agrawal

47,018
22
121
208

1

But if another session does an insert into the `QuizTable` after your insert, but before your `IDENT_CURRENT` call, you'll get the identity value of the other insert, not yours (see [my answer](http://stackoverflow.com/a/10365118/6884) for detailed information) – Christian Specht Apr 28 '12 at 15:54

score 1 · Answer 2 · answered Apr 28 '12 at 07:09

@Adrian De Barro: hi you can use following code to get inserted record id and save in variable

in your sqlquery add following statement:

return @@IDENTITY

and your C# code will be as follows:

    SqlConnection cn = new   
    SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);        
    cn.Open();
    SqlCommand cmd = new SqlCommand(procname, cn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter str = new SqlParameter("name", SqlDbType.VarChar);
    str.Direction = ParameterDirection.ReturnValue;
    foreach (SqlParameter par in param)
    {
        cmd.Parameters.Add(par);
    }
    string name = Convert.ToString(cmd.ExecuteScalar());
    cmd.Dispose();
    cn.Close();
    return name;

score 1 · Accepted Answer · answered Apr 28 '12 at 15:51

If you want to be 100% sure that you really get the identity from your insert in your actual QuizTable, you should use SCOPE_IDENTITY() (as already mentioned by Luiggi Mendoza in his comment), and not @@IDENTITY or IDENT_CURRENT.

@@IDENTITY might not work because it will return the identity from the last insert that happened from your connection. So if there is a trigger on your QuizTable which inserts a row in another table that has an identity column as well, @@IDENTITY will return the value from that second insert, and not that of the original insert into the QuizTable .

IDENT_CURRENT will return the last identity value for your table (and only for your table, so it doesn't matter if there are triggers like the one in the previous example).
But it returns the last identity value for any session. So if another row is inserted from another session between your INSERT and your IDENT_CURRENT, you'll get the identity value from that row, not from yours.

Executing an insert statement and put id in a local variable?

3 Answers3