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);

Appendicular

Can you do a print on the resulting SQL from your insert statement...the SQL generated by your code just before it is passed to the DB? Your date may just need to be surrounded by octothorpes (AKA pound or number sign) – Linetta

@Linetta how do I go about doing that? When I hover over dbCmd.CommandText, it still just shows the initial string I set up – Appendicular

@TheColonel your LabDate would have milliseconds which i think will not be handled by access, so try by truncating that part. – Zeculon

I hope you are not going to run into locale problems with that, as often as not you will end up with ambiguous dates, which Access will decide should be America. If LabDate is always Now(), why not just set the default value in the table to Now() and leave date out of the update? – Cluster

#1005198 – Respondent

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

Zeculon

I somehow got it to work magically. Check my original post for what I did. No idea why it works – Appendicular

@TheColonel i guess the resultant string doesn't have the millisecond part, did you try with Now() function i think that suits you more. – Zeculon

Does the Now() function only work in the sql? Several things later on will be connected to this object with the same DateTime – Appendicular

I had the same problem. I found that if the milleseconds component of the DateTime object is 0, it works. If it is anything other than 0, it fails as you described. – Bind

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;

Sightread

See support.microsoft.com/en-us/help/320435/…. – Respondent

Mike Lowery's link takes to you Microsoft's OleDbType enumeration vs. MSAccess data types. An incredibly useful resource! – Protomorphic

Answer 3

OleDbConnection con = new OleDbConnection();
con.ConnectionString = "provider = microsoft.ace.oledb.12.0;data source = E:\\Sohkidatabase\\Sohki.accdb";
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"INSERT INTO Challan_No(challan,goods,quantity,nwlm,rate,total,ident,taaff,dateissue,nature,factory,expected,palce,date)VALUES
                (" + labelchallan.Text + ",'" + textGood.Text + "'," + combQuit.Text + "," + combNwlm.Text + "," + textRate.Text + "," + textvalu.Text + ",'" + textIdent.Text + "','" + texttfclass.Text + "','" + dateTimeIssue.Text + "','" + textNatup.Text + "','" + textFact.Text + "','" + textExpDu.Text + "','" + textPlace.Text + "','" + dateTimeDate.Text + "')";
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
System.Windows.Forms.MessageBox.Show("Recrod Succefully Created");
con.Close();

Expressivity