1

I have a SQL statement causing an error within C# on a MS Access database that seems to intermittently work. I think I tracked down the problem, but don't understand the answer or have a solution.

My SQL statement in C# is written as follows:

// note that Start and StartLog are both nullable variables and ProfileID variable is a string

cmd = new OleDbCommand("INSERT INTO shifts(profile_id, start, start_log) " +
                "VALUES (@profile_id, @start, @start_log);");
cmd.Parameters.AddWithValue("@profile_id", ProfileID);
cmd.Parameters.AddWithValue("@start", Start.Value);
cmd.Parameters.AddWithValue("@start_log", StartLog.Value);

The shifts table has the following columns:

profile_id is a string
start is a date/time
start_log is a date_time

I tried to execute the statement with the following data pulled from the debugger:

profile_id -> "16078965744"
start -> {1/10/2015 1:30:00 PM}
start_log -> {1/10/2015 1:23:13 PM}

and got the following exception:

System.DataOleDb.OleDbException: {"Data type mismatch in criteria expression."}

I noticed that if I called ToString() on Start.Value and StartLog.Value as follows it works:

cmd = new OleDbCommand("INSERT INTO shifts(profile_id, start, start_log) " +
                "VALUES (@profile_id, @start, @start_log);");
cmd.Parameters.AddWithValue("@profile_id", ProfileID);
cmd.Parameters.AddWithValue("@start", Start.Value.ToString());
cmd.Parameters.AddWithValue("@start_log", StartLog.Value.ToString());

My suspicion is that the problem is related to this SO answer by Steve that the the millisecond part is causing the exception by a call earlier to DateTime.Now.

Anyone have a solution to this or know what the problem is?

I suspect this will solve my problem, but I am a beginner so frankly I am confused by the whole ordeal.

This problem came out of this question here that is unresolved that I am investigating.

Community
  • 1
  • 1
YouAreSalty
  • 105
  • 10
  • are ProfileID (from DB) and @profile_id String? – Mark Jan 10 '15 at 22:01
  • @Mark `ProfileID` is a string instance variable, whereas `@profile_id` is the parameter to OleDB and is also a string. – YouAreSalty Jan 10 '15 at 22:10
  • Sounds like the DB has Start and StartLog defined as string rather than Date otherwise you should still get a mismatch passing string to a Date col – Ňɏssa Pøngjǣrdenlarp Jan 10 '15 at 22:22
  • @Plutonix that is precisely not the case. I also double checked the DB table and both columns are listed as Date/Time. – YouAreSalty Jan 10 '15 at 22:28
  • In that case, remove the `.ToString`, to pass dates; then examine parameters in debug after both AddWithValue statements execute and see what those 2 parameter datatypes are (esp if they are different Date vs TimeSpatmp 8I think*). AddWithValue allows OleDB to interpret the data type based on the data passed. Are these comining from a DTP? – Ňɏssa Pøngjǣrdenlarp Jan 10 '15 at 22:34

1 Answers1

0

Yes, it is almost certainly the milliseconds that are causing the problem if you have derived the parameter values from an earlier call to DateTime.Now in .NET.

You can get rid of the milliseconds using the methods in the answers to the previous question you cited here, or you could just format the parameters as yyyy-MM-dd HH:mm:ss strings and let Access OLEDB convert them back to Access Date/Time values, i.e., 

cmd.Parameters.AddWithValue("@profile_id", ProfileID);
cmd.Parameters.AddWithValue("@start", Start.Value.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("@start_log", StartLog.Value.ToString("yyyy-MM-dd HH:mm:ss"));
Community
  • 1
  • 1
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Is this a potential pitfall for different time formats in different regions? – YouAreSalty Jan 11 '15 at 10:59
  • @YouAreSalty - Quite the opposite. Explicitly formatting the date as `yyyy-MM-dd` makes it unambiguous so it will work regardless of the short date format specified in Windows. Other formats, especially `dd-MM-yyyy` can potentially cause problems because of the way the Access Database Engine interprets date literals. – Gord Thompson Jan 11 '15 at 11:17
  • I marked yours as the answer, but I ended up using the extension method mentioned in my original post, and updating the class to always truncate to the nearest second. Saved me from having to edit every instance and add a ToString() since everywhere that I call those properties it will be pre-truncated. – YouAreSalty Jan 12 '15 at 18:35