how do i get id of the last inserted row

Question

i want to display booking id of the last inserted row.my insert code is given below. pls anyone can give me code to display the id

    protected void Button1_Click(object sender, EventArgs e)
    {
        string cs = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(cs))
        {
            SqlCommand cmd;
            SqlDataReader dr;
            con.Open();
            cmd = new SqlCommand("insert into [booking] values('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "','" + TextBox6.Text + "','" + TextBox7.Text + "','" + TextBox8.Text + "','" + TextBox9.Text + "','" + TextBox10.Text + "','" + TextBox11.Text + "')", con);

            cmd.ExecuteNonQuery();

        }
    }

This doesn't address your question, but yikes - watch out for [SQL injection](https://en.wikipedia.org/wiki/SQL_injection)! Maybe consider a [parameterised query](http://www.techrepublic.com/article/shorten-development-time-by-using-parameterized-queries-in-adonet/) instead. — Richard Ev, Feb 17 '16 at 17:21
You need to use a parametrized query, SqlCommand is disposable, You need to state the database your using for this to be answerable. — Alex K., Feb 17 '16 at 17:22
Possible duplicate of [Best way to get identity of inserted row?](http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row) — Dour High Arch, Feb 17 '16 at 18:09

score 1 · Answer 1 · answered Feb 17 '16 at 17:40

I would suggest using something like this:

protected void Button1_Click(object sender, EventArgs e)
{
    var cs = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
    using (var con = new SqlConnection(cs))
    {
        con.Open();
        var cmd = new SqlCommand(
            "DECLARE @IDReturnTable TABLE( ID INT ); INSERT INTO [booking] OUTPUT INSERTED.NameOfYourIdColumn INTO @IDReturnTable VALUES(@param1, @param2, @param3); SELECT ID FROM @IDReturnTable", 
            con);
        cmd.Parameters.Add("@param1", SqlDbType.VarChar).Value = TextBox1.Text;
        cmd.Parameters.Add("@param2", SqlDbType.VarChar).Value = TextBox2.Text;
        cmd.Parameters.Add("@param3", SqlDbType.VarChar).Value = TextBox3.Text;

        var returnedId = cmd.ExecuteScalar();
    }
}

I didn't use all 11 Text Boxes, just 3 to illustrate the technique.

I declared it, look at the SQL. – Kevin Feb 17 '16 at 17:44 — Kevin, Feb 17 '16 at 17:44

score 0 · Answer 2 · edited May 23 '17 at 11:59

You will be better off doing this as a stored procedure, and less susceptible to injection.

To achieve it with your current code, add a call to ;SELECT SCOPE_IDENTITY():

cmd = new SqlCommand("insert into [booking] values('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "','" + TextBox6.Text + "','" + TextBox7.Text + "','" + TextBox8.Text + "','" + TextBox9.Text + "','" + TextBox10.Text + "','" + TextBox11.Text + "');SELECT SCOPE_IDENTITY()", con);

And execute as scalar:

var id = cmd.ExecuteScalar();

(This assumes you have an identity column on your table)

To do it as a stored procedure:

If you have a finite number of values, you can just create the stored procedure normally, with an @Parameter for each TextBox.Text but with SELECT SCOPE_IDENTITY() at the end.

But it looks like you have a variable number of inputs, so see How to insert a multiple rows in SQL using stored procedures? which outlines an approach using a table paramater and one using a UDF to split a list of values.

Again, you would need to SELECT SCOPE_IDENTITY() at the end of the proc to pick up the identity of the last row.

For a detailed discussion on the ways of selecting the last inserted id see What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current?

@JamieR indeed - hence "But you will be better off doing this as a stored procedure, and less susceptible to injection." — NikolaiDante, Feb 17 '16 at 17:31
For a full example of this technique: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar%28v=vs.110%29.aspx — Steve Wellens, Feb 17 '16 at 17:35

how do i get id of the last inserted row

2 Answers2