Data type mismatch in criteria expression when updating a typed dataset table's row

I have a typed dataset with one sample data table with an Integer ID field and a decimal Price field:

The source database is an MS Access mdb file. When I'm updating Price field I'm getting "Data type mismatch in criteria expression" error message in the case when MS Windows system wide decimal symbol is set to comma:

In the case when MS Windows system wide decimal symbol is set to dot, my test works well:

Question: What would be the minimal correction of my sample code:

TestTableAdapter adapter = new TestTableAdapter();
ConsoleApplication2.SampleDataSet.TestDataTable table = adapter.GetData();
table[0].Price = 1.23m;
adapter.Update(table[0]); 

to make it working well independent of the system wide decimal symbol setting value ?

Answers


The following "trick" of using "fake" parameters seems to be solving the subject issue. Every "fake" parameter has to be supplied with a custom "converter" code line - below is just a solution for the sample case not a generic solution. It can be generalized but I'd still expect to find more simple and generic solution:

var adapter = new TestTableAdapter();
adapter.Adapter.RowUpdating += 
   new System.Data.OleDb.OleDbRowUpdatingEventHandler((sender, e) =>
    {
      // UPDATE [Test] SET [Price] = {{Price}} WHERE [ID] = @id
      // {{Price}} is a fake parameter to be replaced by custom code
      e.Command.CommandText = e.Command.CommandText.Replace("{{Price}}", 
                 string.Format(new System.Globalization.CultureInfo("en-US"),
                 "{0:#0.00}", 
                 e.Row["Price"])); 
     }
   );
ConsoleApplication2.SampleDataSet.TestDataTable table = adapter.GetData();
table[0].Price = 1.23m;
adapter.Update(table[0]);

Here is a generic solution:

var adapter = new TestTableAdapter();
adapter.Adapter.RowUpdating += new System.Data.OleDb.OleDbRowUpdatingEventHandler(
   (sender, e) =>
   {
     foreach (System.Data.OleDb.OleDbParameter parameter in e.Command.Parameters)
          if (parameter.DbType == System.Data.DbType.Decimal)
              parameter.OleDbType = System.Data.OleDb.OleDbType.Currency;
   }
);
ConsoleApplication2.SampleDataSet.TestDataTable table = adapter.GetData();
table[0].Price = 3.75m;
adapter.Update(table[0]);

This solution is a result of comments to this topic provided by Gord Thompson and some tests I have made here.


Need Your Help

Operator Overloading in C++ (small error)

c++ compiler-errors operator-overloading operators

I have a small error with operator overloading that's been bugging me.

What is the right Syntax for SetOutPath in NSIS script?

syntax scripting syntax-error nsis

i want to use SetOutPath, but I'm have problem with the Syntax,