Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object
To prevent inserting a record that exist already. I'd check if the ID value exists in the database. For the example of a Table created with an IDENTITY PRIMARY KEY:
CREATE TABLE [dbo].[Persons] (
ID INT IDENTITY(1,1) PRIMARY KEY,
LastName VARCHAR(40) NOT NULL,
FirstName VARCHAR(40)
);
When JANE DOE and JOE BROWN already exist in the database.
SET IDENTITY_INSERT [dbo].[Persons] OFF;
INSERT INTO [dbo].[Persons] (FirstName,LastName)
VALUES ('JANE','DOE');
INSERT INTO Persons (FirstName,LastName)
VALUES ('JOE','BROWN');
DATABASE OUTPUT of TABLE [dbo].[Persons] will be:
ID LastName FirstName
1 DOE Jane
2 BROWN JOE
I'd check if i should update an existing record or insert a new one. As the following JAVA example:
int NewID = 1;
boolean IdAlreadyExist = false;
// Using SQL database connection
// STEP 1: Set property
System.setProperty("java.net.preferIPv4Stack", "true");
// STEP 2: Register JDBC driver
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// STEP 3: Open a connection
try (Connection conn1 = DriverManager.getConnection(DB_URL, USER,pwd) {
conn1.setAutoCommit(true);
String Select = "select * from Persons where ID = " + ID;
Statement st1 = conn1.createStatement();
ResultSet rs1 = st1.executeQuery(Select);
// iterate through the java resultset
while (rs1.next()) {
int ID = rs1.getInt("ID");
if (NewID==ID) {
IdAlreadyExist = true;
}
}
conn1.close();
} catch (SQLException e1) {
System.out.println(e1);
}
if (IdAlreadyExist==false) {
//Insert new record code here
} else {
//Update existing record code here
}
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)
What is the value you're passing to the primary key (presumably "pk_OrderID")? You can set it up to auto increment, and then there should never be a problem with duplicating the value - the DB will take care of that. If you need to specify a value yourself, you'll need to write code to determine what the max value for that field is, and then increment that.
If you have a column named "ID" or such that is not shown in the query, that's fine as long as it is set up to autoincrement - but it's probably not, or you shouldn't get that err msg. Also, you would be better off writing an easier-on-the-eye query and using params. As the lad of nine years hence inferred, you're leaving your database open to SQL injection attacks if you simply plop in user-entered values. For example, you could have a method like this:
internal static int GetItemIDForUnitAndItemCode(string qry, string unit, string itemCode)
{
int itemId;
using (SqlConnection sqlConn = new SqlConnection(ReportRunnerConstsAndUtils.CPSConnStr))
{
using (SqlCommand cmd = new SqlCommand(qry, sqlConn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@Unit", SqlDbType.VarChar, 25).Value = unit;
cmd.Parameters.Add("@ItemCode", SqlDbType.VarChar, 25).Value = itemCode;
sqlConn.Open();
itemId = Convert.ToInt32(cmd.ExecuteScalar());
}
}
return itemId;
}
...that is called like so:
int itemId = SQLDBHelper.GetItemIDForUnitAndItemCode(GetItemIDForUnitAndItemCodeQuery, _unit, itemCode);
You don't have to, but I store the query separately:
public static readonly String GetItemIDForUnitAndItemCodeQuery = "SELECT PoisonToe FROM Platypi WHERE Unit = @Unit AND ItemCode = @ItemCode";
You can verify that you're not about to insert an already-existing value by (pseudocode):
bool alreadyExists = IDAlreadyExists(query, value) > 0;
The query is something like "SELECT COUNT FROM TABLE WHERE BLA = @CANDIDATEIDVAL" and the value is the ID you're potentially about to insert:
if (alreadyExists) // keep inc'ing and checking until false, then use that id value
Justin wants to know if this will work:
string exists = "SELECT 1 from AC_Shipping_Addresses where pk_OrderID = " _Order.OrderNumber; if (exists > 0)...
What seems would work to me is:
string existsQuery = string.format("SELECT 1 from AC_Shipping_Addresses where pk_OrderID = {0}", _Order.OrderNumber);
// Or, better yet:
string existsQuery = "SELECT COUNT(*) from AC_Shipping_Addresses where pk_OrderID = @OrderNumber";
// Now run that query after applying a value to the OrderNumber query param (use code similar to that above); then, if the result is > 0, there is such a record.
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