• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4505
  • Last Modified:

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

0
kwh3856
Asked:
kwh3856
  • 5
  • 4
1 Solution
 
Fernando SotoCommented:
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

0
 
kwh3856Author Commented:
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

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Fernando SotoCommented:
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

0
 
kwh3856Author Commented:
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
0
 
Fernando SotoCommented:
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

0
 
kwh3856Author Commented:
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
 
0
 
Fernando SotoCommented:
Hi Kenny;

The code below is your code modified with the code I posted. I marked the function call with

// =======================
Function call here
// =======================

Fernando
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.SqlClient;
 
 
namespace eClinicalReadData
{
    class Program
    {
        static void Main(string[] args)
        {
 
            
            DataClasses1DataContext dcChartRelay = new DataClasses1DataContext();
 
            DataClasses2DataContext dceClinicalWorks = new DataClasses2DataContext();
 
           
            // Query to get all patients from the Patients table
            // that have not been flagged as imported
            var patients = from p in dceClinicalWorks.patients
                           where p.Imported == null
                           join d in dceClinicalWorks.doctors on p.doctorId equals d.doctorID
                           join u in dceClinicalWorks.users on p.pid equals u.uid
                           join i in dceClinicalWorks.patientinfos on p.pid equals i.pid
                           select new {d,u,i,p};
     
 
            // Process each row of the result set in the foreach loop
            foreach (var patient in patients)
            {
                    // Write the MPI Record 
 
                    if (string.IsNullOrEmpty(patient.u.ssn))
                    {
                        //Console.WriteLine("SSN is EMPTY");
                    }
                    else
                    {
                        //Console.WriteLine("SSN: {0}", patient.ssn.ToString());
                    }
 
          
                    MPI myMPI = new MPI();
                    PATIENT myCRpatient = new PATIENT();
                    patientinfo myPatientInfo = new patientinfo();
                    user myUser = new user();
                    PATIENT_PHONE_NUMBER myPatPhones = new PATIENT_PHONE_NUMBER();
                    Patient_Address myPatAddresses = new Patient_Address();
                    PATIENTS_ATTENDED_BY_DOCTOR myPatAttByDoc = new PATIENTS_ATTENDED_BY_DOCTOR();
                    PATIENT_IN myPatIns = new PATIENT_IN();
                    EMPLOYER myPatEmployers = new EMPLOYER();
                    BillingAlert myPatBillAlerts = new BillingAlert();
                    EligibilityStatus myPatElgStatus = new EligibilityStatus();
 
                                   
 
                    Guid initGuid = Guid.NewGuid();
                    myMPI.MPI1 = initGuid;
                    //myMPI.SYSTEM_ACCT_NUMBER = patient.pid;
                    myMPI.NPI = patient.d.doctorID;
                    myMPI.ModifiedDate = DateTime.Today;
 
                    myMPI.EntitySystemAcctNum = patient.p.pid.ToString();
 
                    myMPI.EntitySystemModifiedDate = DateTime.Today;
                    
                    if (string.IsNullOrEmpty(patient.u.ssn))
                    {
                        // Do not write any data
                    }
                    else
                    {
                 
                        string ssn = patient.u.ssn;
                        ssn = patient.u.ssn.Sanitize();
 
                    // Write the Patient demographic record
 
 
                        myCRpatient.SEX = patient.u.sex[0];
 
                        
 
 
 
                    myCRpatient.SSN = patient.u.ssn;
                    myCRpatient.LAST_NAME = patient.u.ulname;
                    myCRpatient.FIRST_NAME = patient.u.ufname;
                    
                    // myCRpatient.PREFIX = patient.p.pre
                    // myCRpatient.SUFFIX =
                    // myCRpatient.COUNTRY = patient.p.cou
                    myCRpatient.EMAIL = patient.u.uemail;
                    // myCRpatient.RELIGION = patient.p.re
                    myCRpatient.RACE = patient.p.race;
                    myCRpatient.UPDATED_DATE_TIME = DateTime.Today;
                    // myCRpatient.FACILITY_TIN = patient.i.---------Will be hard coded for each program
 
                    
                    //myCRpatient.SELF_EMPLOYED = patient.p.em
                    //myCRpatient.UPDATED_BY = patient.p.m
                    myCRpatient.PRIMARY_LANGUAGE = patient.p.language;
                    myCRpatient.MARITAL_STATUS = patient.p.maritalstatus;
                    // myCRpatient.PRIMARY_CARE_PHYSICIAN_NPI = patient.i.------Add to physician table
                    //myCRpatient.ORGAN_DONAR = patient.p.or
                    // myCRpatient.DISABILITY_PERCENT = patient.p.disa
 
 
                    string tmpDateTime = patient.p.deceasedDate.ToString();
                    if (tmpDateTime.Length > 0)
                    {
                        DateTime tmpDateTime2;
 
                        if (DateTime.TryParse(patient.p.deceasedDate.ToString(), out tmpDateTime2))
                        {
                            myCRpatient.DECEASED_DATE = tmpDateTime2;
                        }
                        else
                        {
                            // failed, invalid date format
                        }
                    }
                    else
                    {
                        // failed, DateTime is null
                    }
 
               
                    //myCRpatient.HEAD_OF_HOUSEHOLD = patient.p.hea
                    // myCRpatient.PATIENT_RELATIONSHIP_TO_INSURED = patient.p.rel
                    //  myCRpatient.COUNTY = patient.u.ucoun ------  Do a lookup based on zip 
 
 
                    string tmp2DateTime = patient.u.dob.ToString();
                    if (tmp2DateTime.Length > 0)
                    {
                        DateTime tmp2DateTime2;
 
                        if (DateTime.TryParse(patient.u.dob.ToString(), out tmp2DateTime2))
                        {
                            myCRpatient.DATE_OF_BIRTH = tmp2DateTime2;
                        }
                        else
                        {
                            // failed, invalid date format
                        }
                    }
                    else
                    {
                        // failed, DateTime is null
                    }
 
 
                    //myCRpatient.DATE_OF_BIRTH = patient.u.dob;
 
                    // Add these to Patient_Phone_Numbers
                    myPatPhones.MPI = initGuid;
                    myPatPhones.SourceOfRecord = patient.d.doctorID;
 
                    int myInt;
                    if (Int32.TryParse(patient.u.upPhone, out myInt))
                    {
                        // Success! myInt now contains the numeric value of myString
                        myPatPhones.PhoneNumber = myInt;
                    }
                    else
                    {
                        // Failed! MyInt is not defined.
                    }
 
                    
                    myPatPhones.TypeOfNumber = "Home";
                    myPatPhones.DateOfRecord = DateTime.Today;
  
                    // Add these to Patient_Addresses
                    myPatAddresses.MPI = initGuid;
                    myPatAddresses.Source = patient.d.doctorID;
                    myPatAddresses.Address = patient.p.straddress;
                    myPatAddresses.City = patient.p.city;
                    myPatAddresses.State = patient.p.state;
                    myPatAddresses.Zip = patient.p.zip;
                    myPatAddresses.DateOfRecord = DateTime.Today;
 
 
                    myPatEmployers.MPI = initGuid;
                    myPatEmployers.SourceNPI = patient.d.doctorID;
                    myPatEmployers.CompanyName = patient.p.employername;
                    myPatEmployers.ADDRESS_1 = patient.p.employeraddress;
                    myPatEmployers.ADDRESS_2 = patient.p.employeraddress2;
                    myPatEmployers.CITY = patient.p.employercity;
                    myPatEmployers.STATE = patient.p.employerstate;
                    myPatEmployers.ZIP = patient.p.employerzip;
                    // myPatEmployers.CONTACT_FIRST_NAME = patient.p.
                    myPatEmployers.PHONE = patient.p.employerPhone;
                    // myPatEmployers.OCCUPATION = patient.p.
                    
                        
 
                    // setup foreach loop to lookup insurance comp npi and adress info
                    var ecinscomps = from ecins in dceClinicalWorks.insurances
                           where ecins.insId == patient.p.insId
                           select new {ecins};
 
                    // Process each row of the result set in the foreach loop
                    foreach (var insurance in ecinscomps)
 
                    {
                        myPatIns.MPI = initGuid;
                        myPatIns.ProviderNPI = patient.d.doctorID;
 
 
                        int myInsId;
                        if (Int32.TryParse(patient.p.insId.ToString(), out myInsId))
                        {
                            // Success! myInt now contains the numeric value of myString
                            myPatIns.Id = myInsId.ToString();
                        }
                        else
                        {
                            // Failed! MyInt is not defined.
                        }
    
                        myPatIns.GroupId = patient.p.insgroupno;
                        //myPatIns.CoPay = patient.p.inscopay;
                        
                        
                        myPatIns.ADDRESS_1 = insurance.ecins.insuranceaddress;
                        myPatIns.ADDRESS_2 = insurance.ecins.insuranceaddress2;
                        myPatIns.CITY = insurance.ecins.insurancecity;
                        myPatIns.STATE = insurance.ecins.insurancestate;
                        myPatIns.ZIP = insurance.ecins.insurancezip;
                        myPatIns.PHONE = insurance.ecins.insurancephone;
                        myPatIns.Phone2 = insurance.ecins.insurancephone2;
                        myPatIns.FAX_PHONE = insurance.ecins.insurancefax;
                        myPatIns.ClaimOfficePhone = insurance.ecins.ClaimOfficeNumber;
                        myPatIns.email = insurance.ecins.insuranceemail;
                        myPatIns.MediGapId = insurance.ecins.MedigapID;
                        myPatIns.website = insurance.ecins.website;
 
                    }
 
                    short blalrt;
                    blalrt = patient.p.BillingAlert;
 
 
                    short myBillAlert;
                    if (short.TryParse(patient.p.BillingAlert.ToString(), out myBillAlert))
                    {
                        // Success! myInt now contains the numeric value of myString
                        myPatBillAlerts.BillingAlert1 = myBillAlert.ToString();
                    }
                    else
                    {
                        // Failed! MyInt is not defined.
                    }
 
                    
                    myPatBillAlerts.BillingAlertNotes = patient.p.BillingAlertNotes;
 
                    short myCollection;
                    if (short.TryParse(patient.p.BillingAlert.ToString(), out myCollection))
                    {
                        // Success! myInt now contains the numeric value of myString
                        myPatBillAlerts.GivenToCollection = myCollection.ToString();
                    }
                    else
                    {
                        // Failed! MyInt is not defined.
                    }
                    
                    myPatBillAlerts.GivenDate = patient.p.givenDate;
                    myPatBillAlerts.CollectionAgencyStatus = patient.p.CollectionAgencyStatus;
 
                    myPatElgStatus.ElgibilityStatus = patient.u.EligibilityStatus;
                    myPatElgStatus.EligibilityMessage = patient.u.EligibilityMessage;
                    myPatElgStatus.DateEligibilityObtained = DateTime.Today;
 
                    
                    // PastHistory
                    // Pharmacy Name
                    // Surgical History
                    // Hospitalization
                    // Medications
                    // Pharmacy Fax
                    
                    // Notes
                    
                    // Deceased Notes
                    // Student Status
                    
                    // PicFileName
                    // SelfPay
 
                    // ===================================================
                    // Remove this line and replace it with the function call
                    // dcChartRelay.MPIs.InsertOnSubmit(myMPI);
                    // This line of code now does what the above was doing
                    UpdateTable(dcChartRelay, 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)
                        //    {
                        //        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;
                        //        };
                        //    }
 
                        //}
 
                    }                                       
            }
        }
 
        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.MPIs.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.MPIs.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.MPIs.DeleteOnSubmit(record as MPI);
                        break;
                }
            }
        }
   }
}

Open in new window

0
 
kwh3856Author Commented:
Fernando,
Many thanks.  You went above and beyond for this question.
0
 
Fernando SotoCommented:
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.  ;=)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now