Question
I'm trying to do an insert with Dapper into my MS Access database, now querying the data works fine but when I do try an insert I get an System.Data.OleDb.OleDbException : Data type mismatch in criteria expression
The code is :
let conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=path;Persist Security Info=False;")
let exec f =
try
conn.Open()
do f()
finally
conn.Close()
[<CLIMutable>]
type Timecard =
{ Id : int
Employee : string
WorkDate : DateTime
Hours : int
Description : string
BillType : string }
exec (fun () ->
let tc = { Id = 0
Employee = "John Doe"
WorkDate = DateTime.Today
Hours = 10
Description = "working 9 to 5"
BillType = "BillableInternal" }
conn.Execute("insert into Timecards (Employee, WorkDate, Hours, Description, BillType) values (@Employee, @WorkDate, @Hours, @Description, @BillType)", tc)
|> ignore)
Accepted Answer
After some digging, I was able to find a cause:
Below is CreateParamInfoGenerator delegate from dapper's SqlMapper:
public static Action<IDbCommand, object> CreateParamInfoGenerator(Identity identity)
{
// code above here
IEnumerable<PropertyInfo> props = type.GetProperties().OrderBy(p => p.Name);
The props is your unanimous param which gets re-ordered by OrderBy(p => p.Name), which moves city upfront.
new { firstName = "John", city = "SomeCity", lastName = "Smith" }
Props is then being added to the IDbCommand Parameters where the order is important.
If I comment out OrderBy() clause, then everything works.
I also tested DynamicParameters and intentionally re-ordered the attributes to move city upfront:
var parameters = new DynamicParameters();
parameters.Add("city", "SomeCity");
parameters.Add("firstName", "John");
parameters.Add("lastName", "Smith");
var result = dbConnection.Query<string>(
"update Students set FirstName = @firstName, City = @city where LastName = @lastName",
parameters
);
The above did not work as well, so the order of attributes is the reason!
I guess you can modify your local copy of SqlMapper for now and remove OrderBy() and wait for an official verdict from Marc...
Hope this helps.
Popular Answer
I had a similar issue, what I did was to use parameter names like @param1, @param2 instead of @name,@id,@price so the order stays the same without having to modify SQLMapper.cs file.
Something like
public void Update(Movie movie)
{
var sql = "UPDATE myDB.movies set title=@param1, genre=@param2 where ID=@param3";
db.Execute(sql, new { param1 = movie.Title, param2 = movie.Genre, param3 = movie.ID });
}