How to get last inserted Identity value in SQL server - ASP.NET

Thursday

Getting the Identity of the last Inserted row - ASP.net/C#:
                                    The key to @@Identity is that it returns the value of an autoincrement column that is generated on the same connection.
           The Connection object used for the Insert query must be re-used without closing it and opening it up again. Access doesn't support batch statements, so each must be run separately. It is also therefore possible, though not necessary, to create a new Command object to run the Select @@Identity query. The following code shows this in action where the Connection object is opened, then the first query is executed against cmd using ExecuteNonQuery() to perfom the Insert, followed by changing the CommandText property of cmd to "Select @@Identity" and running that.
Code:

protected void btnSave_Click(object sender, EventArgs e)
    {
        string strConnection = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
        SqlConnection sqlConn = default(SqlConnection);
        SqlCommand sqlCmd = default(SqlCommand);
        try
        {
            string query2 = "Select @@Identity";
            sqlConn = new SqlConnection(strConnection);
            sqlCmd = new SqlCommand("INSERT into student (firstname, lastname, street, city, state) VALUES ('" + txtFirstname.Text + "', '" + txtLastname.Text + "', '" + txtStreet.Text + "','" + txtCity.Text + "','" + txtState.Text + "')", sqlConn);
            sqlConn.Open();
            sqlCmd.ExecuteNonQuery();
            sqlCmd.CommandText = query2;
            int idx = Convert.ToInt32(sqlCmd.ExecuteScalar());
            if (idx != 0)
            {
                Response.Write("<script>alert('Successfully saved')</script>");
            }
            else
                Response.Write("<script>alert('Not saved')</script>");
        }
        catch (Exception ex)
        {
            Response.Write(ex.ToString() + "<br>");
        }
        finally
        {
            sqlConn.Close();
        }
    }

Please share this post if it's useful to you. Thanks!.