Violation of PRIMARY KEY constraint..how to solve this error?

[akmaRudiliyn]

Hai everybody :). I have problem and need help.

ERROR:

ERROR [23000] [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK_Table1_01'. Cannot insert duplicate key in object 'dbo.table1'.
ERROR [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.


MY QUESTION:

How to solve this error?

It is about two tables.
Primary Key(PK) for Table1 is same value with PK for Table 2, but different names.
Data from Table1 is going to insert into Table 2.
I want to run my form but because of this error, form can not run.
I'm trying many ways to solve it but still can not solve.
I hope anyone can help me.
Thank you.

[ck9663]

Primary Key is a restriction that is being placed in a table to protect it's quality/integrity. It prevents from inserting multiple records with the same key. If you remove the constraint, you'll have multiple records on your table and if your search expects a single record, it will fail.

Depending on the use of your table you may remove the constrain.t Just be careful, the constraint was placed for a reason.

-- CK

[akmaRudiliyn]

Thank you CK for you answer.I had trying that way before and the error is solve. Anyway, i can not remove the constraint because it is compulsory to have in my system.

Lately, the error is disappear without drop the constraint or else. No error appear when i am debugging my form, but the form can not run. I also didnt put any break in my code. What happend here? I am confuse and weird. Anyone can explain me?


Here is example of my code to update data or row when same id is insert. Anyone can check and comment? Actually i am newbie in this field. Need much help from expert. Thank you for your all concern.


Do While drReaderSELECT.Read

If sMode = "Duplicate Record" Then

Dim SQLUPDATE As String =
"UPDATE Table2 SET id='" & strID.Trim & "', icNo='" & stricNo.Trim & "' _
WHERE id ='" & strID & "' "

Dim CMDUPDATE As New OdbcCommand(SQLUPDATE, conn)
Dim adDb As OdbcDataAdapter
adDb = New OdbcDataAdapter

CMDUPDATE.Parameters.Add("strID", OdbcType.NVarChar, 50)
CMDUPDATE.Parameters.Add("stricNo", OdbcType.NVarChar, 12)

adDb.UpdateCommand = CMDUPDATE

End If

Loop



***ID = From Table1***
***id = From Table2***
***id = ID ***

[debasisdas]

i am not sure from your code what exactly you are trying to do . Please pass more info.

[akmaRudiliyn]

Sorry taking for a long time to reply.
I want to know, why my form can not run although there is no error or warning when i'm debugging.

[debasisdas]

you need to check for existance of data in the database before inserting the same to the table.

[mimeauja]

you need to check for existance of data in the database before inserting the same to the table.

I found the answer to this question, late, but I've just run into this in a program, so...here it is.

It's a bug (but someone I'm sure would call it a feature).

If you try to update a field in your row that is part of a primary key constraint, even if it has the same value as the row you're trying to update already has, then the command assumes that you're doing an insert.

You have to modify your code to NOT update the PK fields unless you really want to do an insert.

And thus my old saying, if you try to read people's minds too much in your program, you're going to fail miserably.

[baghul]

I have run into this issue before as well, Like debasisdas mentioned, you can check for existence of data before you insert. Here is sample code you could use

--for insert

IF NOT EXISTS (SELECT * FROM dbo.table1 WHERE ID=123)

INSERT dbo.table1 (ID, ..........)
VALUES (123, .........)

--for update

IF NOT EXISTS (SELECT * FROM dbo.table1 WHERE ID=123)

UPDATE dbo.table1
SET ID=123
WHERE condition

hope that helps.

Kash
Instructor
http://www.learningsqlserver2008.com/