sql-serversql-server-2008primary-key

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


On an eCommerce site, this code is inserting order shipping info into another database:

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, 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).

Should I declare the ID in the statement?

How would I adjust the code to fix this?


Solution

  • Assuming pk_OrderID is the PK of AC_Shipping_Addresses and you are trying to insert a duplicate via the _Order.OrderNumber, do

    select * from AC_Shipping_Addresses where pk_OrderID = 165863;
    

    or select count(*) ....

    You should 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)