May 27, 2009 at 2:32 am
Hi,
I'm currently writing a program that keeps its data on a SQL server.
It stores project information.
The project consists of several chapters, for which I created a table each.
Each project has one unique code, called the "YPcode".
like: CREATE TABLE ResourcesBudget (YP_code VARCHAR(15) PRIMARY KEY , DE_YP_est_days FLOAT, BI_YP_est_days FLOAT)
Each chapter has a log part, where the user can put in information like a date and a description.
because several log entries can be part of a YPcode, this table as an ID colum.
like: CREATE TABLE OverallStatus (ID BIGINT IDENTITY(1,1) PRIMARY KEY, YP_code VARCHAR(15), datum DATETIME, Description VARCHAR(5000))
On a new project, everything goes as it should be.
The records are created and stored.
Now the user wants to update a project.
The information is read from the database and put in a dataset. Then all the fields of the dataset are linked to the right controls on the form. He can now make his changed, all fields except the one for YPcode.
The he clicks on finish and the following is done:
- all records for the YPcode are backed up with:
UPDATE ResourcesBudget SET YP_code = YP_code+ '_BAK' WHERE YP_code = 'test'
and
UPDATE OverallStatus SET YP_code = YP_code+ '_BAK' WHERE YP_code = 'test'
- the new changed data is written to the database as it were new with:
INSERT INTO ResourcesBudget (YP_code, DE_YP_est_days, BI_YP_est_days) VALUES ('test', 0, 0)
and
INSERT INTO OverallStatus (YP_code, datum, Description) VALUES ('test', '20090527', 'test log entry')
On the second INSERT INTO statement I get te following error:
{Violation of PRIMARY KEY constraint 'PK__OverallS__3214EC277EECB764'. Cannot insert duplicate key in object 'dbo.OverallStatus'.
The statement has been terminated.}
I don't understand why I get this error.
The primary key of this table is the ID column which is an identity column with autoincrement. The error message suggests it wants to use an used ID number.
How can I solve this?
please help me, since I'm stuck now.
rg,
Eric
May 27, 2009 at 2:58 am
well I solved the issue by removing the ID column, thus removing the primary key.
But I'm not sure if it is the right sollution.
At least I can now continue.. 😀
May 27, 2009 at 7:35 am
Something else must be going on in your code. I copied these two lines:
CREATE TABLE OverallStatus (ID BIGINT IDENTITY(1,1) PRIMARY KEY, YP_code VARCHAR(15), datum DATETIME, Description VARCHAR(5000))
INSERT INTO OverallStatus (YP_code, datum, Description) VALUES ('test', '20090527', 'test log entry')
I ran the second line multiple times without an issue. You might want to double check the data definition of that table. The primary key defined on it might not be what you thought it was.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 27, 2009 at 11:47 am
did you also try the update part between the create and insert parts?
Only with the update in between, it goes wrong.
May 27, 2009 at 11:53 am
Yep. I just tried the update. It didn't seem to affect things at all. It sure sounds like the PK was not defined as the script you supplied. I can't get an error out of this structure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 27, 2009 at 11:57 am
well, thank you for your time.
I'm leaving the ID column out since I don't use it anyways.
thanks.
Eric
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply