tiseffies
asked on
VB, SQL and German Regional Settings
I'm using vb5 and my regional settings are German. When look into my SQL table the format is correctly sown, with . as the thousand separator and coma as the decimal separator. Why does my recordset return the numbers in the english format? i.e 1,000.00
instead of 1.000,00.
This results in funny results when i add them up. Can anyone help?
instead of 1.000,00.
This results in funny results when i add them up. Can anyone help?
Check your ODBC data source. There is an option 'Use regional settings when outputting currency, numbers, dates and times' it's on 4th page of datasource configuration wizard.
ASKER
I followed your instructions, and got an error when accessing a field returned from a recordset. The field is of money data type. I set the language on the SQL Server to German, and my regional settings to German. It all should be compatible now..Any ideas would be appreciated
Here is the error:
Run-time error 0:
01S00 [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
Here is the error:
Run-time error 0:
01S00 [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
ASKER
P.S. Numeric data type works..don't know why money is a problem
ASKER
I realise that ADO fixes the problem...but that is a very big change for me to perform at the moment......
Say, is that error occuring exactly when you access that field?
Could you provide a fragment of code?
Could you provide a fragment of code?
ASKER
sure. i have also been looking into sql odbc driver patches but no luck yet...
i set the odbc connection
Set gcnMain = DBEngine.Workspaces("Main" ).OpenConn ection("Ma in", dbDriverNoPrompt, False, gsODBCConnectString$)
gsODBCConnectString$ = Connect=ODBC;DSN=my_dsn;Da tabase=my_ db;UID=sa; PWD=;Login Timeout=10
then i open a recordset (DAO 3.6)
Set msnTable = gcnMain.OpenRecordset("sel ect * from mytable", dbOpenSnapshot)
then i access the fields:
sTemp = msnTable!numeric_field
sTemp = msnTable!money_field
when i get to line 1 numeric_field...i get the value, line 2 i get the error
I suspect that during the odbc connection it ignores the problem accessing this value, bypasses it somehow, and then only complains when i try to access it.
I also tried using cast in my sql say to numeric but then i lose the money data type's functionality anyway, and i have to repeat that everywhere in the code (or schema) which i don't want.
i set the odbc connection
Set gcnMain = DBEngine.Workspaces("Main"
gsODBCConnectString$ = Connect=ODBC;DSN=my_dsn;Da
then i open a recordset (DAO 3.6)
Set msnTable = gcnMain.OpenRecordset("sel
then i access the fields:
sTemp = msnTable!numeric_field
sTemp = msnTable!money_field
when i get to line 1 numeric_field...i get the value, line 2 i get the error
I suspect that during the odbc connection it ignores the problem accessing this value, bypasses it somehow, and then only complains when i try to access it.
I also tried using cast in my sql say to numeric but then i lose the money data type's functionality anyway, and i have to repeat that everywhere in the code (or schema) which i don't want.
Sorry... I have no idea :(
ASKER
It's ok . I've emailed Microsoft about it too, but they haven't gotten back to me either :(
Thanks for trying :-)
Cheers
Anybody else any ideas?
Thanks for trying :-)
Cheers
Anybody else any ideas?
ASKER
I have found the answer to my problem.
The option on the odbc unfortunately caused some conflict when i had the default language set to German in SQL. If i leave the odbc unchecked, and the German default language, and convert my numeric data type to decimal (functionally the same) i overcome the problems I had.
Thanks to everyone who attempted to help.
The option on the odbc unfortunately caused some conflict when i had the default language set to German in SQL. If i leave the odbc unchecked, and the German default language, and convert my numeric data type to decimal (functionally the same) i overcome the problems I had.
Thanks to everyone who attempted to help.
ASKER
Correction I don't need to set the server to German either now. It will still work, just reads the Regional Settings (i must have mixed up as part of my testing). Numeric still has to be converted Decimal which is functionally the same to SQL.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.