I have a error. It said violation of primary key constraint 'PK_medicalregistration'. Cannot insert duplicate key in' dbo.medical registration'. The duplicate key value is (17002) please guys help me

Question

1.00/5 (2 votes)

See more:

C#

public void updatedata()
        {
            try
            {
                DialogResult dr = MetroMessageBox.Show(this, "Are You Really want to update dependent Record..", "Dependent Update Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (dr == DialogResult.No)
                {
                    return;
                }
                else
                {
                    con.Open();

                    cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandText = "update MedicalRegistration set Client_ID =  '" + txtID.Text + "', Date_Registered =  '" + dtpDate.Text + "', Date_of_Released = '" + dateTimePicker1.Text + "', Client_Name =  '" + txtClient.Text + "', BirthDate =  '" + dtpBday.Text + "', Age =  '" + txtAge.Text + "', Gender =  '" + cbGender.Text + "', Civil_Status =  '" + cbStatus.Text + "', Address =  '" + txtAddress.Text + "', Cellphone_Number =  '" + mtxtCell.Text + "', Telephone_Number =  '" + mtxtTell.Text + "', Religion =  '" + txtReligion.Text + "', Place_of_Birth =  '" + txtPOB.Text + "', Primary_Education =  '" + txtPrimary.Text + "', Secondary_Education =  '" + txtSec.Text + "', Tertiary_Education =  '" + txtTertiary.Text + "', Final_Diagnosis =  '" + txtFinalDiagnosis.Text + "', Amount =  '" + txtAmount.Text + "', Claimant_Name =  '" + txtClaimant.Text + "'";
                    cmd.ExecuteNonQuery();
                    MetroMessageBox.Show(this, "Successfuly Update Data", "UPDATE Data", MessageBoxButtons.OK, MessageBoxIcon.Question);

                    con.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Try Again" + ex);
            }
        }

What I have tried:

Above is my update button code, when I pressed the update button, it throw the error violation of primary key constraint 'PK_medicalregistration'. Cannot insert duplicate key in' dbo.medical registration'. The duplicate key value is (17002). HELP!

Posted 9-Apr-17 16:36pm

Member 13111663

Updated 10-Apr-17 1:22am

Bryian Tan

v2

Add a Solution

Comments

Bryian Tan 9-Apr-17 23:00pm

The error clearly indicate the code trying to insert a record with a primary key that already exists in the table. I'm assuming Client_ID is the primary key, the update statement should have a WHERE Client_ID = txtID.Text clause right? or how the query know which record to update? Right now your query is trying to update all the records in the table.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2017-04-09T17:12:00

First of all, that SQL generation code is HORRIBLE. Google for "SQL Injection Attack" to find out why what you're doing is so bad. Google for "C# sql parameterized query" to find out what to do about it and make your code FAR easier to debug at the same time.

Your SQL UPDATE statement doesn't have a WHERE clause identifying the record you want to update. Your UPDATE statement should NEVER set the ID of a record. Your's does and since you don't have a WHERE clause, it's changing the ID of EVERY RECORD IN THE TABLE to whatever your supplied ID value is. Since you can't have more than one record with the same Primary Key ID, you're getting that error message.

Bryian Tan · Answer 2 · 2017-04-09T17:15:00

As I mentioned earlier, look like the code is missing a WHERE clause (WHERE Client_ID = txtID.Text). Right now, the query will update all the records in the database with the same information. Unless there only one record in the table, which I doubt it.

C#

cmd.CommandText = "update MedicalRegistration set Date_Registered =  '" 
                        + dtpDate.Text + "', Date_of_Released = '" + dateTimePicker1.Text + "', Client_Name =  '" 
                        + txtClient.Text + "', BirthDate =  '" + dtpBday.Text + "', Age =  '" + txtAge.Text + "', Gender =  '" 
                        + cbGender.Text + "', Civil_Status =  '" + cbStatus.Text + "', Address =  '" + txtAddress.Text + "', Cellphone_Number =  '" 
                        + mtxtCell.Text + "', Telephone_Number =  '" + mtxtTell.Text + "', Religion =  '" + txtReligion.Text + "', Place_of_Birth =  '" 
                        + txtPOB.Text + "', Primary_Education =  '" + txtPrimary.Text + "', Secondary_Education =  '" + txtSec.Text + "', Tertiary_Education =  '" 
                        + txtTertiary.Text + "', Final_Diagnosis =  '" + txtFinalDiagnosis.Text + "', Amount =  '" + txtAmount.Text + "', Claimant_Name =  '" 
                        + txtClaimant.Text + "' WHERE Client_ID=" + txtID.Text;

Once you get everything straighten up, try use Parameterized Query in the code to minimize SQL injection possibility.

Building Dynamic SQL In a Stored Procedure[^]
How to: Execute a Parameterized Query[^]

Here is an example on how the Cross-Site Scripting and SQL Injection vulnerabilities come into a nightmare.
SQL Injection and Cross-Site Scripting[^]

Patrice T · Answer 3 · 2017-04-09T18:27:00

Quote:
error violation of primary key constraint 'PK_medicalregistration'. Cannot insert duplicate key in' dbo.medical registration'. The duplicate key value is (17002).

It's a matter of knowing SQL.
A primary key is a field that have a unique value for each record, by definition, 2 records cannot have the same primary key.
SQL PRIMARY KEY Constraint[^]
SQL Primary Key[^]
SQL FOREIGN KEY Constraint[^]
SQL AUTO INCREMENT a Field[^]

Never build an SQL query by concatenating with user inputs, it is named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability.
SQL injection - Wikipedia[^]
SQL Injection[^]

kp564 · Answer 4 · 2017-04-10T01:22:00

Solution 4

you are trying to update primary key value which is not allow in SQL. From your update query remove Primary key.

Posted 10-Apr-17 1:22am

kp564

Comments

CHill60 10-Apr-17 7:52am

I'm sorry to point this out but you are wrong. SQL will allow updates to the Primary Key as long as the constraint that it must be unique is not violated.
For example with this simple table

create table testPK ( pk int PRIMARY KEY, somedata nvarchar(125))

insert into testPK values (1, 'first set of data'),(2, 'second set of data')

then this is perfectly legal

update testPK set pk = 3 where pk = 1

. Not advised perhaps, but allowed.
Besides the real problem has been identified, hours ago.

I have a error. It said violation of primary key constraint 'PK_medicalregistration'. Cannot insert duplicate key in' dbo.medical registration'. The duplicate key value is (17002) please guys help me

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0