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