"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.

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.