故障插入日期时间与OleDb的访问 [英] Trouble inserting DateTime into Access with OleDb

问题描述

试图插入一行数据到Access时，我得到错误条件表达式中数据类型不匹配。 。周围有点搞乱后，我把范围缩小到datetime作为问题

下面是我的代码：

 类ABGDA 
 {
私人OleDbConnection的dbConn; 
私人的OleDbCommand dbCmd; 
私人OleDbDataReader dbReader; 
私人字符串sConnection; 
私人SQL字符串; 
私人ABG ABG; 
 
公共无效insertProgressNotes（ABG ABG）
 {
 = ABG ABG; 
 
 sConnection =供应商= Microsoft.ACE.OLEDB.12.0; + 
数据源= SimEMR.accdb; 
 dbConn =新的OleDbConnection（sConnection）; 
 dbConn.Open（）; 
 
 SQL =INSERT INTO ABG（AccountNo，LabDate，PAO2，PACO2，SAO2，碳酸氢盐，BaseExcess，+ 
O2Setting，SetRate，SetPEEP，氧浓度）VALUES（？，？， ？，，，，，，，，）;？？？？？？？？ 
 
 dbCmd =新的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（小苏打，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（氧浓度，OleDbType.Double）.value的= abg.FiO2; 
 
 dbCmd.ExecuteNonQuery（）; 
 dbConn.Close（）; 
} 
} 
  

abg.LabDate使用DateTime.Now <获得/ p>

奇怪的是，我用DBTIMESTAMP另一类INSERT语句，比似乎工作得很好。 ？有没有人有什么我的问题可能是一个想法

更​​新：看来我找到了一个解决方案，我不知道为什么它的工作。我改变abg.LabDate为字符串，并保存当前的日期/时间。

  abg.LabDate = DateTime.Now.ToString（ ）; 
  

然后当我去把它插入到数据库中，我分析这回DateTime和奏效...

  dbCmd.Parameters.Add（LabDate，OleDbType.DBTimeStamp）.value的= DateTime.Parse（ABG。 LabDate）; 
  

解决方案

我认为错误是由于毫秒部分现在你的的DateTime 将不被访问来处理这样无论是你可以截断毫秒部分，并尝试插入或在情况下，它仅 DateTime.Now 然后用等值 NOW（）的接入功能。

 插入表1（datecolumn）值（NOW（））//日期（）如果在部分时间
  

不感兴趣

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

解决方案

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

这篇关于故障插入日期时间与OleDb的访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！