SQL Output Inserted

Question

I have two buttons on a page, one that logs a start time, and one that logs an end time.

The start time button performs an sql insert. At that point i need to grab the primary key that's create. To do this i want to use the sql command (output inserted).

Then when the stop time is clicked,the row should be update with a stop time using the primary key from the start in the where clause. I believe insert SQL is correct but i don't know how to pass the primary key to the next command.

Code dump, with what i have so far.

var command1 = "INSERT INTO [Time] ([Start Time], [Work Order]) OUTPUT INSERTED.PrimaryKey VALUES (@StartTime, @Work_Order)";
using (SqlConnection cnn1 = new SqlConnection(cnnString))
{
    using (SqlCommand cmd1 = new SqlCommand(command1, cnn1))
    {
        cmd1.Parameters.AddWithValue("@StartTime", SqlDbType.DateTime).Value = System.DateTime.Now;
        cmd1.Parameters.AddWithValue("@Work_Order", SqlDbType.Int).Value = e.CommandArgument;
        cnn1.Open();
        Label1.Text = cmd1.ExecuteScalar().ToString();
        cnn1.Close();
    }
}

var command = "UPDATE [Time] SET [Stop Time] = @StopTime WHERE [PrimaryKey] = @PrimaryKey";
using (SqlConnection cnn = new SqlConnection(cnnString))
{
    using (SqlCommand cmd = new SqlCommand(command, cnn))
    {
        cmd.Parameters.AddWithValue("@StopTime", SqlDbType.DateTime).Value = System.DateTime.Now;
        cmd.Parameters.AddWithValue("@PrimaryKey", *PrimaryKey from INSERT output*

        cnn.Open();
        cmd.ExecuteNonQuery();
    }
}

Answer 1

instead of having it go to the label have it go to an int and then set the label text with the int. Then pass the int on the second part. Declare the int outside the scope of the using statements though or it will be disposed and you will get a null reference exception when you try and call it later.

Edit: To add, this would be better if you convert to stored procs and define the SqlParameter objects (you don't have them, you'll need them).

SqlParameter

    int myPK;
    var command1 = "INSERT INTO [Time] ([Start Time], [Work Order]) OUTPUT INSERTED.PrimaryKey VALUES (@StartTime, @Work_Order)";
    using (SqlConnection cnn1 = new SqlConnection(cnnString))
    {
        using (SqlCommand cmd1 = new SqlCommand(command1, cnn1))
        {
            cmd1.Parameters.AddWithValue("@StartTime", SqlDbType.DateTime).Value = System.DateTime.Now;
            cmd1.Parameters.AddWithValue("@Work_Order", SqlDbType.Int).Value = e.CommandArgument;
            cnn1.Open();
            myPk = Convert.ToInt32(cmd1.ExecuteScalar());
            Label1.Text = myPk.ToString();
            cnn1.Close();
        }
    }

    var command = "UPDATE [Time] SET [Stop Time] = @StopTime WHERE [PrimaryKey] = @PrimaryKey";
    using (SqlConnection cnn = new SqlConnection(cnnString))
    {
        using (SqlCommand cmd = new SqlCommand(command, cnn))
        {
            cmd.Parameters.AddWithValue("@StopTime", SqlDbType.DateTime).Value = System.DateTime.Now;
            cmd.Parameters.AddWithValue("@PrimaryKey", myPK);

            FindControl("Work_OrderLabel"); ;

            cnn.Open();
            cmd.ExecuteNonQuery();
        }
    }