Primary key violation exception with SqlDataAdapter bulk insert

Question

Hello,

I have a WinForm 2.0 application that uses a standard DataGridView control to allow inserts, updates and deletes.

I am implementing the bulk insert via the SqlDataAdapter bulk insert functionality, but am getting a primary key constraint violation of my table I am trying to insert into.

Violation of PRIMARY KEY constraint 'PK_tblLIDCustomersAddresses'.
Cannot insert duplicate key in object 'tblLIDCustomersAddresses'

Here is the code that implements this:

I'm probably missing something simple, but if anyone has a solution, please reply back.

Thanks in advance!

StringBuilder insertSQL = new StringBuilder();
insertSQL.Append("INSERT INTO tblLIDCustomersAddresses(");
insertSQL.Append("lngLIDCustomerID,strAddressType,strCustomerAddress,strCustomerLocation,");
insertSQL.Append("strCustomerStreetName,strCustomerSuite,strCustomerStreetTypes,");
insertSQL.Append("strCustomerCity,strCustomerState,strCustomerZip,dtmUpdated,strUserUpdated)");
insertSQL.Append("VALUES(@lngLIDCustomerID,@strAddressType,@strCustomerAddress,");
insertSQL.Append("@strCustomerLocation,@strCustomerStreetName,");
insertSQL.Append("@strCustomerSuite,@strCustomerStreetTypes,@strCustomerCity,");
insertSQL.Append("@strCustomerState,@strCustomerZip,@dtmUpdated,@strUserUpdated)");
insertSQL.AppendLine("SET @CustomerAddressID = SCOPE_IDENTITY()");

using (SqlConnection cn = Utilities.CreateSQLConnection(Properties.Settings.Default.DBConnection))
{
    da = new SqlDataAdapter();
    da.InsertCommand = new SqlCommand(insertSQL.ToString(), cn);
    da.InsertCommand.Parameters.Add("@lngLIDCustomerID", SqlDbType.Int, 4, "lngLIDCustomerID");
    da.InsertCommand.Parameters.Add("@strAddressType", SqlDbType.VarChar, 20, "strAddressType");
    da.InsertCommand.Parameters.Add("@strCustomerAddress", SqlDbType.VarChar, 25, "strCustomerAddress");
    da.InsertCommand.Parameters.Add("@strCustomerLocation", SqlDbType.VarChar, 2, "strCustomerLocation");
    da.InsertCommand.Parameters.Add("@strCustomerStreetName", SqlDbType.VarChar, 50, "strCustomerStreetName");
    da.InsertCommand.Parameters.Add("@strCustomerSuite", SqlDbType.VarChar, 10, "strCustomerSuite");
    da.InsertCommand.Parameters.Add("@strCustomerStreetTypes", SqlDbType.VarChar, 4, "strCustomerStreetTypes");
    da.InsertCommand.Parameters.Add("@strCustomerCity", SqlDbType.VarChar, 35, "strCustomerCity");
    da.InsertCommand.Parameters.Add("@strCustomerState", SqlDbType.VarChar, 2, "strCustomerState");
    da.InsertCommand.Parameters.Add("@strCustomerZip", SqlDbType.VarChar, 10, "strCustomerZip");
    da.InsertCommand.Parameters.AddWithValue("@dtmUpdated", DateTime.Now.ToString());
    da.InsertCommand.Parameters.AddWithValue("@strUserUpdated", this._user.Name);
    da.InsertCommand.Parameters.Add("@CustomerAddressID", SqlDbType.Int, 4, "CustomerAddressID");
    da.InsertCommand.Parameters["@CustomerAddressID"].Direction = ParameterDirection.Output;                    
    da.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
    da.UpdateBatchSize = 0; // set batch size to maximum value possible
    da.Update(addressChanges);
}

Answer 1

I would double check your table before running your application - is there already some data in it that you may be trying to insert again unitentionally? That's usually the case. Now if you know there will be duplicates sometimes in the future you can catch the error and ignore it or do something with those values.

Answer 2

Thanks for replying. Your answer made me think about what SQL Server was really complaining about.

I checked the primary key constraint definition for the tblLIDCustomersAddresses table and the constraint is the combination of the lngLIDCustomerID and strAddressType fields.

If you try to insert two more more rows from the DataGridView control with the same Address Type value, you will indeed get the primary key constraint violation.

The whole time, I was thinking there was some issue with the use of the Scope_Identity() function, but it's really the primary key constraint definition that is the culprit here.

Thanks again...