Dapper & MS Access - Read works, Write doesn't

Question

Let's start by getting this out of the way: I'm stuck using an MS Access DB and I can't change it.

This works fine:

using (OleDbConnection conn = ConnectionHelper.GetConnection())
{
  conn.Open();
  var results = conn.Query<string>(
    "select FirstName from Students where LastName = @lastName", 
    new { lastName= "Smith" }
  );
  conn.Close();
}

This works fine:

using (OleDbConnection conn = ConnectionHelper.GetConnection())
{
  OleDbCommand cmd = new OleDbCommand(
    "update Students set FirstName = @firstName, City = @city where LastName = @lastName", 
    conn
  );
  cmd.Parameters.AddWithValue("firstName", "John");
  cmd.Parameters.AddWithValue("city", "SomeCity");
  cmd.Parameters.AddWithValue("lastName", "Smith");

  conn.Open();
  var result = cmd.ExecuteNonQuery();
  conn.Close();
}

This doesn't... it executes without error but it sets the FirstName as "SomeCity" in the DB and the City as "John":

using (OleDbConnection conn = ConnectionHelper.GetConnection())
{
  conn.Open();
  var results = conn.Query<string>(
    "update Students set FirstName = @firstName, City = @city where LastName = @lastName", 
    new { firstName = "John", city = "SomeCity", lastName = "Smith" }
  );
  conn.Close();
}

Any ideas?

EDIT BELOW

Dapper works if I use DynamicParameters:

using (OleDbConnection conn = ConnectionHelper.GetConnection())
{
  DynamicParameters parameters = new DynamicParameters();
  parameters.Add("firstName", "John");
  parameters.Add("city", "SomeCity");
  parameters.Add("lastName", "Smith");

  conn.Open();
  var result = conn.Query<string>(
    "update Students set FirstName = @firstName, City = @city where LastName = @lastName",
    parameters
  );
  conn.Close();
}

Note: Just tried to reproduce with sql express 2008 and it works fine... — Void Ray, Sep 07 '12 at 18:35
I've done this successfully with Sql Server... so I think this is with MS Access only — Trev, Sep 07 '12 at 21:21
@MarcGravell See the accepted answer below, and my comment... do you know if removing the `.OrderBy(p => p.Name)` code will affect other parts of Dapper? — Trev, Sep 08 '12 at 15:15
According to a comment to the answer [here](http://stackoverflow.com/a/19481354/2144390), this is no longer an issue with the current version of Dapper. — Gord Thompson, Jan 29 '14 at 09:08
@GordThompson the issue is not fixed still. We need to preserve the order still. Please refer this question http://stackoverflow.com/questions/39339478/dapper-with-access-update-statement-partially-not-working — RobinAtTech, Sep 07 '16 at 00:07
@RobinAtTech - Thanks for the update. I seem to recall testing a subsequent version of *Dapper* itself and confirming that it no longer sorted the parameters by name, but it was quite a while ago and I can no longer be certain of that. I wonder if Dapper-*Extensions* may still be sorting the parameters by name, perhaps because it is still tied to an older version of Dapper. (Dapper-Extensions on GitHub has not been updated in over two years.) — Gord Thompson, Sep 07 '16 at 00:45

Void Ray · Accepted Answer · 2012-09-08T01:28:42.487

9

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.

edited Sep 08 '12 at 01:28

answered Sep 08 '12 at 01:08

Void Ray

9,849
4
33
53

1

It turns out MS Access is [fussy about the order of the parameters](http://stackoverflow.com/a/1476818/47121). I tried removing the `.OrderBy(p => p.Name)` code from SqlMapper and it works now. I'd also like to know from Marc or Sam whether removing this code would have any implications elsewhere. – Trev Sep 08 '12 at 15:13
I did not know that as well; good to know! And I'm also curious about the purpose of the OrderBy() clause. – Void Ray Sep 08 '12 at 19:00
According to a comment to the answer [here](http://stackoverflow.com/a/19481354/2144390), this is no longer an issue with the current version of Dapper. – Gord Thompson Jan 29 '14 at 09:06
1

Having this issue still on 1.42 from NuGet. – Jane Panda Dec 07 '15 at 21:26
1

@Bob Having this issue still in 1.50.2 – RobinAtTech Sep 07 '16 at 00:10

score 4 · Answer 2 · answered Jan 29 '14 at 07:03

4

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

answered Jan 29 '14 at 07:03

Zo Has

12,599
22
87
149

1

Things may have changed since you last worked with Dapper. According to a comment to the answer [here](http://stackoverflow.com/a/19481354/2144390), this is no longer an issue with the current version. – Gord Thompson Jan 29 '14 at 09:08
1

@GordThompson Thanks for the link. I am having the same problem with version 1.13 via NuGet. – Zo Has Jan 29 '14 at 09:20
I am having same issue in 1.50.2 – RobinAtTech Sep 07 '16 at 00:11

Dapper & MS Access - Read works, Write doesn't

2 Answers2

Linked