data mismatch in criteria expression using Oledb, C#, Dapper

Question

I am simply inserting data using [c#, DAPPER, MS Access] inside a table but the column which is [DateTime] in c# is mapped to [date/time] column in MsAccess cause a issue like "data mismatch in criteria expression".

After a long research I got a link, but the "Floor" solution doesn't work here. Any suggestions are most welcome.

score 2 · Answer 1 · answered Oct 14 '18 at 14:01

2

The best solution I found is changing the parameter syntax inside the query string from @Foo to ?Foo?.

The reason for this is explained in https://github.com/StackExchange/Dapper/issues/911

answered Oct 14 '18 at 14:01

Eduardo Hernández

393
4
16

score 0 · Answer 2 · edited Sep 28 '16 at 04:16

0

Try something like this which works for me:

@CLStart = DateTime.Parse(Convert.ToDateTime(client.CLStart).ToShortDateString())

client.CLStart here is of data type DateTime? - a Nullable<DateTime>

edited Sep 28 '16 at 04:16

BlackICE

8,816
3
53
91

answered Sep 28 '16 at 03:22

Nandy

1
2

Thanks, I will try this and let you know. – Ankur Soni Oct 05 '16 at 06:08

score 0 · Answer 3 · answered Sep 30 '17 at 02:15

I've had this issue before when handwriting SQL queries in combination with Dapper and MS Access when the query has multiple parameters. The problem is that the access engine doesn't respect parameter order and sorts them alphabetically.

Take for example this sample table named MyTable:

MyNumber   Number
MyDate     Date/Time

Assuming you had a C# class MyClass:

public class MyClass
{
    public int MyNumber { get; set; }
    public DateTime MyDate { get; set; }
}

And had an instance myClass that you passed into the following Dapper statement:

connection.Execute("INSERT INTO MyTable (MyNumber, MyDate) VALUES (@MyNumber, @MyDate)", myClass);

The statement would fail due to System.Data.OleDb.OleDbException: 'Data type mismatch in criteria expression.'

This is because Access will sort the parameters alphabetically, which in this case causes it to try to stick a number into a date and a date into a number.

You can workaround this by naming your parameters in an alphabetically-friendly manner, or ordering them alphabetically. In this case the above statement could be rewritten as:

connection.Execute("INSERT INTO MyTable (MyDate, MyNumber) VALUES (@MyDate, @MyNumber)", myClass);

After some trails, I found that it is not about access engine. It is more like the Dapper mapping your `MyClass` alphabetically to generate a DbCommand. You can try to use an anonymous object like `new {MyNumber=1,MyDate=Today}` to match the first statement, which overwriten the default alphabet order. No error will apear. It looks like Dapper's mapping reflection is somehow related to the properties' order of your entity. And try the hand coded way using `OleDbCommand`. The order depends on `cmd.Parameters.Add`. — joe, Dec 26 '17 at 05:50

score 0 · Answer 4 · answered Apr 25 '19 at 08:05

I had the same problem in vb.net, and I solved it this way.

 Public Shared Function dapperBugDate(ByVal birthday As Date)
        birthday = birthday.ToString("yyyy-MM-dd HH:mm:ss")
    Return birthday
End Function

Sub save()
    Dim actions = db.Execute("INSERT into table (birthday) VALUE (@birthday)", New table With {.birthday = dapperBugDate(Me.birthday)})
End Sub

score 0 · Answer 5 · answered Nov 26 '20 at 12:32

Converting the DateTime to string worked for me. For example,

using (var conn = new OleDbConnection(connectionString))
{
    conn.Open();
             
    string sql = "Insert into TableName (TableId, DateChanged) values (@TableId, @DateChanged);";
    var effectedRowCount = await conn.ExecuteAsync(sql, new { TableId = 1, DateChanged = DateTime.Now.ToString()});

    conn.Close()
}

data mismatch in criteria expression using Oledb, C#, Dapper

5 Answers5