Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
INTRODUCTION AND RELEVANT INFORMATION:

I have an MS Access 2007 database with a table that has string field and numerical one. The numerical field is of type double and can be negative or positive.

I use ADO to connect to the database and load its data into edit controls with SetDialogItemText API-edit controls are in the dialog box.

User can edit the values of existing data ( when saving changes I get data from edit controls with GetDialogItemText API ).

PROBLEM:

Everything works fine in my program if the user has English or US locale, but if the user selects European locale then my ADO string is incorrect and reports error.

This is expected since in the English version it has 2 parameters-string and a double but since Europeans use comma as a decimal point ( for example US 123.456 is 123,456 ) ADO interprets it as having 3 parameters instead of two-for example:
SQL
INSERT INTO table VALUES ( 'some string', 12.5 ); -- US locale

SQL
INSERT INTO table VALUES ( 'some string', 12,5 ); -- ERROR! 3 parameters instead 2

To explain more thoroughly:

Let us say that we have loaded data from database into edit controls using European locale:

First one holds string ( Some string ) and second one decimal number ( 123,456 ).

Let us say user changes the string value into Some other string and presses the Save button.

Now my code gets values from edit controls but the error must occur since I got 123,456 from the second edit control ( remember, user left decimal data unchanged ), and my ADO string is not properly configured since now it looks like this:
SQL
INSERT INTO table VALUES ( 'Some other string', 123,456 ); --ERROR! 3 parameters

MY QUESTION:

If the user has set the European locale, is there a way to load decimal values from MS Access 2007 database into edit control with comma changed into dot?

Or maybe I can somehow subclass edit control to change comma into dot?

User input can be limited to dot and letters by sublclassing the edit control so that is not the problem. I just want to load data from database properly, so my ADO string can be executed without error if user decides not to edit decimal data like in the example I described above.

Thank you.

Best regards.
Posted
Updated 27-Jan-14 15:23pm
v2

1 solution

Easy. You need to convert the text to double and then insert it in the database. You have not specify what you are using for data insert, so I assume you build the INSERT query dynamically, like this (assuming you are using MFC):
C++
CString text1, text2, sql;
GetDialogItemText(IDC_EDIT1, text1);
GetDialogItemText(IDC_EDIT2, text2);
sql.Format(_T("INSERT INTO table VALUES ( '%s', %s );"), (LPCTSTR)text1, (LPCTSTR)text2);

So, instead of doing this, use the following:
C++
CString text1, text2, sql;
GetDialogItemText(IDC_EDIT1, text1);
GetDialogItemText(IDC_EDIT2, text2);
sql.Format(_T("INSERT INTO table VALUES ( '%s', %f );"), (LPCTSTR)text1, _ttof_l(text2, _get_current_locale()));

The important thing to remember is to use "_l" versions of atof/wtof functions

Edit: As suggested by the author the best way to fix these type of problems it is to load current regional settings with _wsetlocale( LC_ALL, L"" ); and to use ADO parametrized query for everything to work.
 
Share this answer
 
v2
Comments
AlwaysLearningNewStuff 27-Jan-14 22:38pm    
I use pure Win32 API to obtain data from edit controls. It is similar to what you have described in your answer ( MFC is a thin wrapper for Win32 ).

I use ADO for database communication and the insert query is formatted similarly to your example.

I was reading about versions of atof with "l" sufix but on MSDN they had no example for using them.

I will try to implement your suggestion, just tell me if I need #include"locale.h" for using _get_current_locale() since I see that function for the first time.

Thank you for answering.

Best regards.
chaau 27-Jan-14 22:40pm    
MSDN http://msdn.microsoft.com/en-us/library/3szh5e2z.aspx has it closer to the bottom: Required header: "locale.h" (can't insert here less and more symbols)
chaau 27-Jan-14 22:50pm    
MSDN http://msdn.microsoft.com/en-us/library/3szh5e2z.aspx has it closer to the bottom: Required header: <locale.h>
AlwaysLearningNewStuff 31-Jan-14 17:14pm    
I had to load current regional settings with _wsetlocale( LC_ALL, L"" ); and to use ADO parametrized query for everything to work.

Please edit your answer by including the bold parts of my comment so future generations can benefit from your answer. I have accepted your answer and gave you 5 stars. Thank you for your help! Best regards.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900