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

Question

I get a type mismatch in the expression of the criteria when inserting a new record into the access database. The application works fine on UK computers, but this error occurs on South African computers. It makes me think that this is due to the date format. However, if I change my Regional settings in southern Africa, I can not 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 a lot of similar questions in Stack, but they all seem to be building SQL strings manually, or this seems to be another reason. I double-checked the order of the parameters in the same way as in the InsertOrder request (and the code works for 99.9% of users).

UPDATE 8/8/2014

In fact, it looks like the Price parameter, which causes the problem, not the date. If I rigidly tie the price to 0, then it works fine. However, on UK and South African computers, totalPrice.ToString () now produces “350.6” when I forcefully connected the application to en-GB in web.config. So it should be that, on South African PCs, access still works at a decimal value. How can I do the same application work on both British and South African PCs? I do not understand how this can misinterpret the decimal value when using parameters.

Price is the Currency data type in the Access database.

Answer 1

Have you tried using OleDbType.Currency instead of OleDbType.Decimal ? It is possible that these types are intepreted in different ways, although both are of the decimal type according to the documentation . And, as you said, this is also the type that is used for your Access database.

Answer 2

This is more like a criterion, so most likely this is not your joint venture, which is to blame. Try the following:

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

You specify the criteria for the Lookup field, and the criteria use the values ​​displayed in the search list, and not their corresponding foreign key values. Because foreign key values ​​are values ​​actually stored in the base table, you should use them when specifying criteria for a field. You entered a dollar sign ($) according to the criteria specified for the Currency field. Remove the dollar sign and then view the results. Verify that the data type of each pair of connected fields in the query is the same. If not, change the data type of one of the combined fields to match the data type of the other.