Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
628 views
in Technique[技术] by (71.8m points)

c# - Trouble inserting DateTime into Access with OleDb

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);
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

57.0k users

...