Solved

VB, SQL and German Regional Settings

Posted on 2004-04-19
11
465 Views
Last Modified: 2008-03-17
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?
0
Comment
Question by:tiseffies
  • 7
  • 3
11 Comments
 
LVL 5

Expert Comment

by:chaniewskim
ID: 10866622
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.
0
 

Author Comment

by:tiseffies
ID: 10873664
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

0
 

Author Comment

by:tiseffies
ID: 10873683
P.S. Numeric data type works..don't know why money is a problem
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:tiseffies
ID: 10874158
I realise that ADO fixes the problem...but that is a very big change for me to perform at the moment......
0
 
LVL 5

Expert Comment

by:chaniewskim
ID: 10876831
Say, is that error occuring exactly when you access that field?
Could you provide a fragment of code?
0
 

Author Comment

by:tiseffies
ID: 10884709
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").OpenConnection("Main", dbDriverNoPrompt, False, gsODBCConnectString$)

gsODBCConnectString$ = Connect=ODBC;DSN=my_dsn;Database=my_db;UID=sa;PWD=;LoginTimeout=10

then i open a recordset (DAO 3.6)
Set msnTable = gcnMain.OpenRecordset("select * 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.
0
 
LVL 5

Expert Comment

by:chaniewskim
ID: 10886381
Sorry... I have no idea :(
0
 

Author Comment

by:tiseffies
ID: 10895054
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?
0
 

Author Comment

by:tiseffies
ID: 10924495
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.
0
 

Author Comment

by:tiseffies
ID: 10946398
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.
0
 

Accepted Solution

by:
PashaMod earned 0 total points
ID: 10971896
Closed, 500 points refunded.
PashaMod
Community Support Moderator
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
groupSumClump challenge 9 115
Rubik's Cube Code for Effective Presentation 3 54
Advice in Xamarin 21 79
What are programming codes and how do they work? 7 94
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question