3

I am using Dapper to Update and Insert Access DB. Code is working not throwing exception but its not updating the value in DB. Below is my code

sql.Append("UPDATE drugs_repository SET drug_name = @DrugName ");

sql.Append(" WHERE id = @DrugId");

var parameters = new
{
    DrugName = objDrug.DrugName,                           
    DrugId = objDrug.DrugId
};
var t = connection.Query<string>(sql.ToString(), parameters);

Can someone please let me know what exactly I am missing in the above code? When I hardcode the value than its updating in the DB. So probably its related to parameter.

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
techknackblogs
  • 119
  • 1
  • 14
  • 1
    Got the issue. In SqlMapper.cs file of Dapper I removed orderby from "//IEnumerable props = type.GetProperties().Where(p => p.GetIndexParameters().Length == 0).OrderBy(p => p.Name);". its working fine now. Not sure whether removing orderby will have any implications. Suggestions will be appreciated. – techknackblogs Oct 20 '13 at 16:36
  • 1
    +1 Well done! Because `'@DrugName' > '@DrugId'` the `.OrderBy` was swapping the order of the parameters, and Access.OLEDB requires that the parameters be created in the order in which they appear in the SQL CommandText. – Gord Thompson Oct 20 '13 at 18:40

1 Answers1

2

If you are nervous about possible side-effects from removing the .OrderBy() in the Dapper code then a workaround would be to name your parameters in such a way that they will sort in the same order that they appear in the SQL command. For example, I suspect that the unmodified Dapper code would probably work okay if the parameters were named @1DrugName and @2DrugId.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thanks for the idea. I was actually using the older version of Dapper. In latest version it's fixed. Anyways thanks for the reply. techknackblogs.com – techknackblogs Oct 24 '13 at 14:54
  • I think the issue still exist unless the order is kept http://stackoverflow.com/questions/39339478/dapper-with-access-update-statement-partially-not-working – RobinAtTech Sep 07 '16 at 00:01