How to prevent insert of duplicated records SQL server using C# windows application

Question

0.00/5 (No votes)

See more:

I'm getting an error when I insert same data in textbox txtKVNumber. The error is like: An error has occurred, please try again later. Violation of PRIMARY KEY constraint 'PK_khaas'. Cannot insert duplicate key in object 'dbo.khaas'. The statement has been terminated.

What I have tried:

private void btnKSave_Click(object sender, EventArgs e)
       {
           try
           {
               if (txtKVNumber.Text == "")
               {
                   string myStringVariable1 = string.Empty;
                   MessageBox.Show("Vehicle Number is required");
               }
               else if (cboKVColor.Text == "")
               {
                   string myStringVariable2 = string.Empty;
                   MessageBox.Show("Select Vehicle Color");
               }
               else if (cboKVBrand.Text == "")
               {
                   string myStringVariable3 = string.Empty;
                   MessageBox.Show("Select Vehicle Brand");
               }
               else if (cboKVType.Text == "")
               {
                   string myStringVariable12 = string.Empty;
                   MessageBox.Show("Select Vehicle Type");
               }
               else if (txtKOName.Text == "")
               {
                   string myStringVariable5 = string.Empty;
                   MessageBox.Show("Owner Name is required");
               }
               else if (txtKCivilID.Text == "")
               {
                   string myStringVariable7 = string.Empty;
                   MessageBox.Show("Civil ID is required");
               }
               else if (txtKTelephone.Text == "")
               {
                   string myStringVariable8 = string.Empty;
                   MessageBox.Show("Telephone Number is required");
               }

               else
               {
                   command = new SqlCommand("insert into khaas( VNumber, VColor, VType, VBrand, ExpiryDate, DaysLeft, OName, CivilID, Telephone ) VALUES (@vnumber, @vcolor, @vtype, @vbrand, @expirydate, @daysleft, @ownername, @civilid, @telephone )", con);
                   con.Open();
                   command.Parameters.AddWithValue("@vnumber", txtKVNumber.Text);
                   command.Parameters.AddWithValue("@vcolor", cboKVColor.Text);
                   command.Parameters.AddWithValue("@vtype", cboKVType.Text);
                   command.Parameters.AddWithValue("@vbrand", cboKVBrand.Text);
                   command.Parameters.AddWithValue("@expirydate", dateTimePickerKhaas.Value.ToString("MM/dd/yyyy"));
                   command.Parameters.AddWithValue("@daysleft", txtKDaysLeft.Text);
                   command.Parameters.AddWithValue("@ownername", txtKOName.Text);
                   command.Parameters.AddWithValue("@civilid", txtKCivilID.Text);
                   command.Parameters.AddWithValue("@telephone", txtKTelephone.Text);
                   command.ExecuteNonQuery();
                   con.Close();
                   MessageBox.Show("Inserted Successfully");
                   grd_fillKhaas();
                   txtKVNumber.Text = "";
                   cboKVColor.Text = "";
                   cboKVType.Text = "";
                   cboKVBrand.Text = "";
                   dateTimePickerKhaas.Value = DateTime.Now;
                   txtKDaysLeft.Text = "";
                   txtKOName.Text = "";
                   txtKCivilID.Text = "";
                   txtKTelephone.Text = "";
               }
           }
           catch (Exception ex)
           {
               MessageBox.Show("An error has occurred, please try again later." + ex.Message);

           }
           finally
           {
               con.Close();
           }
       }

Posted 12-Jul-17 21:42pm

Member 13151446

Updated 13-Jul-17 14:25pm

Add a Solution

Comments

Richard MacCutchan 13-Jul-17 3:47am

You need to check the database first to see if that number already exists.

Andy Lanng 13-Jul-17 4:33am

Either that or add a Where not exists (select * from t where id = @id).

the command command.ExecuteNonQuery(); returns an int of how many rows were inserted. If this is zero then it already existed. It's a bit longer winded in code but cuts down on sql calls

F-ES Sitecore 13-Jul-17 4:32am

In addition to checking the data for the primary key doesn't already exist (we don't know your db schema so we don't know what field(s) are in your PK) you should also gracefully handle this error using try\catch error handling and report to the user as it is possible that the data is inserted between you checking if it is there and inserting it yourself.

Also it could be that there is a problem with your data and you mean for it to have a different value than it does. For example if you have an integer field that is a PK and you are not handling your data correctly and a 0 goes in as the value, then the next time you don't handle your data correctly and a 0 goes in again you'll get this message.

Basically this is something no-one can really help you with, you need to look at your data and inputs and work out why you're get the error, what the reason for the error is and what you want to do to solve it, we can't do that for you.

1 solution

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)

Patrice T · Answer 1 · 2017-07-13T14:25:00

Quote:
Violation of PRIMARY KEY constraint 'PK_khaas'. Cannot insert duplicate key in object 'dbo.khaas'. The statement has been terminated.

That is the principle of PRIMARY KEY.
By principle, SQL do not allow 2 records to share the same PRIMARY KEY as it is the value identifying each records and thus must be unique.

Quote:
How to prevent insert of duplicated records SQL server using C# windows application

You can check if key exist before inserting new record.
Or even better, have the server generate a unique key for you.