OleDbException on writing to MS Access using Dapper

dapper f# ms-access oledb

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



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why