Trouble inserting DateTime into Access with OleDb

Question

I get the "data type mismatch in criteria expression" error when trying insert a row of data into Access. After messing around a little, I narrowed it down to the DateTime being the issue.

Here's my code:

class ABGDA
{
    private OleDbConnection dbConn;
    private OleDbCommand dbCmd;
    private OleDbDataReader dbReader;
    private string sConnection;
    private string sql;
    private ABG abg;

    public void insertProgressNotes(ABG ABG)
    {
        abg = ABG;

        sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                      "Data Source=SimEMR.accdb";
        dbConn = new OleDbConnection(sConnection);
        dbConn.Open();

        sql = "INSERT INTO ABG (AccountNo, LabDate, PAO2, PACO2, SAO2, Bicarbonate, BaseExcess, " + 
            "O2Setting, SetRate, SetPEEP, FiO2) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

        dbCmd = new OleDbCommand();
        dbCmd.CommandText = sql;
        dbCmd.Connection = dbConn;

        dbCmd.Parameters.Add("AccountNo", OleDbType.Integer).Value = abg.AccountNo;
        dbCmd.Parameters.Add("LabDate", OleDbType.DBTimeStamp).Value = abg.LabDate;
        dbCmd.Parameters.Add("PAO2", OleDbType.Double).Value = abg.PAO2;
        dbCmd.Parameters.Add("PACO2", OleDbType.Double).Value = abg.PACO2;
        dbCmd.Parameters.Add("SAO2", OleDbType.Double).Value = abg.SAO2;
        dbCmd.Parameters.Add("Bicarbonate", OleDbType.Double).Value = abg.Bicarbonate;
        dbCmd.Parameters.Add("BaseExcess", OleDbType.Double).Value = abg.BaseExcess;
        dbCmd.Parameters.Add("O2Setting", OleDbType.Char).Value = abg.O2Setting;
        dbCmd.Parameters.Add("SetRate", OleDbType.Double).Value = abg.SetRate;
        dbCmd.Parameters.Add("SetPEEP", OleDbType.Double).Value = abg.SetPeep;
        dbCmd.Parameters.Add("FiO2", OleDbType.Double).Value = abg.FiO2;

        dbCmd.ExecuteNonQuery();
        dbConn.Close();
    }
}

abg.LabDate was obtained using DateTime.Now

The weird thing is that I used DBTimeStamp in another class for an insert statement and than seemed to work just fine. Does anyone have an idea on what my problem might be?

UPDATE: It seems I found a solution, and I have no idea why it worked. I changed abg.LabDate to a string and saved the current date/time.

abg.LabDate = DateTime.Now.ToString();

Then when I go to insert it into the database, I parsed it back to a DateTime and that worked...

dbCmd.Parameters.Add("LabDate", OleDbType.DBTimeStamp).Value = DateTime.Parse(abg.LabDate);

Answer 1

I think the error is due to the milliseconds part present in your DateTime which will not be handled by Access so either you could truncate the milliseconds part and try the insert or in case its only DateTime.Now then use the equivalent Now() function in access.

insert into table1 (datecolumn) values (Now()) // Date() if not interested in the time part

Answer 2

I know this question is old. But I just related to this when working with both Access and MS SQL databases. Field in Access was of type Date/Time and in MSSQL of type Datetime.

My solution is was to use OleDbType.Date

    dbCmd.Parameters.Add("LabDate", OleDbType.Date).Value = DateTime.Now;