Troubleshooting "Data type mismatch in criteria expression." during MS Access Insert.Into

Question

I'm creating a basic customer inventory application, and when converting the code from using SQL Server to using MS Access (which I'm quite a bit less versed in), I ran into a "Data type mismatch" error when trying to do a basic insert.

I've looked into several similar questions here, and double checked the msdn syntax guide, but I can't find a reason why the script I've written would generate that error. I changed my code several times to try and ensure proper data type (ending up with what I have below with explicit typing and adding the value later). I've actually even taken the string and pasted it into MS Access (sans white space and double quotes), and it seems to work just fine with the values given. At this point, I'm really and truly stumped, and I'm wondering if it might just be a quirk with the Oledb adapter? Any help would be appreciated. Thanks.

// SQL query defined elsewhere:
public static readonly string sqlAddCustomerNotes = "INSERT INTO CustomerNotes (Customer_ID, Notes, NotesDate) "
            + "VALUES(@Customer_ID, @Notes, @NotesDate);";
// end sql query

// data access function
public static void addNotes(int customerID, string notes, DateTime notesDate)
        {
            string query = Scripts.sqlAddCustomerNotes;
            using (
                OleDbCommand dbCommand = new OleDbCommand()
                {
                    Connection = new OleDbConnection(ConnectionAccess.connString),
                    CommandType = CommandType.Text,
                    CommandText = query,
                    Parameters =
                        {
                            new OleDbParameter("@Customer_ID", OleDbType.Integer),
                            new OleDbParameter("@Notes", OleDbType.LongVarChar),
                            new OleDbParameter("@NotesDate", OleDbType.DBTimeStamp)
                        }
                }) // end using parenthetical
            { // begin using scope
                dbCommand.Parameters[0].Value = customerID;
                dbCommand.Parameters[1].Value = notes;
                dbCommand.Parameters[2].Value = notesDate;
                foreach (OleDbParameter param in dbCommand.Parameters)
                { // replace ambiguous null values with explicit DBNulls.
                    if (param.Value == null)
                    {
                        param.Value = DBNull.Value;
                    }
                }
                dbCommand.Connection.Open();
                int rowsAffected = dbCommand.ExecuteNonQuery();
                dbCommand.Connection.Close();
                Console.WriteLine($"Rows affected: {rowsAffected}");
            }
        } // end addCustomerNotes

/*
table "CustomerNotes" has the following columns:datatypes
    CustomerNotes_ID: AutoNumber
    Customer_ID: Number
    Notes: Memo
    NotesDate: Date/Time
    CreateDate: Date/Time

test case (in code) was:
    @Customer_ID = 5
    @Notes = "customer might change last name to simpson."
    @NotesDate = {6/26/2019 12:05:39 PM}
*/

Is problem field the date/time? Why does it show curly braces? The delimiter in Access for date/time is #. — June7, Jun 26 '19 at 19:48
I believe the curly braces are just how visual studio denotes the contents of a DateTime object. I copied it straight from the debugger's "value" column. — Xellarant, Jun 26 '19 at 21:33
@June7 Your comment, however, does bring up an interesting point. It could be that the data adapter isn't passing the object in a way that Access recognizes (even though the type may fit). — Xellarant, Jun 26 '19 at 21:43
See comments under accepted answer for https://stackoverflow.com/questions/16217464/trying-to-insert-datetime-now-into-date-time-field-gives-data-type-mismatch-er — June7, Jun 26 '19 at 21:44
@June7 that directly and completely answers my question. Thank you! — Xellarant, Jun 27 '19 at 00:21

score 1 · Answer 1 · answered Jun 26 '19 at 20:14

1

It probably is a date, not a timestamp:

new OleDbParameter("@NotesDate", OleDbType.DBDate)

answered Jun 26 '19 at 20:14

Gustav

53,498
7
29
55

DBDate, according to the summary in the OleDbType enum, does not include a timestamp. I did specifically want to include a time (which is why the type in the database is a Date/Time). The text for timestamp does say that it maps to "DateTime" objects. – Xellarant Jun 27 '19 at 00:06
Date and timestamp is not the same, and _DBDate_ does include time if present, and your variable is DateTime not a timestamp. So, try it. – Gustav Jun 27 '19 at 06:48
Tried it. `DBDate` got rid of the time portion just as described. `Date`, however, seems to work. – Xellarant Jul 12 '19 at 20:59

score 0 · Accepted Answer · answered Jun 27 '19 at 00:17

Considering June7's comment about delimiters, it seems the issue lies in some issue inherent to the OleDbParameter type. In SQL Server terms, I do want DateTime (not Date), but representing it as a DBTimeStamp seems to make it unrecognizable by Access.

For the time being, I've sent the date as a VarChar and allowed Access to convert it however its internal engine sees fit. It feels/seems wrong, but it does, in fact, solve the problem.

Parameters =
                        {
                            new OleDbParameter("@Customer_ID", OleDbType.Integer),
                            new OleDbParameter("@Notes", OleDbType.LongVarChar),
                            new OleDbParameter("@NotesDate", OleDbType.VarChar)
                        }

EDIT: Just saw June7's latest comment, and there was in fact, an answer in another thread. OleDbType.DBDate doesn't do what I want, but OleDbType.Date does.

Troubleshooting "Data type mismatch in criteria expression." during MS Access Insert.Into

2 Answers2