SQL INSERT: duplicate key value set?

I have a tracking table in the AS400 system in LIBRARY1.TRACKINGTABLE. This table has 5 fields:

  • SSN 9.0 (e.g. 123456789) NON-NULL
  • DATE 8.0 (e.g. 20131202) NON-NULL
  • TIME 6.0 (e.g. 133000) NON-NULL
  • PRINT_NEW Z (e.g. 2013-12-02-11.23.47.965000) (using CURRENT_TIMESTAMP) NON-NULL
  • PRINT_OLD Z (e.g. 2013-12-02-11.23.47.965000) (using CURRENT_TIMESTAMP) NULLABLE

In my application, during processing, I suggest to the user if they want to transfer processed documents to the image file server. If you click YES in the dialog box, all processed documents will be moved from their local folder in C: to the shared network folder.

Then I have an array of all the SSNs contained in the processed documents. For each of the SSNs in the array, I call a function with a name updateAddrChangHistory(ssn)that updates my tracking table:

            // PROMPT USER TO COMMIT
            DialogResult dResult = MessageBox.Show("Mail Merge Completed. Individual Documents Saved to C:\\TEMP\\to fyi\\. \n\n Commit generated documents to NetFYI?", "Confirm Commit", MessageBoxButtons.YesNo);
            if (dResult == DialogResult.Yes)
            {
                moveLocalToCommitFYI();

                // If selected letter is New/Old Address Letter, and thus tracked in table LIBRARY.TRACKINGTABLE,
                // update the tracking table using SSN stored in SSNsArray.
                if (cmbLetterType.SelectedIndex < 2)
                {
                    // Now that user has "theoretically" printed and Commited the processed documents, update Change History Table
                    foreach (string ssn in SSNsArray)
                    {
                        if (ssn != null && ssn.Length > 0)
                        {
                            updateAddrChngHistory(ssn);
                        }
                    }
                }
                SSNsArray = new string[0]; // Clear the array for next processing
                updatePrintedCntLabel();
            }

Then, depending on the type of document that was processed, I either entered INSERT new records (denoting a new address letter) or UPDATE records (the designation of the old address letter was printed for the same member).

    public void updateAddrChngHistory(string SSN)
    {
        // Update/Modify Tracking Table
        string formattedDate = dtpDate.Value.ToString("yyyyMMdd");
        //string formattedTime = DateTime.Now.TimeOfDay.ToString("HHmmss");
        string query = "";
        switch (docType)
        {
            case "oldAddr":
                query = "UPDATE LIBRARY.TRACKINGTABLE SET PRINT_OLD = CURRENT_TIMESTAMP WHERE SSN = " + SSN + " AND PRINT_OLD IS NULL";
                break;
            case "newAddr":
                query = "INSERT INTO LIBRARY.TRACKINGTABLE (SSN, DATE, TIME, PRINT_NEW, PRINT_OLD) VALUES (" + SSN + ", " + formattedDate + ", " + System.DateTime.Now.ToString("HHmmss") + ", CURRENT_TIMESTAMP, NULL)";
                break;
            case "nameChg":

                break;

             case "nameChgWAR":

                break;
        }

        mdl.InsertUpdateData(query);
        // Close Connection
        mdl.closeConn();
    }

mdl.InsertUpdateDate() is a class file function:

namespace MergeDoc
{
    public class MergeDocClassLibrary
    {
        OdbcConnection conn = new OdbcConnection();

        public void InsertUpdateData(string query)
        {
            // PROD
            string connString = "DRIVER=Client Access ODBC Driver (32-bit); SYSTEM=XX.XX.X.XX; UID=XYXYXYZ; PWD=YXZYXZY";
            // DEV
            //string connString = ""

            OdbcCommand cmd = new OdbcCommand(query, conn);

            // Set connection using connectionString
            conn.ConnectionString = connString;
            // Open Connection
            conn.Open();
            // Execute command and store in OBDC DataReader
            cmd.ExecuteReader();
        }
    }
}

My problem is this :

My end user is in a virtual machine. When they launch the application, say, processing 97 new entries of address letters, 72 will enter the tracking table. Each time, at some point in processing, they receive an error below (AFTER documents have been successfully moved to the Image Server processing folder):

Duplicate key error

, INSERT. , , - . , ( , ).

- ? , , ?

SSN - SSN, DATE - , datepicker control, TIME - , print new - -. NON-NULL .

+3
2

.

?



,

ExecuteReader()
ExecuteNonQuery()

, , SSN PK
, , docType - newAddr , SSN.

if(string.IsNullOrEmpty(query)) return;
try 
{
    OdbcCommand cmd = new OdbcCommand(query, conn);
    conn.ConnectionString = connString;
    conn.Open();
    cmd.ExecuteReader();
}
catch (OdbcException Ex) 
{
    Debug.WriteLine(Ex.Message);
    throw Ex;
}
finally
{ } 
+3

SQL-. SSN, . 10 , , , .

() . - , "" "" , . . , SSN 123 . , 123 . , "". , 5 , SSN 123. , 123 , "". updateAddrChngHistory, SSN 123 INSERTed , 5 , SSN 123 , INSERT . , .

, SQL:

INSERT INTO LIBRARY.TRACKINGTABLE 
  (SSN, DATE, TIME, PRINT_NEW, PRINT_OLD) 
  VALUES (" + SSN + ", " + formattedDate + ", " + 
         System.DateTime.Now.ToString("HHmmss") + ", CURRENT_TIMESTAMP, NULL)
  where ssn not in (select ssn from library.trackingtable)
+2

All Articles