Changing culture Info

[shnizle]

hey , i am using an access DB as part of .net application ,
i need to start a connection to the access MDB file so the culture info of dates and numbers will always be on "en-us".

does anyone know if its possible to achieve that and how?

[NeoPa]

Is that something to do with your connection? I'm afraid your question is not very clear (to me at least). You may get better results if you explain it again a little more clearly, remembering we cannot see it and rely on your explanation, mingled in with our own general Access experience.

Welcome to Bytes!

[shnizle]

sorry about that,
here is the full story:

I am working on a project developed in vb.net, and using MDB files as databases,
recently we have encountered a serious problem as we tested our application abroad (Germany) ,
we have found out that our application can't handle the German decimal separator , they use a comma instead of a dot, what happened is every parsing from string to number got messed up ,
for example parsing the number 4.23 would result in 423, and many more problems.

as a temporary solution untill we code all the project correctly with globalization format parsing,
we want to lock our application to en-us culture only, which means the application would display an "en-us" cultureInfo only at the UI and data.

so i have found a way doing that in .NET framework , it is working great ,
the problem now is the connection to MS Access, right now if i use a comp using German Regional settings , i see my application the same as en-us but when linking to the database ,
the db still on german regional settings which creates various conflicts ,

THE QUESTION:
is there anyway i can set a connection to access mdb files so the connection will be in "en-us" format?? ( dates numbers ....) on insert queries should be en-us all the time.

any tip on the subject will be very helpful thanks in advance!!

[NeoPa]

Ah.

I don't think there is. Let me explain.

Access BE (Back-End) databases are really not like other BE databases (which are true servers). Access processes the BE databases via the local Access engine. There is no facility to separate local settings between local databases and the BE ones.

I would ask why this should make any difference. As long as the data is all accessible it should show as German in Germany and English in USA etc. The only problems I could foresee is that certain numeric fields are stored as strings. If that is the case I would certainly consider fixing this part of the problem instead, and letting each user see the data as the convention for their country indicates.

[FishVal]

Hello, shnizle.

Where does number/string conversion take place.
Does it take place within Access queries or in FE code?

This is all about the following -

...
but when linking to the database ,
the db still on german regional settings which creates various conflicts
...

How do you get these values?
Do you get them as strings which are obtained as calculated fields of Access query?
If no, then "regional format issue" is all about your FE .NET code.

[NeoPa]

As a temporary solution, you could try seeing if you could save the current country settings associated with a PC then set them to "en-us" on the fly. When completed processing of course, they should be set back to their original settings. You haven't really indicated which value this is, so I can't help much with the coding, but I suspect there is something that allows this somewhere.

[shnizle]

Hey Thanks for the replay everyone,

FishVal: the conversion takes place in .net for example:

Dim test as double = 4.2
dim str as String = test.toString()  ' <--------- in German format 4,2
Console.writeLine("Hello German " &  double) ' <--- Hello Germen 4,2

things like that will cause a disaster when i will try to insert some values into the database using a crafted string query.

i know its a programing mistake but i need a fast temporary solution to make the application work on all regional settings no matter what, the reason: it will take few months to convert the project to be globalized correctly.


NeoPa :

i didn't understand what did you reffer to in your sentence:
" You haven't really indicated which value this is, so I can't help much with the coding"

what value ?

and do you mean i should set the whole computer regional settings to "en-us" ?
and then back to original ?

i think it would be a bit of a problems cause an average company have a restriction for this kind of activity.

[FishVal]

So.

It looks like there is nothing Access related in the question.
Just find a method in .NET to convert number to properly formated string or write your own.

[shnizle]

if you read the full story ,
the access related issue is that , im trying to force a certain culture info on the application ( " en-us " ) , i have managed to do that successfully on the application side but not on the connection side to aceess i still have access on a german regional settings which creates various conflicts with dates

( the project contains millions lines of code and the conversion will take months with testings ) , as i said im looking for a temporary solution to solve the above problem.

[FishVal]

@shnizle
What does "access on a german regional settings" mean?
I guess you query Access database via ADO or ODBC connection.
So, values, you get in recordset are the same binary digits sequence all over the world. What regional format do you mention?
The only way you could get number formatted according to regional settings is maybe calculated fields in Access db queries where function used to convert number to formatted string is affected by regional settings.
Is that the case? If no, then, could you elaborate on what the Access part of the issue is all about?

[shnizle]

the main issue with MS-ACCESS connection after i have converted my project to be locked to "en-us" culture is the dates, when using a crafted String for an insert query which contains dates in it, the dates are in en-us format, but when executing the query Access expects dates in German formats which is dd.mm.yyyy ,

the question is if theres any way i can set the connection to read dates or whatever is en-us format.

thanks for replying.

[NeoPa]

You don't appear to have read (or possibly just not understood) my post #4.

Let me try to clarify (pretty well the same as Fish is saying BTW) :

Dates are all stored the same the world over.
When accessing them in SQL there is only one correct (standard) way to do it. If you are using date strings WITHOUT formatting them explicitly into M/D/Y format (standard for SQL - See Literal DateTimes and Their Delimiters (#)) first, then you are not doing it correctly I'm afraid.

With this understanding, I suspect the problem will simply disappear. Let us know if it doesn't.

[FishVal]

@shnizle
Could you post examples of these SQL commands?

[shnizle]

ok i guess, i was wrong about the dates stuff,
while reExaming the problem i have seen that the problem is not with the dates as i suspected but still im getting an unUsual behavior i will update you if i get to any conclusion , thanks for your answer.

[shnizle]

ok, it is a date issue :)
(My computer Regional settings are set to German)
the problem is when i select for example the following query:
(i always use M/d/yyyy format)

SELECT * FROM DAILYHISTORY WHERE `dDate` = #1/2/2005#

i get rows which their dates are 02.01.2005 which is 2/1/2005,

and there for my question rise again , is there any way to set a connection to MS ACCESS DB so everything would be on (us-en) format?

or whats wrong there?

[FishVal]

This brings us back to what we've started from.

The date value is the same, just its visual appearance is in accordance with german regional settings. It is not the Access which "returns date in German format", it is .NET code which formats the date when writing to Console or converting to string. This make problem when you further try to incorporate thus formatted string into SQL expression.

[shnizle]

so this brings me back to the original question ,
is there a way to force access connection to recognize dates formated in en-us only as a temporary solution, or i will have to reconstruct all the project to be generic for all dates and number formats that all i wish to know.

i am looking for a temporary fast solution to make the project be able to work on every machine no matter what the regional settings are set to, i acknoladge that theres a fundamental problem with the .net code, i'm just looking for something temporary that will solve this issue until i rewrite all the project to be globalized enabled.

[NeoPa]

@shnizle
You do realise you've used 1/2/2005 as an example don't you? We still don't have a clear idea of what you're trying to say. Please clarify which date you're talking about :- 1st Feb or 2nd Jan.

You seem to be saying that Access is not responding to the standard date format for literals in SQL. This I find hard to believe. Are you sure this is a literal date string you're using and not a value specified somehow else (It's only date values embedded within the SQL string itself which work this way)?

The last item of confusion is the character (`) you use to delineate your field name. I can only assume this is working well for you as, if this were failing then your results would be quite different. In my experience though, the correct way to delineate names within SQL is to use [] characters.

[NeoPa]

@shnizle
That's a hard question to answer, as SQL is already designed specifically to do just that. There are no regional variations within SQL. The designers realised that having such regional variations would cause this type of problem everywhere. You can't do anything to make the SQL any more region independent.

[shnizle]

i said i am using en-us format so 1/2/2005 = january 2nd 2005
en-us format is - MM/dd/yyyy or in this example : M/d/yyyy

[NeoPa]

@shnizle
You also said :
@shnizle
plus various other comments that would tend to make the point very ambiguous, which was why I was particularly surprised that you would continue with your explanation simply expressed as 1/2/2005. It's not as if it wasn't already clear that we were having trouble trying to understand what you were saying, after wasting quite a lot of time trying to lead you to express the problem clearly enough to work with.

To be honest, with that sort of attitude you can continue without any further contributions from me. I see no sign you've read them anyway so I'm sure that won't hurt you.

[DonRayner]

Shiznle, can't you understand what NeoPa and FishVal are trying to tell you!!!

When storing a date to a field in access that has it's type set to Date/Time, Access doesn't store the information as dates. It is stored as double precision floating point number. The integer portion represents the date and the decimal portion represents the time. Your problem lays in how your .net code is interpreting this number.

Here is a link to a MS KB article explaining it.

[shnizle]

i can see that,
but for some reason when my computer regional setting is set to en-us my application works great, on the other hand when i set my computer to Germany ,
yet i force the application to work with en-us format it has lots of bugs,
so i dont see how it is possible.

[shnizle]

sorry NeoPa i thought i was very clear with all the description i gave you here,
it doesn't seem to be so complicated to me.

[DonRayner]

@shnizle
Maybe try over in the .Net form to see if anybody there has insights on working with dates and different regional settings.

[shnizle]

ok thank you all i didn't mean to disrespect anyone,
it seems like i would just have to rewrite the project with no temporary solution that i am looking for.

[NeoPa]

@shnizle
We're good Shnizle. I got a little frustrated, but I'm good now.