Get the id of the inserted record in sql server database

Jennifer ODonovan

I am looking to know how to Store a newly created ID(record) in sql server DB into a Session Variable. ( or other option)

For example:

I am using asp.Net Web Forms with a sql server Database

i have a web page where users enter in details of a booking and then click a addBooking button which stores this to my DB.

I am looking to know how to capture this Newly created ID to display it after that button is submitted

protected void AddBooking_Click(object sender, EventArgs e)

{

//Creating a connection to my database using the connection string

string cs = ConfigurationManager.ConnectionStrings["BookingDb"].ConnectionString;

SqlConnection con = new SqlConnection(cs);

//preparing a query which will insert the data entered in the textboxes to the database

SqlCommand cmd = new SqlCommand("[dbo].[spAddBooking]", con);

cmd.CommandType = CommandType.StoredProcedure;

try

{

cmd.Parameters.AddWithValue("@UserId", UserID.Text.Trim());

cmd.Parameters.AddWithValue("@MemberType", ddlMemberType.SelectedValue);

cmd.Parameters.AddWithValue("@PitchSection", ddlSyntheticPitch.SelectedValue);

cmd.Parameters.AddWithValue("@Date", txtDate.Text.Trim());

cmd.Parameters.AddWithValue("@StartTime", ddlStartTime.SelectedValue);

cmd.Parameters.AddWithValue("@EndTime", ddlEndTime.SelectedValue);

cmd.Parameters.AddWithValue("@Description", txtDescription.InnerText.Trim());

con.Open();

cmd.ExecuteNonQuery();

}

catch (Exception ex)

{

Response.Write(ex);

}

finally

{

// SetSession();

con.Close();

Response.Redirect("WebForm2.aspx");

}

// Get the ID of the inserted record in the database

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mageshwaran R6y ago
Hi,
Can you check with the below link:
https://www.c-sharpcorner.com/UploadFile/4aae29/complete-crud-operations-in-Asp-Net/
0
Nitin Sontakke8y ago
In case you are going with code posted by Prakash, modify you sp code as follows:
Begin
   insert into dbo.BookingDetails values (@UserId ,@Date, @StartTime, @EndTime, @Description, @AmountPaid, @MemberType, @PitchSection)
   select @BookingId = SCOPE_IDENTITY();
  select @BookingId [BookingId];
End
+1
Jennifer ODonovan8y ago
@Pankaj yes i have BookingId set as an identity(auto generated number) in my SP.

This is the end of my SP with the insert statament and scope identity set.
BookingId is also set as an identity in thr BookingDetails Table.

   Begin
   insert into dbo.BookingDetails values (@UserId ,@Date, @StartTime, @EndTime, @Description,    @AmountPaid, @MemberType, @PitchSection)
   select @BookingId = SCOPE_IDENTITY()
   End

+1
Nitin Sontakke8y ago
Nicer if you could care to share the sp code as well, please. Also let us know if you have any identity column on the table or not.

In addition, do not call your db access code from UI pages. Instead create separate database access class and a business class. Code consolidation is a must, otherwise very soon you will run into many problems.
+1
Prakash Chasiya8y ago
Hi Jennifer;

Please see the below code, I have modifeid it a little bit:
protected void AddBooking_Click(object sender, EventArgs e)
{
    //Creating a connection to my database using the connection string
    string cs = ConfigurationManager.ConnectionStrings["BookingDb"].ConnectionString;
    SqlConnection con = new SqlConnection(cs);
    //preparing a query which will insert the data entered in the textboxes to the database
    SqlCommand cmd = new SqlCommand("[dbo].[spAddBooking]", con);
    cmd.CommandType = CommandType.StoredProcedure;
    try
    {
        cmd.Parameters.AddWithValue("@UserId", UserID.Text.Trim());
        cmd.Parameters.AddWithValue("@MemberType", ddlMemberType.SelectedValue);
        cmd.Parameters.AddWithValue("@PitchSection", ddlSyntheticPitch.SelectedValue);
        cmd.Parameters.AddWithValue("@Date", txtDate.Text.Trim());
        cmd.Parameters.AddWithValue("@StartTime", ddlStartTime.SelectedValue);
        cmd.Parameters.AddWithValue("@EndTime", ddlEndTime.SelectedValue);
        cmd.Parameters.AddWithValue("@Description", txtDescription.InnerText.Trim());
        con.Open();

        int intProductID = 0;
        intProductID = Convert.ToInt32(cmd.ExecuteScalar());

        //Here, you can store your ID in session or cookie
    }
    catch (Exception ex)
    {
        Response.Write(ex);
    }
    finally
    {
        // SetSession();
        con.Close();
        Response.Redirect("WebForm2.aspx");
    }
}
By using above code, you will get your last inserted record ID, but from your Store Procedure side you need add below line of code after your record gets inserted successfully:
Return @@identity;

Hope this help.

Thanks
+2
Pankaj Kumar Choudhary8y ago
Hello Jennifer! are you using the auto-matic generated id(identity) into you table and want to get the value of this automatic generated id value? am I getting right ??
+1