CODEWITHSUNDEEP
c#sql-server
Adrian De Barro
Adrian De Barro

Reputation: 503

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

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

Upvotes: 1

Views: 580

Answers (3)

Christian Specht
Christian Specht

Reputation: 36451

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.

Upvotes: 1

R.D.
R.D.

Reputation: 7403

@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;

Upvotes: 1

Nikhil Agrawal
Nikhil Agrawal

Reputation: 48580

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.

Upvotes: 2

Related Questions