Question 87004 in ASP.NET Question posted in
The official documentation can be found here.

Get recent insert record id for update statement in ASP.NET with SQL Server

In my code first, I insert a record for a SQL table. then I want to update the same table after checking an API response. For that, I want to get the inserted record id and pass it to the update statement. I didn’t find a way to get the id. Please give a solution for that. Thank you

using (SqlConnection sqlcon = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]))
{
    sqlcon.Open();
    string query = "INSERT INTO GOU VALUES(@OldPassword,@DateTime,@NewPassword,@IsSuccess,@IsActive)";
    SqlCommand sqlCommand = new SqlCommand(query, sqlcon);
    sqlCommand.Parameters.AddWithValue("@OldPassword", "oldPassword");
    sqlCommand.Parameters.AddWithValue("@DateTime", DateTime.Now);
    sqlCommand.Parameters.AddWithValue("@NewPassword", "");
    sqlCommand.Parameters.AddWithValue("@IsSuccess", 0);
    sqlCommand.Parameters.AddWithValue("@IsActive", 0);
    sqlCommand.ExecuteNonQuery();                                

}
bool Apirespone = getResponse();
if (Apirespone == true)
{
    using (SqlConnection sqlCon = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]))
    {
        sqlCon.Open();
        string query = "UPDATE GOU SET IsSuccess = @IsSuccess,IsActive = @IsActive where Id = @id";
        SqlCommand sqlCommand = new SqlCommand(query, sqlCon);
        sqlCommand.Parameters.AddWithValue("@IsSuccess", 1);
        sqlCommand.Parameters.AddWithValue("@IsActive", 1);
        sqlCommand.Parameters.AddWithValue("@Id", ????);//get recent added record id
        sqlCommand.ExecuteNonQuery();
    }

}
Tags:

2

Answers


  1. 0

    You can use "SCOPE_IDENTITY()" for get the last inserted or updated unique tables id

     using (SqlConnection sqlcon = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]))
 {
     sqlcon.Open();
     string query = @"
INSERT INTO GOU VALUES(@OldPassword,@DateTime,@NewPassword,@IsSuccess,@IsActive);
select SCOPE_IDENTITY() as id;
";
     SqlCommand sqlCommand = new SqlCommand(query, sqlcon);
     sqlCommand.Parameters.AddWithValue("@OldPassword", "oldPassword");
     sqlCommand.Parameters.AddWithValue("@DateTime", DateTime.Now);
     sqlCommand.Parameters.AddWithValue("@NewPassword", "");
     sqlCommand.Parameters.AddWithValue("@IsSuccess", 0);
     sqlCommand.Parameters.AddWithValue("@IsActive", 0);
     int id = (int)sqlCommand.ExecuteScalar();
 }
    Login or Signup to reply.
  2. 0

    — Consider below example with sample table.

    CREATE TABLE #myTemp(identity_column INT IDENTITY(1,1), myColumn CHAR(10));

    –Use this SQL in C# inline query.

        INSERT #myTemp(myColumn) 
OUTPUT inserted.identity_column as id
VALUES('test');

    –You can read output sql in to variable and use it as you expected.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search