Why am I getting "Data type mismatch in criteria expression" with this code?

Question

I am trying to insert a record into an MS Access table with this code:

public void InsertIntoPPTData(string tx, string site_no, string xmlfile, DateTime collect_dttm, string ppt_user, string tx_memo, string file_beg, string file_end)
{
    string connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Duckbilled;Password=Platypus;Data Source=C:\RGT\NotSoBigData.MDB;Jet OLEDB:System database=C:\CCRWin\Data\jftfdcsc.mdw";
    using (var conn = new OleDbConnection(connStr))
    {
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = 
                @"INSERT INTO tx_tillermantea (tx, site_no, xmlfile, collect_dttm, ppt_user, tx_memo, file_beg, file_end) 
                  VALUES(@txval, @siteNum, @xmlfileName, @DTNow, @PPTUser, @TXMemo, @strfile_beg, @strfile_end)";
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@txval", tx);
            cmd.Parameters.AddWithValue("@siteNum", site_no);
            cmd.Parameters.AddWithValue("@xmlfileName", xmlfile);
            cmd.Parameters.AddWithValue("@DTNow", collect_dttm);
            cmd.Parameters.AddWithValue("@PPTUser", ppt_user);
            cmd.Parameters.AddWithValue("@TXMemo", tx_memo);
            cmd.Parameters.AddWithValue("@strfile_beg", file_beg);
            cmd.Parameters.AddWithValue("@strfile_end", file_end);
            conn.Open();
            cmd.ExecuteNonQuery();
        }
    }
}

...but the "cmd.ExecuteNonQuery();" line blows up with:

System.Data.OleDb.OleDbException was unhandled by user code Message=Data type mismatch in criteria expression. Source=Microsoft Office Access Database Engine StackTrace: at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

My guess is that the problem is the lone DateTime value, but what is wrong with:

cmd.Parameters.AddWithValue("@DTNow", collect_dttm);

?

I am passing "DateTime.Now" as the collect_dttm arg.

Check this answer http://stackoverflow.com/questions/16217464/inserting-dates-in-an-ms-access-database-again/16218074#16218074 — Steve, Feb 27 '14 at 19:51
@HeenaChhatrala and Sudhakar: I wish I could - the .MDB does not afford a right-click "Design View" context menu item for some reason. I can open the table and see its contents, and clicking the sorting down-arrow indicates that all fields are string except for "collect_dttm" which is date. I say this because they all say, "Text Filters" except for "collect_dttm" which says "Date Filters" — B. Clay Shannon-B. Crow Raven, Feb 27 '14 at 20:10

score 2 · Accepted Answer · answered Feb 27 '14 at 21:42

The link provided shows at least two fixes for this; I found the most straightforward one to be simply removing the DateTime parameter from the mix and directly injecting the dateTime value from within the Insert statement by replacing the "DTNow" param with a call to "Now()" like so:

cmd.CommandText = 
    @"INSERT INTO tx_tillermantea (tx, site_no, xmlfile, collect_dttm, ppt_user, tx_memo, file_beg, file_end) 
      VALUES(@txval, @siteNum, @xmlfileName, Now(), @PPTUser, @TXMemo, @strfile_beg, @strfile_end)";

Why am I getting "Data type mismatch in criteria expression" with this code?

1 Answers1