Locale and OLEDB problem

[James Minns]

Hi all,
I wonder if you can help with the following problem - it seems that the
"Locale Identifier" parameter in my connection string is completely ignored;
I've written a program of a few lines which duplicates the problem.

db1.mdb contains 1 table with 2 fields:
DateAndTime (a date/Time field)
A_Number (a number of type "Single")

which contain one row: 21/10/1954 2.345
note the decimal point in the number


here is the program:

''''start program
Dim db_conn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=|DataDirectory|\db1.mdb;Locale Identifier=1040")
db_conn.Open()
Dim DS As New DataSet
Dim da As New OleDb.OleDbDataAdapter("SELECT STR(DateAndTime) as dt,
STR(A_Number) as num FROM Table1", db_conn)
da.Fill(DS, "MyTable")
For Each r As DataRow In DS.Tables("MyTable").Rows
Debug.WriteLine(String.Format("Date and time is {0}, Number is {1}",
r!dt, r!num))
Next
'''' end program

The conversion from date/time to string is done by the database engine
(jet), because of the STR conversion function in the SELECT statement:
However the program output is the same no matter what Locale Identifier I
specify in the connection string. the output is always:
Date and time is 21/10/1954, Number is 2.345
even though with Locale Identifier=1040 (Italian) I should see a comma as
decimal separator, instead of a point.

I know I can read the data without the STR conversion, and format it in VB
with the CurrentUICulture stuff, but as I said this reproduces a problem
from a larger project...

Thanks for any pointers, James

 

[Cor Ligthert [MVP]]

James,

A hundred times asked question wherefore is in fact only one answer and
which you only have to know. Use with datetimes in a SQL statement forever
parameters.

Here is a sample for that
http://www.vb-tips.com/default.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44af0137

I hope this helps,

Cor

 

[James Minns]

Hi Cor,
thanks for answering, but I don't have that problem;
my select statement asks for a Single precision number to be converted to a
*string*

SELECT CStr(DateAndTime) as dt, CStr(A_Number) as num FROM Table1

and the CStr is converting numbers according to the control panel settings
(decimal separator), not according to the Locale Identifier specified in the
database connection string.

James