SQL INSERT: Duplicate Key Value Specified?

Question

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

1. SSN 9,0 (ex. 123456789) NON-NULL
2. DATE 8,0 (ex. 20131202) NON-NULL
3. TIME 6,0 (ex. 133000) NON-NULL
4. PRINT_NEW Z (ex. 2013-12-02-11.23.47.965000) (CURRENT_TIMESTAMP used) NON-NULL
5. PRINT_OLD Z (ex. 2013-12-02-11.23.47.965000) (CURRENT_TIMESTAMP used) NULLABLE

In my application, during processing, I prompt a user if they wish to commit processed documents to a image file server. If the click YES to the dialogue, all processed documents are moved from their local folder in C: to the shared network folder.

I then have an array of all SSN's contained in the processed documents. For each one of the SSN's in the array, i call a function called updateAddrChangHistory(ssn) which 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's 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 which was processed, I either INSERT new records (denoting a NEW address letter has been printed) or UPDATE records (denoting an old address letter has been printed for 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 issue is the following:

My end user is on a virtual machine. When they run the application, let's say processing 97 new address letter records, 72 will make it into the tracking table. Each time, at some point in processing, they receive the below error (AFTER documents are successfully moved to the Image Server Processing Folder):

As best I can tell, this is stemming from my INSERT statements. What I cannot figure out however, is why several records successfully make it into the table, and then all of a sudden something causes this error. What's even more frustrating, I cannot seem to replicate the error on my own machine (even when clearing the tracking table and processing the EXACT same records as my user).

Has anyone else have experience with this error? Any ideas for what to do to fix this?

SSN is the processed SSN, DATE is the date selected from my application datepicker control, TIME is current system time, and print new is the current date-time. These four fields are all NON-NULL, and should together create a completely unique value.

Answer 1

This code has a lot of problems

Should test for an open connection before Open.

Why are you leaving the connection open?

You could use some error trapping
You will know the error is there and should be able to get more detail

You should not be using an ExecuteReader() for update or insert
Update or insert should be a ExecuteNonQuery()

Based on update and insert statements most likely SSN is a PK
Appears you are performing inserts if the docType is newAddr with no check if the SSN exists.

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
{ }