הערה
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות להיכנס או לשנות מדריכי כתובות.
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות לשנות מדריכי כתובות.
Question
Monday, March 10, 2014 9:43 PM
Hi,
I have written an app which until now was only used in the UK.
The app stores values, which can be string or numeric in an access database (field type is text). I know many of you will say it's bad to store a number in a text field, but there are reasons for me to do it this way, and it's too late to back track & change.
Until now this has not been a problem, but now I have an Italian user who's numeric format is different.
When I store 1,000.20 as a string in the UK, my value is stored as string "1,000.20" (without the quotes).
When I store 1.000,20 as a string in Italy (not the comma separator) my value is stored as "1.000,20" (without the quotes) - note the switch of comma and period which is Italian format.
This causes me a problem when I try to convert the string to a number one thousand in Italy (1.000) gets converted to 1 in code.
I am trying to use globalization to solve this issue
I have tried (before writing to the db)
Decimal.Parse((Value),Globalization.CultureInfo.CreateSpecificCulture("en-Gb")).ToString
and
Decimal.Parse((Value),Globalization.CultureInfo.InvariantCulture).ToString
but any value less than 0 gets factored by 10, so 0.2 (in Italian format 0,2) gets formatted to 2.
I need for the value to store in the DB in UK format, correctly formatted with a period (as I also force formatting to a variety of decimal places dependant on the number use so 0, 1, 2 or 3 dp's).
Can anyone help?
Thanks,
J.
All replies (11)
Sunday, April 20, 2014 12:15 PM ✅Answered | 2 votes
If I understand this correctly you have stored numbers in the DB as text using a specific culture (en-GB). An Italian user wants to use this data. That means that the DB values will have to be decoded using en-GB and then shown in a different culture, it-IT. Here is some code that converts the numbers in the DB to Italian, and Italian input to the culture of the DB.
Dim dbCult As System.Globalization.CultureInfo = New Globalization.CultureInfo("en-GB")
Dim itCult As System.Globalization.CultureInfo = New Globalization.CultureInfo("it-IT")
Dim style As System.Globalization.NumberStyles = Globalization.NumberStyles.AllowDecimalPoint Or Globalization.NumberStyles.AllowThousands
Dim numInDB As String = "1,000.23" 'number as stored in database
Dim cnvrtdDB As Decimal 'numInDB converted
Dim numStrIT As String = "" 'cnvrtdNum as italian string
Dim numIT As Decimal
'convert the text stored in the database
If Decimal.TryParse(numInDB, style, dbCult, cnvrtdDB) Then
'at this point the number was converted from the
'text database format
'now, what does it look like in italy
numStrIT = cnvrtdDB.ToString("n2", itCult)
Debug.WriteLine(numStrIT)
End If
'to store italian input (text) into the DB
'convert italian input to number
If Decimal.TryParse(numStrIT, style, itCult, numIT) Then
numIT += 0.01D 'for test
'convert number to DB culture
numInDB = numIT.ToString("n2", dbCult)
End If
"Those who use Application.DoEvents() have no idea what it does and those who know what it does never use it." JohnWein
Monday, March 10, 2014 11:36 PM | 1 vote
Hello,
Perhaps simply setting the application culture will help i.e. shows us changing the culture to each available culture then back to the original. Note in each case I show the raw and formatted value. Now of course this is for the app, not sure how MS-Access side will handle this as MS-Access works with the culture of the computer it resides on.
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim Remember As Globalization.CultureInfo = Application.CurrentCulture
Console.WriteLine("Current culture {0}", Remember.Name)
Dim MyDec As Decimal = 1000.56D
Application.CurrentCulture = New Globalization.CultureInfo("it-IT")
Dim Cultures = CultureList()
For Each c In CultureList()
Application.CurrentCulture = New Globalization.CultureInfo(c.Name)
Console.WriteLine("{0,12} {1,10} {2,10}", c.Name, MyDec, MyDec.ToString("n2"))
Next
Application.CurrentCulture = New Globalization.CultureInfo("en-Gb")
Console.WriteLine(MyDec.ToString("n2"))
Application.CurrentCulture = Remember
Console.WriteLine("Current culture {0}", Remember.Name)
End Sub
Public Function CultureList() As Globalization.CultureInfo()
Return (From T In Globalization.CultureInfo.GetCultures(Globalization.CultureTypes.SpecificCultures)
Order By T.EnglishName).ToArray
End Function
End Class
Current culture en-US
af-ZA 1000.56 1,000.56
sq-AL 1000,56 1.000,56
gsw-FR 1000,56 1 000,56
am-ET 1000.56 1,000.56
ar-DZ 1000.56 1,000.56
ar-BH 1000.56 1,000.56
ar-EG 1000.56 1,000.56
ar-IQ 1000.56 1,000.56
ar-JO 1000.56 1,000.56
ar-KW 1000.56 1,000.56
ar-LB 1000.56 1,000.56
ar-LY 1000.56 1,000.56
ar-MA 1000.56 1,000.56
ar-OM 1000.56 1,000.56
ar-QA 1000.56 1,000.56
ar-SA 1000.56 1,000.56
ar-SY 1000.56 1,000.56
ar-TN 1000.56 1,000.56
ar-AE 1000.56 1,000.56
ar-YE 1000.56 1,000.56
hy-AM 1000.56 1,000.56
as-IN 1000.56 1,000.56
az-Cyrl-AZ 1000,56 1 000,56
az-Latn-AZ 1000,56 1 000,56
ba-RU 1000,56 1 000,56
eu-ES 1000,56 1.000,56
be-BY 1000,56 1 000,56
bn-BD 1000.56 1,000.56
bn-IN 1000.56 1,000.56
bs-Cyrl-BA 1000,56 1.000,56
bs-Latn-BA 1000,56 1.000,56
br-FR 1000,56 1 000,56
bg-BG 1000,56 1 000,56
ca-ES 1000,56 1.000,56
zh-CN 1000.56 1,000.56
zh-SG 1000.56 1,000.56
zh-HK 1000.56 1,000.56
zh-MO 1000.56 1,000.56
zh-TW 1000.56 1,000.56
co-FR 1000,56 1 000,56
hr-HR 1000,56 1.000,56
hr-BA 1000,56 1.000,56
cs-CZ 1000,56 1 000,56
da-DK 1000,56 1.000,56
prs-AF 1000,56 1.000,56
dv-MV 1000.56 1,000.56
nl-BE 1000,56 1.000,56
nl-NL 1000,56 1.000,56
en-AU 1000.56 1,000.56
en-BZ 1000.56 1,000.56
en-CA 1000.56 1,000.56
en-029 1000.56 1,000.56
en-IN 1000.56 1,000.56
en-IE 1000.56 1,000.56
en-JM 1000.56 1,000.56
en-MY 1000.56 1,000.56
en-NZ 1000.56 1,000.56
en-PH 1000.56 1,000.56
en-SG 1000.56 1,000.56
en-ZA 1000.56 1 000.56
en-TT 1000.56 1,000.56
en-GB 1000.56 1,000.56
en-US 1000.56 1,000.56
en-ZW 1000.56 1,000.56
et-EE 1000,56 1 000,56
fo-FO 1000,56 1.000,56
fil-PH 1000.56 1,000.56
fi-FI 1000,56 1 000,56
fr-BE 1000,56 1.000,56
fr-CA 1000,56 1 000,56
fr-FR 1000,56 1 000,56
fr-LU 1000,56 1 000,56
fr-MC 1000,56 1 000,56
fr-CH 1000.56 1'000.56
fy-NL 1000,56 1.000,56
gl-ES 1000,56 1.000,56
ka-GE 1000,56 1 000,56
de-AT 1000,56 1.000,56
de-DE 1000,56 1.000,56
de-LI 1000.56 1'000.56
de-LU 1000,56 1.000,56
de-CH 1000.56 1'000.56
el-GR 1000,56 1.000,56
kl-GL 1000,56 1.000,56
gu-IN 1000.56 1,000.56
ha-Latn-NG 1000.56 1,000.56
he-IL 1000.56 1,000.56
hi-IN 1000.56 1,000.56
hu-HU 1000,56 1 000,56
is-IS 1000,56 1.000,56
ig-NG 1000.56 1,000.56
id-ID 1000,56 1.000,56
iu-Latn-CA 1000.56 1,000.56
iu-Cans-CA 1000.56 1,000.56
ga-IE 1000.56 1,000.56
xh-ZA 1000.56 1,000.56
zu-ZA 1000.56 1,000.56
it-IT 1000,56 1.000,56
it-CH 1000.56 1'000.56
ja-JP 1000.56 1,000.56
kn-IN 1000.56 1,000.56
kk-KZ 1000,56 1 000,56
km-KH 1000.56 1,000.56
qut-GT 1000.56 1,000.56
rw-RW 1000,56 1 000,56
sw-KE 1000.56 1,000.56
kok-IN 1000.56 1,000.56
ko-KR 1000.56 1,000.56
ky-KG 1000,56 1 000,56
lo-LA 1000.56 1,000.56
lv-LV 1000,56 1 000,56
lt-LT 1000,56 1.000,56
dsb-DE 1000,56 1.000,56
lb-LU 1000,56 1 000,56
mk-MK 1000,56 1.000,56
ms-BN 1000,56 1.000,56
ms-MY 1000.56 1,000.56
ml-IN 1000.56 1,000.56
mt-MT 1000.56 1,000.56
mi-NZ 1000.56 1,000.56
arn-CL 1000,56 1.000,56
mr-IN 1000.56 1,000.56
moh-CA 1000.56 1,000.56
mn-MN 1000,56 1 000,56
mn-Mong-CN 1000.56 1,000.56
ne-NP 1000.56 1,000.56
nb-NO 1000,56 1 000,56
nn-NO 1000,56 1 000,56
oc-FR 1000,56 1 000,56
or-IN 1000.56 1,000.56
ps-AF 1000,56 1?000,56
fa-IR 1000.56 1,000.56
pl-PL 1000,56 1 000,56
pt-BR 1000,56 1.000,56
pt-PT 1000,56 1.000,56
pa-IN 1000.56 1,000.56
quz-BO 1000,56 1.000,56
quz-EC 1000,56 1.000,56
quz-PE 1000.56 1,000.56
ro-RO 1000,56 1.000,56
rm-CH 1000.56 1'000.56
ru-RU 1000,56 1 000,56
smn-FI 1000,56 1 000,56
smj-NO 1000,56 1 000,56
smj-SE 1000,56 1 000,56
se-FI 1000,56 1 000,56
se-NO 1000,56 1 000,56
se-SE 1000,56 1 000,56
sms-FI 1000,56 1 000,56
sma-NO 1000,56 1 000,56
sma-SE 1000,56 1 000,56
sa-IN 1000.56 1,000.56
gd-GB 1000.56 1,000.56
sr-Cyrl-BA 1000,56 1.000,56
sr-Cyrl-ME 1000,56 1.000,56
sr-Cyrl-CS 1000,56 1.000,56
sr-Cyrl-RS 1000,56 1.000,56
sr-Latn-BA 1000,56 1.000,56
sr-Latn-ME 1000,56 1.000,56
sr-Latn-CS 1000,56 1.000,56
sr-Latn-RS 1000,56 1.000,56
nso-ZA 1000.56 1,000.56
tn-ZA 1000.56 1,000.56
si-LK 1000.56 1,000.56
sk-SK 1000,56 1 000,56
sl-SI 1000,56 1.000,56
es-AR 1000,56 1.000,56
es-VE 1000,56 1.000,56
es-BO 1000,56 1.000,56
es-CL 1000,56 1.000,56
es-CO 1000,56 1.000,56
es-CR 1000,56 1.000,56
es-DO 1000.56 1,000.56
es-EC 1000,56 1.000,56
es-SV 1000.56 1,000.56
es-GT 1000.56 1,000.56
es-HN 1000.56 1,000.56
es-MX 1000.56 1,000.56
es-NI 1000.56 1,000.56
es-PA 1000.56 1,000.56
es-PY 1000,56 1.000,56
es-PE 1000.56 1,000.56
es-PR 1000.56 1,000.56
es-ES 1000,56 1.000,56
es-US 1000.56 1,000.56
es-UY 1000,56 1.000,56
sv-FI 1000,56 1 000,56
sv-SE 1000,56 1 000,56
syr-SY 1000.56 1,000.56
tg-Cyrl-TJ 1000,56 1 000,56
tzm-Latn-DZ 1000,56 1.000,56
ta-IN 1000.56 1,000.56
tt-RU 1000,56 1 000,56
te-IN 1000.56 1,000.56
th-TH 1000.56 1,000.56
bo-CN 1000.56 1,000.56
tr-TR 1000,56 1.000,56
tk-TM 1000,56 1 000,56
uk-UA 1000,56 1 000,56
hsb-DE 1000,56 1.000,56
ur-PK 1000.56 1,000.56
ug-CN 1000.56 1,000.56
uz-Cyrl-UZ 1000,56 1 000,56
uz-Latn-UZ 1000,56 1 000,56
vi-VN 1000,56 1.000,56
cy-GB 1000.56 1,000.56
wo-SN 1000,56 1 000,56
sah-RU 1000,56 1 000,56
ii-CN 1000.56 1,000.56
yo-NG 1000.56 1,000.56
1,000.56
Current culture en-US
Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.
Sunday, March 23, 2014 7:58 AM
Thanks for you reply. In understand how to grab different cultures, my problem is that the parsing of 'numeric' strings doesn't work properly.
I pass all my string value into this proc
Public Function ValueToNeutralCulture(Value As String) As String
Dim strReturn As String
If IsNumeric(Value) Then
strReturn = Double.Parse((Value), Globalization.CultureInfo.InvariantCulture).ToString
Return strReturn
Else
Return Value
End If
End Function
If I set my PC to be use Italian formatting, passing in a string which can be converted to a decimal just strips out the decimal separator, so passing in "123,456" for example, passes back 123456. But is I switch my PC back to en-GB and pass in 123.456, it returns 123.456.
So my previous post was a bit misleading - it seems that parsing the value using invariant culture, just strips out the decimal separator, so numbers with 'point zero' appeared to be factored by ten. (ie 10,0 was returning 100).
Can anyone help? Driving me crazy...
Thanks,
J
Saturday, April 19, 2014 5:43 AM
I have attached a screen shot to see if anyone can help with this.
AS you can see, if I parse my value through the InvariantCulture in the immediate window, it's fine.
If you look at the hover tip in the code window, you will notice that stuffing the same result into a Double results in it being rounded by 1000. So 485.756999999995 has become 485756.0
I don't understand how calling the function through the immediate window can produce a different result.
Can anyone help?
Saturday, April 19, 2014 7:56 AM
Have you tried converting string to decimal or back to the country code you want it in?
Decimal.Parse((Value),Globalization.CultureInfo.InvariantCulture).ToString("0.00")
or
Decimal.Parse((Value),Globalization.CultureInfo.InvariantCulture).ToString("D")
Convert tostring by setting its culture if your string is a currency
Decimal.Parse((Value),Globalization.CultureInfo.InvariantCulture).ToString("C", "en-US")
you might have to set it to decimal literal before converting .tostring?
Decimal.Parse((Value),Globalization.CultureInfo.InvariantCulture)D.ToString
Saturday, April 19, 2014 11:05 AM
In you experiment Value is already a Double and ToString() does not necessary produce a string that can be converted back with your Parse. You should use the same culture in ToString and Parse. Try this experiment:
Dim s = Value.ToString(c)
Dim d = Double.Parse(s, c)
where c is either CultureInfo.InvariantCulture or CultureInfo.GetCultureInfo("it-IT") depending on your needs.
Saturday, April 19, 2014 7:50 PM
Hi,
I have written an app which until now was only used in the UK.
The app stores values, which can be string or numeric in an access database (field type is text). I know many of you will say it's bad to store a number in a text field, but there are reasons for me to do it this way, and it's too late to back track & change.
Until now this has not been a problem, but now I have an Italian user who's numeric format is different.
Jone,
This sound to me the same like if you had written.
Until now I've always driven on the left lane and there are reasons I want to continue with that.
But now I want to travel to Italy and I don't want to change that behaviour.
Can you help me by telling what is the best way to drive on the left lane on the continent?
Numbers represented in a string can be in every country in the world different, even more than driving left or right, so your question is more silly then my reply above.
Just do it in the way it is designed and store it for instance as decimals.
Anybody who helps you to drive on the left lane in the continent is a potential killer.
Success
Cor
Saturday, April 19, 2014 8:01 PM
Jone,
This sound to me the same like if you had written.
Until now I've always driven on the left lane and there are reasons I want to continue with that.
But now I want to travel to Italy and I don't want to change that behaviour.
Can you help me by telling what is the best way to drive on the left lane on the continent?
Numbers represented in a string can be in every country in the world different, even more than driving left or right, so your question is more silly then my reply above.
Just do it in the way it is designed and store it for instance as decimals.
Anybody who helps you to drive on the left lane in the continent is a potential killer.
Success
Cor
Well geez, you could be a little less blunt about it Cor!
(...by the way - I agree with you).
Please call me Frank :)
Sunday, April 20, 2014 8:18 AM
Interesting analogy...not really helpful to me tho.
As stated, I cannot store the values as decimals as it is way to late to reengineer all I have done . Bad call at the start? Maybe. Unfortunately I have a database in the field with this design in the field with several hundred customers, and several hundred thousand lines of code that would need to be reviewed and changed in order for me to do what you propose - it's just not practical.
I guess my question really is, why does the invariant culture not work (or for that matter forcing an Italian formatted number to Parse as English-UK)?
If a function is provided to convert one country format to another, why does it just remove the decimal symbol and the digit grouping symbol?
What use is passing "1,234.0" through the parsing function and have it return 12340?
Thanks,
J.
Sunday, April 20, 2014 10:41 AM
"1,234.0" is simply not a number, it is a string or varchar.
Human brains are not the same like computers, we translate that to a number.
But going on in my analogy, going on with this you will soon become in trouble, so try to get the database better be done or simply decide never to go from the islands.
With trying to convert hundred of thousand lines of bad code, you will see that somewhere a conversion is forgotten.
If that means for instance a sell for 1000000 pennies while it had to be 1000000 pounds they quick knows who caused this: the program and that means the programmer.
Success
Cor
Monday, April 21, 2014 12:41 PM
That sorts my problem perfectly - thank you so much!