"Data type mismatch in criteria expression" when saving record to Access from C#

Question

I am getting a "Data type mismatch in criteria expression" error when inserting a new record into an access database. The application runs fine on UK computers, but on South African computers it throws this error. This makes me think it's something to do with the date format. However if I change my own regional settings to South African, I cannot reproduce the error.

The code is as follows:

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "INSERT INTO tblOrders "
                        + "( UserID, AccountNumber, EmailAddress, InvoiceAddressID, DeliveryAddressID, PurchaseOrderReference, Comments, TotalPrice, [Date] )"
                        + "VALUES (?,?,?,?,?,?,?,?,?);";
        cmd.Parameters.Add(new OleDbParameter("@UserID", OleDbType.Integer)).Value = userID;
        cmd.Parameters.Add(new OleDbParameter("@AccountNumber", OleDbType.VarChar)).Value = accountNumber;
        cmd.Parameters.Add(new OleDbParameter("@EmailAddress", OleDbType.VarChar)).Value = emailAddress;
        cmd.Parameters.Add(new OleDbParameter("@InvoiceAddressID", OleDbType.Integer)).Value = invoiceAddressID;
        cmd.Parameters.Add(new OleDbParameter("@DeliveryAddressID", OleDbType.Integer)).Value = deliveryAddressID;
        cmd.Parameters.Add(new OleDbParameter("@PurchaseOrderReference", OleDbType.VarChar)).Value = purchaseOrderReference;
        cmd.Parameters.Add(new OleDbParameter("@Comments", OleDbType.VarChar)).Value = comments;
        cmd.Parameters.Add(new OleDbParameter("@TotalPrice", OleDbType.Decimal)).Value = totalPrice;
        cmd.Parameters.Add(new OleDbParameter("@Date", OleDbType.Date)).Value = date;
        cmd.Parameters.Add(new OleDbParameter("@ID",OleDbType.Integer)).Value = orderID;

        ExecuteNonQuery(cmd); // this line errors

There are many similar questions on Stack, but they all seem to be building SQL strings manually or it otherwise seems to be a different cause. I have double checked the parameter order is the same as that in the InsertOrder query (and the code works for 99.9% of users anyway).

UPDATE 8/8/2014

It actually seems to be the Price parameter which is causing the problem - not the date. If I hardcode the price to 0 then it works fine. However on both UK and South African computers, totalPrice.ToString() produces "350.6" now that I've forced the app into en-GB in web.config. So it must be the case that on South African PCs, Access is still tripping up on the decimal value. How can I make the same app work on both UK and South African PCs? I don't understand how it can misinterpret a decimal value when using parameters.

Price is a "Currency" data type in the Access database.

Knickerbocker

Does your date contain also time information to a milliseconds precision? Access doesn't like milliseconds in datetime fields. – Vasyuta

Look at this question #16217964 – Vasyuta

Answer 1

Did you try to use OleDbType.Currency instead of OleDbType.Decimal? Maybe these types are intepreted differently although both map to Decimal type according to documentation. And as you said that is also the type that is used for your Access database.

Wellpreserved

+ 1 for being faster :) – Emirate

Yep! That was it. I figured it out about 2 mins before you posted and came back here to post it as my own answer - but you beat me to it. – Knickerbocker

I'll award the bounty as soon as it lets me (24 hrs?) – Knickerbocker

Answer 2

This sounds more like a criteria, so liekly it is not your SP that is at fault. Try this:

Verify that the criteria for a column matches the data type of the column's underlying field. If you specify a text criterion for a numeric or date/time field, you will get this error. For example, the field ReorderLevel has a Number data type. Therefore, if you type the criteria "50", an error message will be returned because Microsoft Office Access 2003 interprets values in quotation marks as text, not numbers. Other situations that cause a data type conflict include:

You're specifying criteria for a Lookup field, and the criteria use the values that display in the lookup list instead of their associated foreign key values. Because the foreign key values are the values actually stored in the underlying table, you should use them when you specify criteria for a field. You typed the dollar sign ($) in criteria you specified for a Currency field. Remove the dollar sign, and then view the results. Verify that the data type of each pair of joined fields in the query is the same. If not, change the data type of one of the joined fields to match the data type of the other.

Erelong

Well the program has worked perfectly for many years. It's only got this problem since being deployed in South Africa. None of my fields use currency data types - only plain decimal values. – Knickerbocker