C# Access OleDb Data type mismatch in criteria exp

问题:

Would you please kindly check the following code for errors that give me a 'Data type mismatch in criteria expression' exception? I just can't seem to find the source of the problem...

*record.Date of nullable DateTime? type is explicitly casted to DateTime

*record.Date is set as nullable for other uses in the program. But the record.Date set for the INSERT operation is retrieved from a DateTimePicker, so a record.Date value for this method should never be null.

WHERE

AND (in case you're wondering)

---

From my Access file (Design View):

---

Thank you!

---

Here's the AddRecord method. Thanks!

public static int AddRecord(Record record)
{
    OleDbConnection connection = LABMeetingRecordsDB.GetConnection();
    string insertStatement = "INSERT INTO DocumentInfo " +
                             "([FileName], [Date], [Subject], [Type]) " +
                             "VALUES (?, ?, ?, ?)";
    try {
        OleDbCommand insertCommand = new OleDbCommand(insertStatement, connection);
        insertCommand.Parameters.AddWithValue("@FileName", record.FileName);
        insertCommand.Parameters.AddWithValue("@Date", (DateTime)record.Date);
        insertCommand.Parameters.AddWithValue("@Subject", record.Subject);
        insertCommand.Parameters.AddWithValue("@Type", record.getDBType());

        connection.Open();
        insertCommand.ExecuteNonQuery();

        string selectStatement = "SELECT IDENT_CURRENT('DocumentInfo') FROM DocumentInfo";
        OleDbCommand selectCommand = new OleDbCommand(selectStatement, connection);
        int recordID = Convert.ToInt32(selectCommand.ExecuteScalar());

        AddCategory(connection, recordID, record.Category);

        return recordID;

        } catch (OleDbException ex) {
            throw ex;
        } finally {
            connection.Close();
        }
    }

回答1:

So...[PROBLEM SOLVED] :D

From HERE I learnt that

The problem of the mismatch in criteria expression is due to the OleDbType assigned to the parameter used to represent the DateTime.Now value when you call AddWithValue.

The OleDbType choosen by AddWithValue is DBTimeStamp, but Access wants a OleDbType.Date.

Meaning that the convenient AddWithValue pulled a fast one on me...

Thank you @LarsTech and @DJKraze for helping me out despite the confusion of the presentation!

回答2:

OleDbConnection connection = LABMeetingRecordsDB.GetConnection();
string insertStatement = "INSERT INTO DocumentInfo " + "([FileName], [Date], [Subject], [Type]) " + "VALUES (?, ?, ?, ?)";
OleDbCommand insertCommand = new OleDbCommand(insertStatement, connection);
insertCommand.CommandType = CommandType.Text;
insertCommand.Parameters.AddWithValue("@FileName", record.FileName);
insertCommand.Parameters.AddWithValue("@Date", (DateTime)record.Date ?? (object)DBNull.Value);
insertCommand.Parameters.AddWithValue("@Subject", record.Subject);
insertCommand.Parameters.AddWithValue("@Type", record.getDBType ?? (object)DBNull.Value);
connection.Open();
try
{
  insertCommand.ExecuteNonQuery();
}
catch(OleDbException e)
{
   LogYourMessage(e.Message);
}

Casting the DBNull.Value as (object)DBNull.Value; is the correct way to handle the object try this I just checked on my end and did a similar test that works..

回答3:

I think you can also use a ToString() make your date data to the correct format which will be accepted by access

 insertCommand.Parameters.AddWithValue("@Date", record.Date.ToString("dd-MM-yy"));