MS Access double’s number format obstructs ADO query

Question

4.00/5 (1 vote)

See more:

Hello, respected professionals from CodeProject.

I have created dialog box with edit control and 2 buttons.

First button loads double( let us say, for example 25.45 ) from MS Access 2007 database into edit control.

Second one stores the value from edit control into the same field in database. It enables the user to edit the value of that field in the database ( for example, let us say that user entered 56.89 into edit box ).

I use ADO to execute following query:

wchar_t text[10]; // here I store the text from edit control

GetDlgItemText( ..., text, ... );

// create query

wchar_t query[50] = L”INSERT INTO MyTable ( MyField ) VALUES( _wtof( text) )”;

// execute it

Everything works fine on Windows XP, but on Windows 7 I get an error that I will describe bellow via an example:

On Windows XP:

I press first button . I get 25.45 ( pay attention to dot in 25.45 ! ) loaded in my edit control. Then I press second button. The value from edit control is successfully stored.

On Windows 7:

I press first button . I get 25,45( pay attention to comma in 25,45 ! ) loaded in my edit control. Then I press second button. The value from edit control is stored as 2545.

When I open the database on Windows XP, decimal value is stored as 25.45 ( again, pay attention to dot in 25.45 ! ).

When I open the database on Windows 7, decimal value is stored as 25,45 ( again, pay attention to comma in 25.45 ! ).

It seems that MS Access 2007 stores double number on Windows XP as xxx.yyy and on Windows 7 as xxx,yyy .

After going to Control Panel-> Regional and Language Options, I have seen that the settings for decimals on Windows 7 use comma for decimal numbers, and on Windows XP use dot.

My Question:

How can I change my fields definition, to avoid this behavior , so the format of the field uses dot like this : xxx.yyy ?

Is there something I can do with the C++ / Win32 / ADO code to fix this behavior ?

NOTE: I can NOT force the user to change Regional and Language Options, my code must adjust to this.

Thank you.

Regards.

Posted 29-Aug-13 9:25am

AlwaysLearningNewStuff

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

André Kraak · Accepted Answer · 2013-08-29T10:37:00

The edit controls and MS Access are working correctly.
They are using the decimal as defined in the Regional and Language Options as you have noticed.

The problem is the use of the _wtof[^] function.

The documentation states:

Quote:
The versions of these functions with the _l suffix are identical except that they use the locale parameter passed in instead of the current locale.

So the _wtof function uses the current locale. This is however not the one as set in the Regional and Language Options as you might expect.
In the documentation from setlocale, _wsetlocale[^] it states:

Quote:
At program startup, the equivalent of the following statement is executed:

C++
setlocale( LC_ALL, "C" );
A value of C specifies the minimal ANSI conforming environment for C translation. The C locale assumes that all char data types are 1 byte and that their value is always less than 256.

This means that the '.' is used as the decimal point and converting the text with the ',' fails.

To solve your problem you need to set the locale to the one used by windows. For this you can use the function _wsetlocale like:

C++

_wsetlocale( LC_ALL, L"" );

Alternatively you can use LC_NUMERIC to only effect the decimal point:

C++

_wsetlocale( LC_NUMERIC, L"" );

H.Brydon · Accepted Answer · 2013-08-29T18:24:00

Solution 2

I like Andre's solution (and I gave +5 for it too). An alternative thing you can do (and you probably need to do if you are dealing with multiple versions of the stored data) is get the string from the database, and replace all "," with "." in the string before converting to double.

Posted 29-Aug-13 18:24pm

H.Brydon

Comments

AlwaysLearningNewStuff 30-Aug-13 2:15am

I guess I could write the function to do that, and change the string before I build my query string... It seems as the easiest solution, and I would not touch locale...

5ed.