C# Access OleDb Data type mismatch in criteria expression

Question

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...

enter image description here

*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

enter image description here

AND (in case you're wondering)

enter image description here

From my Access file (Design View):

enter image description here

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

what is `record` in your case.. can you show that structure.. also you need to check for or Cast record.DateTime to DBNULL if it's null can you show more code declaration.. are you familiar with ` null-coalescing operator ??` so `insertCommand.Parameters.AddWithValue("@Date", record.Date ?? DBNull.Value);` perhaps would work — MethodMan, Sep 09 '14 at 18:33
I am wondering why your are assigning Nullable types? either way I have posted a working solution that should work for you in the future when you are declaring as property as Nullable `?` use the ` null-coalescing operator` — MethodMan, Sep 09 '14 at 18:50
`record.getDBType()` this is not needed because it will not return the value at all in fact I am actually thinking that it would return System.String or error can you let us know if the changes worked for you..? — MethodMan, Sep 09 '14 at 18:54
Thank you,@DJKRAZE. I'm actually new to C# and the ?? operator is something new to me! But it doesn't work on (DateTime)record.Date because it is not a nullable type (i.e. already casted to DateTime). Still, I'm not sure if record.Date is the problem, and record.Date here will never be null because the DateTime value is retrieved from a DateTimePicker. — MooMooCoding, Sep 09 '14 at 19:02
Nullabe DateTime must have a value.. so I ask again where in code are you assigning record.Date..? if I declare a variable and assign it the following `public static DateTime? Date { get; set; }` tell me what the value of `Date will be when I run past the line of code..? you are not understanding how to initialize and assign nullable variables you need to assign record.Date — MethodMan, Sep 09 '14 at 19:10
On this particular form where the above INSERT operation is used, `record.Date = dtpDate.Value;`. `record.Date` is set as `DateTime?` because the nullable type is needed somewhere else, but here, since a `Record` is set with its date set using a _DateTimePicker_'s value, it should never be null. — MooMooCoding, Sep 09 '14 at 19:21
Ok so I ask you when you are debugging it does it set the value correctly to record.Date..? and if so then there is no need to cast it as DateTime when the database should resolve the datatype — MethodMan, Sep 09 '14 at 19:28
also your getDBType should be called within the method not withing the AddParameters that's kind of weird and wouldn't hurt to move it outside don't you think.. — MethodMan, Sep 09 '14 at 19:30
Yes, `record.Date` is correctly set here. I removed the casting but it still doesn't work. Maybe it has nothing to do with `record.Date`? — MooMooCoding, Sep 09 '14 at 19:32
also where is the `OleDbCommand` Command type defined try adding this after you declare inserCommand `insertCommand.CommandType = CommandType.Text;` — MethodMan, Sep 09 '14 at 19:41
What does ex display.. can you show why are you not displaying ex.Message ? — MethodMan, Sep 09 '14 at 20:09
The ex.Message is exactly as I posted above: 'Data type mismatch in criteria expression'. But I think I found the problem now (which I'm about to post up there) :D — MooMooCoding, Sep 09 '14 at 20:14
you have several problems but I think that what I have put for you in my answer should help get you to resolving any other issues.. also make sure the connection is still open before executing this line as well `OleDbCommand selectCommand = new OleDbCommand(selectStatement, connection);` — MethodMan, Sep 09 '14 at 20:16

score 14 · Accepted Answer · edited May 23 '17 at 12:33

14

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!

edited May 23 '17 at 12:33

Community

1
1

answered Sep 09 '14 at 20:18

MooMooCoding

319
1
5
15

Yes, "AddWithValue" got me good for a few hours... I wouldn't have looked at that AddWithValue was choosing different type hadn't I seen this post. – dance2die Aug 14 '15 at 16:14
1

Problem solved, yes. Also, you note the fundamentals of the problem. But you didn't write what you actually did to solve the problem. – James John McGuire 'Jahmic' Nov 18 '17 at 07:19

MethodMan · Answer 2 · 2014-09-09T19:41:54.670

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..

score 0 · Answer 3 · answered Feb 20 '15 at 14:00

0

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

answered Feb 20 '15 at 14:00

Zhihui Yan

11
3

C# Access OleDb Data type mismatch in criteria expression

3 Answers3

Linked