CODEWITHSUNDEEP
c#sqlscope-identity
will
will

Reputation: 897

Scope_Identity() is returning a blank string

Below is a section of code with two parts. Almost identical to each other. They both insert something into a table, then call Scope_identity. I've checked to make sure the AddressType is being inserted, and it gives me the CityId just fine. I can't see the difference. I've looked over the code and can't find my mistake. I'm hoping someone can be an extra set of eyes and point out what, I am sure, must be an obvious mistake.

SqlCommand addressTypeCommand = new SqlCommand(
       "INSERT INTO AddressType VALUES ('" + customerRow.AddressType + "');",
                    newCustConnection);
try
{
    addressTypeCommand.ExecuteNonQuery();
}
catch (SqlException addressTypeException)
{
    if (addressTypeException.ToString().StartsWith(
             "Violation of UNIQUE KEY constraint"))
    {
        Console.WriteLine("Unique Key exception on 'AddressType'.");
    }
}

SqlCommand selectAddressTypeID = new SqlCommand(
      "select SCOPE_IDENTITY();", newCustConnection);
string addressTypeID = selectAddressTypeID.ExecuteScalar().ToString();

SqlCommand cityCommand = new SqlCommand(
       "INSERT INTO City VALUES ('" + customerRow.City + "');", 
       newCustConnection);
try
{
    cityCommand.ExecuteNonQuery();
}
catch (SqlException cityException)
{
    if (cityException.ToString().StartsWith(
             "Violation of UNIQUE KEY constraint"))
    {
        Console.WriteLine("Unique Key exception on 'City'.");
    }
}
SqlCommand selectCityID = new SqlCommand(
       "select SCOPE_IDENTITY();", newCustConnection);
string cityID = selectCityID.ExecuteScalar().ToString();

Upvotes: 0

Views: 758

Answers (1)

Laurence
Laurence

Reputation: 10976

To use scope_identity() you need to be in the same scope as the insert. The easiest way to achieve this is to pass it as part of the same batch.

e.g.

SqlCommand addressTypeCommand = new SqlCommand(
   "insert into AddressType values (@addressType); select scope_identity()"
);

addressTypeCommand.Parameters.AddWithValue(
    // replace with correct datatype, possibly set data length
    "@addressType", SqlDbType.VarChar, customerRow.AddressType
);
addressTypeID = addressTypeCommand.ExecuteScalar().ToString();

Upvotes: 3

Related Questions