Currency Conversions SQL/VB6

[brandonb]

I have an issue at work here that I may need some help achieving.

We are creating ad-hoc SQL (on the fly) statements, using data from strings.

INSERT INTO table (currency) VALUES (123.40)

That works in English. However, once you change over to French language, their currency decimal turns into a comma.

INSERT INTO table (currency) VALUES (123,40)

That breaks the SQL statement obviously.

So our boss decided to place code in there, always convert comma to decimal point... That will write to the table correctly.

However, the problem is now when we read in the data.

In VB6, we take the value we get from the database:

Currency = CCur("123.40")

This works on English machine. However, not on the French machine, because it can't convert that style because its expecting:

Currency = CCur("123,40")

What the heck do I do to make this work? Anybody have any ideas or where for me to start looking?

 

[Unheard]

Are you having to use just one column or could you create another column to store the French version vs. the US version?

 

[alexeikgb]

Do you have a column in the currency table to tell you if the value is US vs Canada?
If you do u can use that to convert back to ',' when the value is Canadian
If not, it might help to store the value, country pairs over just the value.

EDIT:

Actually, what is the column type that is storing the currency? If it is a String/Varchar/Text type then adding quotes around the value will save the comma

INSERT INTO table (currency) VALUES ('123,40')