Getting ID of record just inserted?

Question

I am processing items users have selected from a gridview, and performing an email and database insert operation.

When the user selects a button, the code below takes information from the gridview, creates a new order in the Order table, and creates new entries in the Transactions table.

How can I get the last inserted ID if I use this method of inserting a record? Would you recommend a different approach to this simple problem?

protected void btnOrder_Click(object sender, EventArgs e)
{
    double Total = 0;
    string MailFrom = "[email protected]";
    string MailTo = "[email protected]";
    string MailSubject = "Online Order";
    string MailCC = "";
    string MailBCC = "";
    string MailReplyTo = "";
    string MailBody = "";

    TextBox ItmCostCode = (TextBox)form1.FindControl("txtCostCode");

    foreach (GridViewRow gvr in GridView1.Rows)
    {
        CheckBox cb = (CheckBox)gvr.FindControl("ItemSelect");
        Label ItmTotal = (Label)gvr.FindControl("ItmTotal");
        Label ItmPrice = (Label)gvr.FindControl("ItmPrice");
        Label ItmName = (Label)gvr.FindControl("lblName");
        TextBox ItmQty = (TextBox)gvr.FindControl("ItmQty");
        TextBox ItmID = (TextBox)gvr.FindControl("lblItemID");

        //Add entry to Order Table
        SqlDataSource2.InsertParameters.Add("OrderDate", DateTime.Now.ToString("MMMM dd, yyyy"));
        SqlDataSource2.InsertParameters.Add("OrderTotal", "0");
        SqlDataSource2.InsertParameters.Add("OrderAccount", "name");
        SqlDataSource2.InsertParameters.Add("OrderCostCentre", ItmCostCode.Text);
        SqlDataSource2.Insert();

        //TODO: GET ORDERID HERE TO USE BELOW:
        if (cb.Checked)
        {
            double Price = Convert.ToDouble(ItmPrice.Text);
            double Qty = Convert.ToDouble(ItmQty.Text);

            Total = Price * Qty;
            OrderTotal = OrderTotal + Total;

            MailBody = MailBody + "Item: "+ItmName.Text+" Quantity: "+ItmQty.Text+" Total: "+ItmTotal.Text+"\n\r";

            //Add entry to Transaction Table
            SqlDataSource3.InsertParameters.Add("ItemID", ItmID.Text);
            SqlDataSource3.InsertParameters.Add("OrderID", );
            SqlDataSource3.InsertParameters.Add("Price", ItmPrice.Text);

            SqlDataSource3.Insert();
        }

        //TODO: Update Order table with OrderTotal
    }

    string strOrderTotal = OrderTotal.ToString();

    MailBody = MailBody+"Order Total: " + strOrderTotal+"\n\r";
    MailBody = MailBody + "Cost Code: " + ItmCostCode.Text;

    MailService.Service1 Mailer = new MailService.Service1();
    Mailer.SendMail("Text", MailFrom, MailTo, MailCC, MailBCC, MailSubject, MailBody, MailReplyTo);
}

Answer 1

use scope identity at the end of your insert query and it will return inserted ID like...

INSERT INTO table (ColumnName) VALUES ();
GO
SELECT SCOPE_IDENTITY();

Edit: for your help, here is some article that can help you to implement

http://msdn.microsoft.com/en-us/library/z72eefad.aspx

http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record http://www.objectreference.net/post/SCOPE_IDENTITY()-return-the-id-from-the-database-on-insert.aspx

Answer 2

Building on what @Muhammad Akhtar said, you could return the identity at the end of your insert and then pass the scalar back up to your DB calling layer/function. I've used it a lot as a check to see if the Insert has gone in ok, return -1 or something to signify it's failure. You can then use this as a way of passing a message back to the user saying this is the case.

I would also structure your code different and have an Order object with OrderItems. This gets created fromt he gridview and passed to your Data Access Layer (DAL). The DAL can then do the inserts and return the Order object and subsequent OrderItems updated with the ID too if necessary.