472,992 Members | 3,418 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,992 software developers and data experts.

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

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


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.


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.
Jun 23 '08 #1
7 89911
2,878 Expert 2GB
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
Jun 24 '08 #2
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


***ID = From Table1***
***id = From Table2***
***id = ID ***
Jun 26 '08 #3
8,127 Expert 4TB
i am not sure from your code what exactly you are trying to do . Please pass more info.
Jun 27 '08 #4
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.
Jul 8 '08 #5
8,127 Expert 4TB
you need to check for existance of data in the database before inserting the same to the table.
Jul 8 '08 #6
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.
Oct 21 '08 #7
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


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

--for update


UPDATE dbo.table1
SET ID=123
WHERE condition

hope that helps.

Aug 30 '09 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Erland | last post by:
Hi all, I have made an ASP.NET 1.1 application on windows 2000 advanced server. In the application, i have saved the file using the following code string str; str="this is my data";...
by: HH | last post by:
I try to make a script for generating backend databases. When I try to make a relationship between a table named 'users' and another table name 'layouts' an error occurs. Option 1:...
by: rocksoft | last post by:
Hi, i got error "class not registered" when i try to create web setup and deployment project to my web application. i'm used Asp.net and c# to my application, i created web setup and deployment...
by: mickey22 | last post by:
Hi, I am getting some errors in building my project I tried to build a C++ file say abc.cpp and I have put the all the directories to include necessary library files and header files..But I...
by: kaleeswaran | last post by:
i know the forigen key table,but i forget the primary key table which it is refer the forigen key... so i want to know the query to find out the primary key table which refer the forigen key table...
by: meru | last post by:
Hi The highlighted below is the sample code I used for calling my stored procedure. Callable statement cstmt = conn.prepareCall( BEGIN STOREDPROCNAME(?,?,?);END;); ArrayDescriptor...
by: winbala | last post by:
Hi, I am having problem creating new project using VS.Net 2003. Whenever I am trying to create new project (any type) it shows error message "Error writing the project file. Error loading...
by: sanQUEST | last post by:
hi, can anybody can tell me how to add pry constraint to a table if it has already duplicated records ? san
by: Lars | last post by:
I have a compilation error and do not know how to fix it. This is the first time I've tried to use a generic type constraint. What have I done wrong? public sealed class ClassA : IInput {...
by: Silvia Mattarel | last post by:
I have two function: Public Sub AddData() Dim sSQL As String = "" Dim check As Boolean = Me.TestExistingRow("QUESTIONARIO1") If check = False Then sSQL = "INSERT INTO QUESTIONARIO1()...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: SueHopson | last post by:
Hi All, I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.