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 ).
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:
INSERT INTO table VALUES ( 'some string', 12.5 );
---> US locale
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:
INSERT INTO table VALUES ( 'Some other string', 123,456 );
-->ERROR! 3 parameters
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.
There are two issues at play here. I will illustrate using two locales:
English (Canada) -- EN-CA: which uses .
as the decimal separator
French (Canada) -- FR-CA: which uses ,
as the decimal separator
and tiny bits of VBScript code (as I don't have C++).
The VBScript
x = 3/2
WScript.Echo x
displays 1.5
when the Windows locale is EN-CA, and it displays 1,5
when the Windows locale is FR-CA. That text is what you are seeing in your edit box.
Say the user leaves the string unchanged. Instead of trying to use the string representation of the decimal number you need to use a locale-aware method of converting the string back to an actual decimal number:
For EN-CA:
x = CDbl("1.5") + 1
WScript.Echo x
displays 2.5
indicating that the string "1.5" was successfully converted to a Double
before adding 1.
For FR-CA:
x = CDbl("1,5") + 1
WScript.Echo x
displays 2,5
indicating that the string "1,5" was successfully converted to a Double
before adding 1.
Summary: When you receive a text representation of a number from the user you need to convert (i.e., parse) that string to an actual numeric data type, and the parsing mechanism must be locale-aware.
The problem here is that Access SQL is not locale-aware; it only "speaks" US English. So if you try and "glue together" a SQL command under FR-CA
x = 3/2
sql = "INSERT INTO Table1 (DoubleField) VALUES (" & x & ")"
WScript.Echo sql
you get INSERT INTO Table1 (DoubleField) VALUES (1,5)
and that fails, as you have discovered.
The solution here is to NOT "glue together" SQL commands (which is a Bad Thing anyway, because of SQL Injection vulnerabilities and other nuisances). Instead, use a parameterized query:
Option Explicit
Dim con ' ADODB.Connection
Dim cmd ' ADODB.Command
Dim prm ' ADODB.Parameter
Dim x
Const adDouble = 5
Const adParamInput = 1
' test data:
' the following statement is valid when
' the Windows locale uses "," as the decimal separator
x = CDbl("1,5")
Set con = CreateObject("ADODB.Connection")
con.Open _
"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
"Dbq=C:\__tmp\main.accdb;"
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = con
cmd.CommandText = "INSERT INTO Table1 (DoubleField) VALUES (?)"
Set prm = cmd.CreateParameter("?", adDouble, adParamInput, , x)
cmd.Parameters.Append prm
cmd.Execute
Set prm = Nothing
Set cmd = Nothing
con.Close
Set con = Nothing