Which is locale-aware: OleDb.Currency or OleDb.Decimal?

Question

This is two-fold. I have an Access database and a table containing MS Access Currency fields. My customers in the USA use decimal values like 1.23 and my customers in Ecuador use decimal values like 1,23.

I have some ADO legacy code and I've tried creating ADODB Parameters with type adDecimal and also with type adCurrency. In either case, after my ADODB Command is executed the data in Access comes in as 1.23 (expected) for USA and 123.00 for Ecuador (not expected).

In my .NET code, I've tried using OleDb parameters with type OleDb.Currency and OleDb.Decimal. It seems like the OleDb.Currency is locale-aware but OleDb.Decimal is not.

My head is spinning. Does anybody know the correct ADO usage for international currency for my legacy code and also the correct way to write the .NET parameters as our codebase moves forward?

Thanks!

Answer 1

Ok, so after some additional digging, it appears that the "Currency" types are preferred over the "Decimal" types. In other words, using adCurrency for ADO or OleDb.Currency will work for decimal values being saved to MS Access Currency fields.

Just in case this comes up for anybody else - I did find a awfully strange behavior in the ADO case as follows:

My code instanced a new ADODB Command and set up the Parameters collection - including some typed as adCurrency. Then, I started updating several records in my database. I reused the same command object for each update and called a method to reset all the parameter fields to 0 before the next update block was started. This resulted in bad data in my fields (see the initial problem described above). I tried lots of stuff to fix this and was unsuccessful. I even commented out the call to my ResetParameters method just to make sure the behavior wasn't related.

Fix: Rather than reuse the command object, I finally decided to just new-up a brand-new command object prior to each update block. Suddenly, all of my decimal values appeared in Access in the proper way regardless of my locale settings.

In summary, it appears something was modified on my command object after calling the Execute method such that the adCurrency parameter on subsequent updates was broken. By the way, the results were as follows: a 4,32 value would appear in Access as 432 (this being the Ecuador currency formatting). My code wasn't ultra-complex and I won't post it here but there were two of us looking at this pretty hard and we just didn't see anything that could explain it. In fact, the idea of creating a new instance came about when we wrote a test app that only called a single update and the EXACT SAME CODE WORKED IN THE TEST AS EXPECTED.

Beware reuse of ADO command objects for multiple updates if you are writing a localized app.

Answer 2

The internal types are no locale-aware, they are just numbers.
The locale affects when you capture them from the input and display them to a window or report.

Answer 3

@Eduardo, I understand what you are saying about formatting. In this case I'm not displaying data to a UI or any output at all. I'm getting the unexpected behavior when I issue a database update completely from code.

I'm really not that familiar with what happens low-level with a provider like Jet (used for Access database). Does the command parameter get translated into an actual SQL string which is parsed later or are the parameters consumed directly somehow? I'm not sure.

What I am sure of is that the behavior I'm seeing indicates that the authors of the provider logic differentiate between "currency" and "decimal" types in such a way that the numeric parameters are slotted into the appropriate MS Access type. When we use the wrong parameter type, a conversion occurs internally and messes up the result.

A seemingly more common issue (and easier to find fixes for) are dealing with dates. Using Ecuador again as an example, the date format in that region is day/month/year instead of month/day/year in the USA. This a SQL string like this isn't going to work well:

UPDATE Customer SET DateOfBirth = #03/06/1970#

The SQL parser will always read this date as March 6th (go USA!) but in Ecudaor it actually means June 3rd. The fix for this is to always convert dates to a universally accepted format before adding them to the SQL string:

UPDATE Customer SET DateOfBirth = #1970-06-03# (June 3rd, 1970)

The 'gotcha' around currency was a little more subtle - and the fact that my ADO parameter wasn't behaving during the loop iteration (see accepted answer above) was way strange.