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

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) — gangreen, Mar 02 '12 at 05:04
@gangreen how do I go about doing that? When I hover over dbCmd.CommandText, it still just shows the initial string I set up — athom, Mar 02 '12 at 05:19
@TheColonel your LabDate would have milliseconds which i think will not be handled by access, so try by truncating that part. — V4Vendetta, Mar 02 '12 at 05:24
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? — Fionnuala, Mar 02 '12 at 10:22
https://stackoverflow.com/questions/1004698/how-to-truncate-milliseconds-off-of-a-net-datetime/1005222#1005222 — Mike Lowery, Mar 13 '19 at 23:35

score 8 · Accepted Answer · answered Mar 02 '12 at 05:28

8

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

answered Mar 02 '12 at 05:28

V4Vendetta

37,194
9
78
82

I somehow got it to work magically. Check my original post for what I did. No idea why it works – athom Mar 02 '12 at 05:45
1

@TheColonel i guess the resultant string doesn't have the millisecond part, did you try with `Now()` function i think that suits you more. – V4Vendetta Mar 02 '12 at 05:49
Does the Now() function only work in the sql? Several things later on will be connected to this object with the same DateTime – athom Mar 02 '12 at 05:57
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. – SouthShoreAK Nov 19 '14 at 22:26

score 4 · Answer 2 · answered Jun 04 '13 at 11:15

4

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;

answered Jun 04 '13 at 11:15

fiLLLipnet

111
1
3

1

See https://support.microsoft.com/en-us/help/320435/info-oledbtype-enumeration-vs-microsoft-access-data-types. – Mike Lowery Mar 13 '19 at 23:08
Mike Lowery's link takes to you Microsoft's OleDbType enumeration vs. MSAccess data types. An incredibly useful resource! – batpox Sep 01 '19 at 17:44

score -1 · Answer 3 · edited Jul 05 '16 at 12:22

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

Trouble inserting DateTime into Access with OleDb

3 Answers3

Linked