SQL Server dropping comma in German language

Gubbs

All,

I've a VB6 application that writes data to a SQL Server database using ADODB in the VB6 code. My market has been the United States. My application is written for the English language.

I've a new user in Germany. Unfortunately, every number that contains a decimal is now stored in the SQL Server database with the decimal dropped. For example, 29.15 gets stored as 2915. I've done some searching and found the culprit is probably the OS Regional Language setting. The Germany user is using German. So it seems ADODB is expressing "29.15" as "29,15", and SQL Server is having a fit when ADODB is trying to write to the database.

My client is reluctant to changing all their user profile language settings to German (or the other option, changing their German language setting decimal symbol option to "."). They fear other applications may be affected.

Can anyone suggest a solution on my software end Does ADODB have some options of which I'm unaware that will ignore the regional language setting and express decimal-containing numbers simply as "." Or is there a setting in SQL Server to compensate

I appreciate all and any help. Thanx!

Re: SQL Server dropping comma in German language

Arnie Rowland

No direct setting in SQL Server to help you handle both language settings in the same data. And unfortunately, VB 6 and ADO have limited facility to handle the situation without writing code.

As you indicated, the issue is a stress point between the applications' display using regional settings, and the server storing decimal values.

VB.NET provides a rich facility to handle regional setting differences. This may be a good 'excuse' to push for the upgrade.

You could have the app 'sniff' the Regional settings from the registry, and then wrap a REPLACE function around the textbox.text, removing periods, and then swapping commas for periods, before storing the data.

Re: SQL Server dropping comma in German language

Gubbs

Thanx Arnie for your input.

The response is timely. I did some digging in my code, did some testing, and found this:

The culprit in this observation was a save routine I was employing:

- The routine first assigns each value to be stored in the database to a variable of variant datatype.

- After doing some checking to ensure data correctness, ADODB writes the value to the database

- When the regional language is English, ADODB seems to convert the variant variable to the datatype expected by the database

-e.g 29.15 gets written as 29.15

- When the regional language is German, either ADODB or the database gets confused, and the comma-decimal delimeter is dropped

- e.g. 29,15 gets written as 2915

- When the regional language is German and I change the variable to the correct datatype before writing to the database (e.g. vba.csng(var1)), the correct value gets written to the database

- e.g. 29,15 gets written as 29,15

The feature of ADODB to determine the correct datatype in the database I found was a plus as it greatly simplified some of my code. However, now that I see the problem in a global environment, the feature seems to be liability since it doesn't seem to be robust. Or maybe my code was just risky. Either way, I'm updating my code to explicitly format the value to match the database.

Thanx for you help!

Re: SQL Server dropping comma in German language

Arnie Rowland

When you start exploring VB.NET, you will find that the globalization issue has been greatly enhanced, and it is 'possible' to intimately marry the client application to the regional settings without effecting the datastore.