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

Question

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?

Answer 1

I was getting the same error on a restored database when I tried to insert a new record using the EntityFramework. It turned out that the Indentity/Seed was screwing things up.

Using a reseed command fixed it.

DBCC CHECKIDENT ('[Prices]', RESEED, 4747030);GO

Answer 2

I'm pretty sure pk_OrderID is the PK of AC_Shipping_Addresses

And you are trying to insert a duplicate via the _Order.OrderNumber?

Do a

select * from AC_Shipping_Addresses where pk_OrderID = 165863;

or select count(*) ....

Pretty sure you will get a row returned.

It is telling you that you are already using pk_OrderID = 165863 and cannot have another row with that value.

if you want to not insert if there is a row

insert into table (pk, value) 
select 11 as pk, 'val' as value 
where not exists (select 1 from table where pk = 11)