none
Violation of Primary key constarint sql server RRS feed

  • Question

  • I have a program written in C#. I have an INSERT SQL in its background. However i have set a single field as a primary key and as a result if u enter the same details, it throws an exception. I want to create a message box that displays to the user that the detail already exists. How do i go about. Below is the code:

            private void btnsave_Click(object sender, RoutedEventArgs e)
            {
                SqlConnection con = new SqlConnection(cs);
                con.Open();
                SqlCommand cmd = new SqlCommand("Insert into Passengers values (@ID_Number, @Name, @From, @To, @Phone, @Time_Out, @Date, @Price)", con);
                cmd.Parameters.AddWithValue("ID_Number", txtid.Text);
                cmd.Parameters.AddWithValue("Name", txtname.Text);
                cmd.Parameters.AddWithValue("From", txtfrom.Text);
                cmd.Parameters.AddWithValue("To", txt_to.Text);
                cmd.Parameters.AddWithValue("Phone", txtphone.Text);
                cmd.Parameters.AddWithValue("Time_Out", txt_time.Text);
                cmd.Parameters.AddWithValue("Date", txtdate.Text);
                cmd.Parameters.AddWithValue("Price", txtprice.Text);
                cmd.ExecuteNonQuery();

                txtid.Text = "";
                txtname.Text = "";
                txtfrom.Text = "";
                txt_to.Text = "";
                txtphone.Text = "";
                txt_time.Text = "";
                txtdate.Text = "";
                txtprice.Text = "";

            }
        }
    }

    Thursday, April 19, 2018 10:53 PM

All replies

  • I have a program written in C#. I have an INSERT SQL in its background. However i have set a single field as a primary key and as a result if u enter the same details, it throws an exception. I want to create a message box that displays to the user that the detail already exists. How do i go about. Below is the code:

    You take the key information and you do a read for the record.

    If the record exist, then you stop further processing, put out a message that the key already exist for a record in the table and leave the method, else, if record with key doesn't exist in the table, then you proceed with the record insert with key.

    Friday, April 20, 2018 12:00 AM
  • Use a Try Catch block and check for the appropriate SQL error code. Something like this:

    try
    {
       // your SQL code here
    }
    catch (SqlException e)
    {
      // your error checking here:
      if (e.Number == 2627) // Primary Key violation
      {
         // your message box code here
      }
    } 
    catch (Exception e)
    {
      // deal with other exceptions
    }
    finally
    {
      // close and dispose of objects
    }



    A full example might look like this:


    private void btnsave_Click(object sender, RoutedEventArgs e) { SqlConnection con; SqlCommand cmd; try { // your SQL code here con = new SqlConnection(cs); con.Open(); cmd = new SqlCommand("Insert into Passengers values (@ID_Number, @Name, @From, @To, @Phone, @Time_Out, @Date, @Price)", con); cmd.Parameters.AddWithValue("ID_Number", txtid.Text); cmd.Parameters.AddWithValue("Name", txtname.Text); cmd.Parameters.AddWithValue("From", txtfrom.Text); cmd.Parameters.AddWithValue("To", txt_to.Text); cmd.Parameters.AddWithValue("Phone", txtphone.Text); cmd.Parameters.AddWithValue("Time_Out", txt_time.Text); cmd.Parameters.AddWithValue("Date", txtdate.Text); cmd.Parameters.AddWithValue("Price", txtprice.Text); cmd.ExecuteNonQuery(); txtid.Text = ""; txtname.Text = ""; txtfrom.Text = ""; txt_to.Text = ""; txtphone.Text = ""; txt_time.Text = ""; txtdate.Text = ""; txtprice.Text = ""; catch (SqlException e) { // your error checking here: if (e.Number == 2627) // Primary Key violation { // your message box code here } else { // deal with other errors } } catch (Exception e)
    {
      // deal with other exceptions
    }

    finally { // if open, close the connection // if exists, dispose of the cmd object // dispose of the com object } }






    Mike Smith TechTrainingNotes.blogspot.com
    Books: SharePoint 2007 2010 Customization for the Site Owner, SharePoint 2010 Security for the Site Owner

    Friday, April 20, 2018 1:37 AM
  • Another way to avoid the duplicate primary-key issue  is to not let the user enter the primary-key,  and let the DB engine assign the primary-key itself to the table record, by using an auto-incremented primary-key assigned to the table record by the DB engine.

    https://chartio.com/resources/tutorials/how-to-define-an-auto-increment-primary-key-in-sql-server/

    You can also use MS SQL Server Management Studio to set up  auto incremented primary key for a table  too.

    Friday, April 20, 2018 2:53 AM
  • Hello Nickson,

    >>However i have set a single field as a primary key and as a result if u enter the same details

    Did you set the ID_Number column as primary key? Or you set another column as primary key field? If you are in front case, you could populate detail data by ID_Number then compare. If you are in the latter case, It means the details you want to insert don't contain primary key. You should build a query statement to check if the same details existed.

    SELECT * FROM Passengers WHERE ID_Numbers=txid.Text And Name=txtname.Text 
        AND ...

    Best Regards,

    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 20, 2018 6:10 AM
    Moderator