none
"Datatype mismatch in criteria" when updating table with 'decimal' fields RRS feed

  • Question

  • Hello together,

    when inserting records into an Access 2003 Database containing fields of type "decimal" (same data type in the data set and the database) I - sometimes - get the "Datatype mismatch in criteria" error message. Did a small demo project where I could reproduce the error: It only occures if inserting records where the value is 0.0m.

    This is the code I use:

    DatabaseDataSet dataSet = new DatabaseDataSet();
    MyTableTableAdapter adapter = new MyTableTableAdapter();
    
    // The following works  
    adapter.Fill(dataSet.MyTable);  
    dataSet.MyTable.AddMyTableRow(1.3434m);
    adapter.Update(dataSet.MyTable);
    
    // The following throws the error on update
    adapter.Fill(dataSet.MyTable);
    dataSet.MyTable.AddMyTableRow(0.0m);
    adapter.Update(dataSet.MyTable);
    
    

    The database contains one table "MyTable" with one auto increment column "ID" of type int and another column "MyDecimalValue" of type decimal with precision 18.4.

    The data type of the "MyDecimalValue" field in the data set is set to DataType="System.Decimal".

    Why does it throw the error when trying to insert records with decimal values 0.0m?

    Friday, October 22, 2010 3:46 PM

All replies

  • Hello,

    As far as I know, Decimal structure has its own zero representation. I recommend you use decimal.Zero instead of 0.0m.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    • Marked as answer by liurong luo Thursday, October 28, 2010 9:51 AM
    • Unmarked as answer by metbone Sunday, October 31, 2010 11:10 AM
    Tuesday, October 26, 2010 7:53 AM
  • Hello Roahn Luo,

    thank you for your Reply. It works if

    dataSet.MyTable.AddMyTableRow(0.0m);

    is replaced with

    dataSet.MyTable.AddMyTableRow(decimal.Zero);

    But the error message still occurs very often and so I played a little with the values:

    I found out that the value explicitly has to have 4 decimals after the comma (or as many as specified in the database schema) or the value is misinterpreted and the error is thrown. Also if the value is < 1.0m and >=0.0m it is impossible to store it in the database.

    Example:

    If the numeric database field is configured to have 4 decimal places after the comma:

    1.2m and 1.20000m => error message

    1.2000m => works

    1.23456m => error message

    1.2345 => works

    0.0000m and 0.1000m => error message (we have 4 decimals after the comma here, but does not work. I assume because of the leading zero.)

    decimal.Zero => works

    .NET or the data provider should handle this for the programmer in my opinion (e.g. check the database schema and cut off or add decimals). So for me this is a bug and as a workaround I will try to overwrite and extend the set methods of my properties to ensure to have the correct number of decimals. What I can do for values < 1.0m I do not know yet.

    Sunday, October 31, 2010 11:09 AM