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

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

Udale

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 – Deflation

Sorry for the confusion,@DJKRAZE. Edited! – Udale

you are assigning the DataTime? as Nullable – Deflation

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` – Deflation

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..? – Deflation

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

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 – Deflation

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

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 – Deflation

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

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? – Udale

also where is the OleDbCommand Command type defined try adding this after you declare inserCommand insertCommand.CommandType = CommandType.Text; – Deflation

What does ex display.. can you show why are you not displaying ex.Message ? – Deflation

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 – Udale

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

Answer 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!

Udale

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

Problem solved, yes. Also, you note the fundamentals of the problem. But you didn't write what you actually did to solve the problem. – Nifty

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

Deflation

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

Multipartite