Search code examples
c#oledb

Catch Duplicate Entry OleDbException & Over-write

enter image description here

I get this error while processing entries when trying to add rows to my Access database. The issue is that some of the entries I'm processing are unique, and some may have a duplicate primary key with updates to the related columns. But, since some have a duplicated primary key, everything being processed fails.

I want to over-write the duplicate entries with the new data and add the new entries. How do I do this?

I did some testing below to try and get the part of the issue with everything failing solved using a try / catch block, to no avail:

            using (OleDbConnection myCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Lenovo\\Desktop\\Weather Cruncher\\wcMainDB.accdb"))
            {
                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandText = "Insert into stations_NOAA " +
                    "([stationID],[stationName],[stationMinDate],[stationMaxDate],[stationDataCoverage]," +
                    "[stationLatitude],[stationLongitude],[stationElevation],[stationElevationUnit])" +
                    "values (?,?,?,?,?,?,?,?,?)";
                cmd.Connection = myCon;
                myCon.Open();

                // Populate Access Database
                foreach (Station loc in noaa.results)
                {
                    try
                    {
                        cmd.Parameters.AddWithValue("@stationID", loc.id); // Short Text data type
                        cmd.Parameters.AddWithValue("@stationName", loc.name); // Short Text data type
                        cmd.Parameters.AddWithValue("@stationMinDate", loc.mindate); // Short Text data type
                        cmd.Parameters.AddWithValue("@stationMaxDate", loc.maxdate); // Short Text data type
                        cmd.Parameters.AddWithValue("@stationDataCoverage", loc.datacoverage); // Number data type
                        cmd.Parameters.AddWithValue("@stationLatitude", loc.latitude); // Number data type
                        cmd.Parameters.AddWithValue("@stationLongitude", loc.longitude); // Number data type
                        cmd.Parameters.AddWithValue("@stationElevation", loc.elevation); // Number data type
                        cmd.Parameters.AddWithValue("@stationElevationUnit", loc.elevationUnit); // Short Text data type
                        cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                    catch(Exception ex)
                    {
                        if (ex.Message.Contains("duplicate values")) // It is a primary key violation, ignore
                        {
                            Console.WriteLine("Dupe skipped.");
                            continue;
                        }
                        else
                        {
                            MessageBox.Show(ex.ToString() + "\n\n" + ex.Message);
                        }
                    }
                }
            }

It was processing 20 queries, of which 15 where unique. I watched it step-thru while comparing the primary keys to the data the program was processing. How can these two issues be addressed?

Solution

  • Perhaps consider something like this... assuming that Station_ID is a unique field...

    foreach (Station loc in noaa.results)
{
    cmdCount.CommandText = "SELECT count(*) from stations_NOAA WHERE StationId = @station_id";
    cmdCount.Parameters.AddWithValue("@station_id", loc.id);
    int count = (int)cmdCount.ExecuteScalar();

    if (count > 0)
    {
         // UPDATE STATEMENT
         cmd.CommandText "UPDATE stations_NOAA Set stationMinDate = @stationMinDate, Set StationMaxDate = @stationMaxDate, ...
             where StationID = @stationID;"
        cmd.Parameters.AddWithValue("@stationID", loc.id); // Short Text data type
        cmd.Parameters.AddWithValue("@stationMinDate", loc.mindate); // Short Text data type
        cmd.Parameters.AddWithValue("@stationMaxDate", loc.maxdate); // Short Text data type
        ...
        ...
        cmd.ExecuteNonQuery();
    }
    else
    {
         // INSERT STATEMENT
         commandText = ("Insert into stations_NOAA " +
                        "([stationID],[stationName],[stationMinDate],[stationMaxDate],[stationDataCoverage]," +
                        "[stationLatitude],[stationLongitude],[stationElevation],[stationElevationUnit])" +
                        "values (?,?,?,?,?,?,?,?,?)";
         cmd.Parameters.AddWithValue("@stationID", loc.id); // Short Text data type
        cmd.Parameters.AddWithValue("@stationMinDate", loc.mindate); // Short Text data type
        cmd.Parameters.AddWithValue("@stationMaxDate", loc.maxdate); // Short Text data type
        ...
        ...
        cmd.ExecuteNonQuery();
    }
    cmd.Parameters.Clear();
}

    And if all the parameters are the same in the Update and the Insert then this pattern:

    foreach (Station loc in noaa.results)
{
    cmd.CommandText = "SELECT count(*) from stations_NOAA WHERE StationId = @station_id", myConnection);
    cmdCount.Parameters.AddWithValue("@station_id", loc.id);
    int count = (int)cmdCount.ExecuteScalar();

    if (count > 0)
        cmd.CommandText "UPDATE stations_NOAA " _
            "Set stationMinDate = @stationMinDate, " _
            "Set StationMaxDate = @ StationMaxDate, " _
               ...
            "where StationID = @stationID;"

    else
        commandText = "Insert into stations_NOAA " +
                        "([stationID],[stationName],[stationMinDate],[stationMaxDate],[stationDataCoverage]," +
                        "[stationLatitude],[stationLongitude],[stationElevation],[stationElevationUnit])" +
                        "values (@stationID,@stationMinDate, @ ...)";

    cmd.Parameters.AddWithValue("@stationID", loc.id); // Short Text data type
    cmd.Parameters.AddWithValue("@stationMinDate", loc.mindate); // Short Text data type
    cmd.Parameters.AddWithValue("@stationMaxDate", loc.maxdate); // Short Text data type
    ...
    ...

    cmd.ExecuteNonQuery();      

    cmd.Parameters.Clear();
}

    But that only works if both the Insert and Update have the same fields and it's a lot cleaner/easier to read.