January 20, 2016 at 11:41 am
I am using ADODB in two programs written in VB and Delphi that interfaces with an SQL Server Express 2008 R2 SP2.
Using the code below, sometimes I get the following error:
Can not insert duplicate key row in object ' dbo.PArticulos ' with unique index ' ca ' . The duplicate key value is (3601506).
In either VB or Delphi, I am using an SQL SELECT to get the implied record, assign some field changes, and then update the same record.
I do not understand at all why I get a duplicate key error, when I am modifying data in the same record previously searched, not adding a new one.
Unique index 'ca' is the 'CodArticulo' field.
Pseudocode is as this:
lArt := 3601506;
sSql := 'SELECT * FROM PArticulos WHERE CodArticulo = ' + IntToStr(lArt);
qry.SQL.Text := sSql;
qry.Open;
If qry.Eof = False Then
begin
qry.Edit;
qry.FieldByName('Description').AsInteger := sDesc;
..../....
qry.Post;
end;
This error happens me programming either with VB or Deplhi and occurs when the modifications are posted.
The table has about 100.000 records, and in the program there is a mechanism to avoid two users modifying the same record at the same time.
January 20, 2016 at 12:06 pm
Hi,
The message is clear: it tells you that you cannot make the update/modify so that you're violating the unique index constraint. The duplicate key value is 3601506. Such value is already existing in the column on which there is a unique index.
Igor Micev,My blog: www.igormicev.com
January 20, 2016 at 12:14 pm
I do not understand at all why I get a duplicate key error, when I am modifying data in the same record previously searched, not adding a new one.
That's an issue with the text in the SQL Server error. This code:
DECLARE @table TABLE (unique_value int NOT NULL UNIQUE);
INSERT @table VALUES (1),(2),(3);
UPDATE @table SET unique_value = 2 WHERE unique_value = 3;
... will generate the following error:
Msg 2627, Level 14, State 1, Line 5
Violation of UNIQUE KEY constraint 'UQ__#A21199A__0CE16C74930B5428'. Cannot insert duplicate key in object 'dbo.@table'. The duplicate key value is (2).
Regardless of how the duplicate record is getting there you are attempting to add a duplicate value in a column that does not allow duplicates.
Update: fixed IFCode
-- Itzik Ben-Gan 2001
January 20, 2016 at 1:49 pm
PSSQL (1/20/2016)
I am using ADODB in two programs written in VB and Delphi that interfaces with an SQL Server Express 2008 R2 SP2.Using the code below, sometimes I get the following error:
Can not insert duplicate key row in object ' dbo.PArticulos ' with unique index ' ca ' . The duplicate key value is (3601506).
In either VB or Delphi, I am using an SQL SELECT to get the implied record, assign some field changes, and then update the same record.
I do not understand at all why I get a duplicate key error, when I am modifying data in the same record previously searched, not adding a new one.
Unique index 'ca' is the 'CodArticulo' field.
Pseudocode is as this:
lArt := 3601506;
sSql := 'SELECT * FROM PArticulos WHERE CodArticulo = ' + IntToStr(lArt);
qry.SQL.Text := sSql;
qry.Open;
If qry.Eof = False Then
begin
qry.Edit;
qry.FieldByName('Description').AsInteger := sDesc;
..../....
qry.Post;
end;
This error happens me programming either with VB or Deplhi and occurs when the modifications are posted.
The table has about 100.000 records, and in the program there is a mechanism to avoid two users modifying the same record at the same time.
First, replace the SELECT * in your code and list out the columns you need instead. There are a ton of issues with SELECT *, but I do not have the time to get into them right now. (Google is your friend)
Second, check the full code. If the index ca is on column CodArticulo only, then you are probably assigning a new value to CodArticulo in the code. SQL Server will then check that the new value does not already exist.
Another possibility is that the index ca is defined as a filtered index (i.e., it is defined as CREATE UNIQUE INDEX ca ON tablename(CodArticulo) WHERE some condition. In that case, the explanation is that the update affects one of the columns mentioned in the WHERE condition of the filter. Before the update the row was excluded from the index, now it is not anymore - and it conflicts with another row that also is not excluded.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply