How do I insert an empty row in a table with an auto-Increment primary key?

Question

I just want to grab the primary using the SqlTransaction method.

This is what I have so far:

using (SqlTransaction transaction = connection.BeginTransaction())
{
    // insert IT Requirements
    string strITStaff = "INSERT INTO ITReq";
    strITStaff += "SELECT SCOPE_IDENTITY()";

    SqlCommand cmdITStaff = new SqlCommand(strITStaff, connection, transaction);
    ddlITSupport.SelectedValue.ToString());

    int ITStaffReq = Convert.ToInt32(cmdITStaff.ExecuteScalar());

    // update lanGaymen
    string strUpdatePhaseSix = "UPDATE lanGaymen SET ";
    strUpdatePhaseSix += "itReqID = @updateITReq ";
    strUpdatePhaseSix += "WHERE seasonID = @compareSeason";

    SqlCommand cmdUpdatePhaseSix = new SqlCommand(strUpdatePhaseSix, connection, transaction);
    cmdUpdatePhaseSix.Parameters.AddWithValue("@updateITReq", ITStaffReq);
}

Answer 1

In order to insert the default values including the "auto-increment" value, you need to use DEFAULT VALUES in your INSERT. Also, you need to have at least a ; between your INSERT and your SELECT SCOPE_IDENTITY() (your current code doesn't have a space nor a semicolon between the two statements):

string strITStaff = "INSERT INTO ITReq DEFAULT VALUES;  SELECT SCOPE_IDENTITY()";

This will insert all default values into the ITReq table, and then select the newly inserted IDENTITY from that table.