Link to home
Start Free TrialLog in
Avatar of kwh3856
kwh3856Flag for United States of America

asked on

Cannot insert duplicate key row in object

I am trying to use keyfields in a table to eliminate duplicate rows.  I thought I coded for the duplicate row exception but I am now getting this error message.  Can someone show me what is missing to handle this exception.

Exact Error Message:
Cannot insert duplicate key row in object 'dbo.MPI' with unique index 'NPIACCT'.
The statement has been terminated.
while (true)
                        {
                            try
                            {
                                dcChartRelay.MPIs.InsertOnSubmit(myMPI);
                                dcChartRelay.SubmitChanges();
                            }
                            catch (DuplicateKeyException dke2)
                            {
                                MPI errObj = dke2.Object as MPI;
                                dcChartRelay.MPIs.DeleteOnSubmit(errObj);
                            }

Open in new window

Avatar of isaackhazi
isaackhazi

Avatar of Fernando Soto
Hi Kenny;

If you are not inserting more then one record in this routine then you do not need the while loop.

What I believe is happening is this, line 5 gets executed and is a duplicate key in the database then lines 10 and 11 get executed, then going back to the top of the while loop which again puts back the original duplicate record at line 5, then executing 10 and 11 again. this continues over and over again until the system finds itself in a continuous loop and just halts with the error. To see if that is the case you can move line 5 just before line 1. If that corrects the problem then just remove lines 1, 2, and 13.

Fernando
1  while (true)
2  {
3      try
4      {
5          dcChartRelay.MPIs.InsertOnSubmit(myMPI);
6          dcChartRelay.SubmitChanges();
7      }
8      catch (DuplicateKeyException dke2)
9      {
10          MPI errObj = dke2.Object as MPI;
11          dcChartRelay.MPIs.DeleteOnSubmit(errObj);
12     }
13  }

Open in new window

Avatar of kwh3856

ASKER

Fernando,
I tried that and got the same error message.  Any ideas?
Thanks
Kenny
 

                   try
                    {
                        dcChartRelay.MPIs.InsertOnSubmit(myMPI);
                        //dcChartRelay.MPIs.InsertOnSubmit(myPatPhones);
                        //dcChartRelay.MPIs.InsertOnSubmit(myPatAddresses);
                        //dcChartRelay.MPIs.InsertOnSubmit(myPatAttByDoc);
                        //dcChartRelay.MPIs.InsertOnSubmit(myPatIns);
                        //dcChartRelay.MPIs.InsertOnSubmit(myPatEmployers);
                        //dcChartRelay.MPIs.InsertOnSubmit(myPatBillAlerts);
                        //dcChartRelay.MPIs.InsertOnSubmit(myPatElgStatus);
                        dcChartRelay.SubmitChanges();
                    }
                    catch (DuplicateKeyException dke2)
                    {
                        MPI errObj = dke2.Object as MPI;
                        dcChartRelay.MPIs.DeleteOnSubmit(errObj);
                    }

Open in new window

Hi Kenny;

I made some corrections to the section of code. I also have two Console.WriteLine statements in the catch let me know if one or both print out.

Fernando
dcChartRelay.MPIs.InsertOnSubmit(myMPI);
//dcChartRelay.PATIENT_PHONE_NUMBER.InsertOnSubmit(myPatPhones);
//dcChartRelay.Patient_Address.InsertOnSubmit(myPatAddresses);
//dcChartRelay.PATIENTS_ATTENDED_BY_DOCTOR.InsertOnSubmit(myPatAttByDoc);
//dcChartRelay.PATIENT_IN.InsertOnSubmit(myPatIns);
//dcChartRelay.EMPLOYER.InsertOnSubmit(myPatEmployers);
//dcChartRelay.BillingAlert.InsertOnSubmit(myPatBillAlerts);
//dcChartRelay.EligibilityStatus.InsertOnSubmit(myPatElgStatus);
 
while (true)
{
    try
    {
        dcChartRelay.SubmitChanges();
    }
    catch (DuplicateKeyException dke2)
    {
        Console.Writeline("In catch DuplicateKeyException");
        string recType = dke2.Object.GetType().Name;
        switch (recType)
        {
            case "MPI":
                MPI errObj1 = dke2.Object as MPI;
                dcChartRelay.MPIs.DeleteOnSubmit(errObj1);
                Console.WriteLine("MPI Record");
                break;
            case "PATIENT_PHONE_NUMBER":
                PATIENT_PHONE_NUMBER errObj2 = dke2.Object as PATIENT_PHONE_NUMBER;
                dcChartRelay.PATIENT_PHONE_NUMBERs.DeleteOnSubmit(errObj2);
                break;
            case "Patient_Address":
                Patient_Address errObj3 = dke2.Object as Patient_Address;
                dcChartRelay.Patient_Addresses.DeleteOnSubmit(errObj3);
                break;
            case "PATIENTS_ATTENDED_BY_DOCTOR":
                PATIENTS_ATTENDED_BY_DOCTOR errObj4 = dke2.Object as PATIENTS_ATTENDED_BY_DOCTOR;
                dcChartRelay.PATIENTS_ATTENDED_BY_DOCTORs.DeleteOnSubmit(errObj4);
                break;
            case "PATIENT_IN":
                PATIENT_IN errObj5 = dke2.Object as PATIENT_IN;
                dcChartRelay.PATIENT_INs.DeleteOnSubmit(errObj5);
                break;
            case "EMPLOYER":
                EMPLOYER errObj6 = dke2.Object as EMPLOYER;
                dcChartRelay.EMPLOYERs.DeleteOnSubmit(errObj6);
                break;
            case "BillingAlert":
                BillingAlert errObj7 = dke2.Object as BillingAlert;
                dcChartRelay.BillingAlerts.DeleteOnSubmit(errObj7);
                break;
            case "EligibilityStatus":
                EligibilityStatus errObj8 = dke2.Object as EligibilityStatus;
                dcChartRelay.EligibilityStatus.DeleteOnSubmit(errObj8);
                break;
        };
    }
}

Open in new window

Avatar of kwh3856

ASKER

Fernando,
It seemed to run much longer this time and then finally got the same error.  The writeline never fired.  Here is a screenshot of the error.

LINQ3.jpg
Hi Kenny;

This is what I have found out in my testing. The DuplicateKeyException only checks all the records in the DataContext to make sure that any field marked as unique is not duplicated in the DataContext before sending the records to the database. This error is not that the key fields exist in the database table. I believe this is to make sure that two insert records will not cause a duplicate record when updating is done.

The error you are getting is that the key field was verified to be unique in the DataContext but when the attempt to insert the record, it found a duplicate in the database. This exception is thrown from SQL Server and not Linq.

Now the problem. The SQL exception does not return a reference to the object causing the problem and therefore can not be deleted from the DataContext as was done with the Linq DuplicateKeyException. I have looked all around the web and can not find a solution to this yet.

The following code snippet will catch the SQL exceptions thrown by the server

using System.Data.SqlClient;  // Needed for catching the SQL exceptions.

catch( SqlException sqle )
{
    // Exception code here
}

The only solution I can come up with at this time is to do one insert and then submit the change and then repeat that process until you have updated all the record. The below code snippet shows a function that will do that.


Fernando
using System.Data.SqlClient;
 
// This is calling the function passing in the DataContext as the first parameter and the record to be inserted as the second parameter. Make this type call for each record to be inserted.
UpdateTable(dcChartRelay, myMPI);
 
// ...
 
private static void UpdateTable(DataClasses1DataContext db, Object record)
{
    // Get the type of the record to insert
    string type = record.GetType().Name;
    // Then one if ststement for each table type
    if (type == "MPI")
        db.MPI.InsertOnSubmit(record as MPI);
 
    try
    {
        // Attempt to update the database
        db.SubmitChanges();
    }
    catch (DuplicateKeyException dke)
    {
        // This DuplicateKeyException is a Linq exception for duplicate keys that exist
        // in the DataContect and not the database, reason why it was not catching it.
        // Remove record from DataContext and continue.
        switch (type)
        {
            case "MPI":
                db.MPI.DeleteOnSubmit(record as MPI);
                break;
        }
    }
    catch (SqlException sqle)
    {
        // This is a duplicate record found in the databas, remove record from DataContext.
        switch (type)
        {
            case "MPI":
                db.MPI.DeleteOnSubmit(record as MPI);
                break;
        }
    }
}

Open in new window

Avatar of kwh3856

ASKER

Fernando,
Do I replace the while loop with this code or do I add it below the while loop code?  I tried replacing the while loop code and I am gettig problems with missing } in the right place.  I am trying to figure that one out right now.
Thanks
Kenny
 
ASKER CERTIFIED SOLUTION
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kwh3856

ASKER

Fernando,
Many thanks.  You went above and beyond for this question.
It is just the way I am, If I am going to do something I need to do it right. Glad I as able to help.  ;=)