CODEWITHSUNDEEP
c#dapperoledbexception
Ankur Soni
Ankur Soni

Reputation: 6018

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

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.

Upvotes: 3

Views: 1277

Answers (5)

malik_aurko96
malik_aurko96

Reputation: 16

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

Upvotes: 0

abubacar mussagy
abubacar mussagy

Reputation: 1

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

Upvotes: 0

Eduardo Hernández
Eduardo Hernández

Reputation: 403

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

Upvotes: 2

Ondrej Balas
Ondrej Balas

Reputation: 544

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

Upvotes: 1

Nandy
Nandy

Reputation: 1

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>

Upvotes: 0

Related Questions