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

user_418785 0
Viewed 241088

I inherited a project and I'm running into a SQL error that I'm not sure how to fix.

On an eCommerce site, the code is inserting order shipping info into another database table.

Here's the code that is inserting the info into the table:

string sql = "INSERT INTO AC_Shipping_Addresses   
(pk_OrderID, FullName, Company, Address1, Address2, City, Province, PostalCode, CountryCode, Phone, Email, ShipMethod, Charge_Freight, Charge_Subtotal)  
VALUES (" + _Order.OrderNumber;
sql += ", '" + _Order.Shipments[0].ShipToFullName.Replace("'", "''") + "'";
if (_Order.Shipments[0].ShipToCompany == "")
{
  sql += ", '" + _Order.Shipments[0].ShipToFullName.Replace("'", "''") + "'";
}
else
{
  sql += ", '" + _Order.Shipments[0].ShipToCompany.Replace("'", "''") + "'";
}
sql += ", '" + _Order.Shipments[0].Address.Address1.Replace("'", "''") + "'";
sql += ", '" + _Order.Shipments[0].Address.Address2.Replace("'", "''") + "'";
sql += ", '" + _Order.Shipments[0].Address.City.Replace("'", "''") + "'";
sql += ", '" + _Order.Shipments[0].Address.Province.Replace("'", "''") + "'";
sql += ", '" + _Order.Shipments[0].Address.PostalCode.Replace("'", "''") + "'";
sql += ", '" + _Order.Shipments[0].Address.Country.Name.Replace("'", "''") + "'";
sql += ", '" + _Order.Shipments[0].Address.Phone.Replace("'", "''") + "'";
if (_Order.Shipments[0].ShipToEmail == "")
{
  sql += ",'" + _Order.BillToEmail.Replace("'", "''") + "'";
}
else
{
  sql += ",'" + _Order.Shipments[0].ShipToEmail.Replace("'", "''") + "'";
}
sql += ", '" + _Order.Shipments[0].ShipMethod.Name.Replace("'", "''") + "'";
sql += ", " + shippingAmount;
sql += ", " + _Order.ProductSubtotal.ToString() + ")";
bll.dbUpdate(sql);

It is working correctly, but it is also outputting the following SQL error:

Violation of PRIMARY KEY constraint 'PK_AC_Shipping_Addresses'. Cannot insert duplicate key in object 'dbo.AC_Shipping_Addresses'. The duplicate key value is (165863).

From reading similar questions, it seems that I should declare the ID in the statement.

Is that correct? How would I adjust the code to fix this issue?

primary-key sql-server-2008 sql-server
7 Answers

Not OP's answer but as this was the first question that popped up for me in google, Id also like to add that users searching for this might need to reseed their table, which was the case for me

DBCC CHECKIDENT(tablename)
user_18260330

Make sure if your table doesn't already have rows whose Primary Key values are same as the the Primary Key Id in your Query.

user_84545520
Related