comma vs point in double with an access database

Question

In my C# program, I use a strongly typed dataset with an access database. In one table of the DB, I use a double. With some cultures, access uses a comma instead of a point for a double value. I can read the value without a problem (IE if access uses a comma: "10,25" is read as a "10.25" double value). But when I want to insert a new value, I get an exception if access uses a comma and one of the values is a decimal (IE "10" is insert, "10.25" throw the exception). The exception is thrown when I update the DB, not when I create a new row in the dataset.

As I use a dataset, I thought that these problems were automatically handled by .Net.

How can I manage comma/point (I think I must get the culture of the user, then force the dataset to use it. But how?)?

PS : I can't change the computer culture, so I need to manage all cultures by code.

Please, show the relevant code! The place where are you inserting the 10.25 in code. — Olivier Jacot-Descombes, Jul 29 '15 at 21:48
why can't you do a check along with using the string.Format function and string.Replace so that if one contains `,` replace it with a `.` or vice versa along with the string.Contains function this can be done show some code.. also why are your databases not setup consistently to begin with in terms of defining the data type / format.. displaying and saving are 2 different things so this can be accomplished — MethodMan, Jul 29 '15 at 21:49
Ideally you would set the application up so that when the current culture uses `,` instead of `.` it will be handled automatically. This might help: https://stackoverflow.com/questions/518625/c-sharp-doubles-show-comma-instead-of-period — Saragis, Jul 29 '15 at 21:51
http://stackoverflow.com/questions/5855753/parse-string-to-decimal-commas-and-periods — MethodMan, Jul 29 '15 at 21:52
As I explain in my comment to the answer of Olivier Jacot-Descombes, I can't see the sql request. I use only double in my code, then pass them to the auto-created dataset code. — bubarnet, Jul 30 '15 at 14:27

Olivier Jacot-Descombes · Answer 1 · 2015-07-29T22:16:54.207

Note that doubles do not contain commas or points. They store the number in a binary representation internally using a mantissa and an exponent. The comma vs point issue only comes into play if you have a string representation of the double (most likely a sql string).

Do not write:

// Here the double is in a string
string sql = "INSERT INTO mytable (id, value) VALUES (1, 10.25)";
using (OleDbCommand cmd = new OleDbCommand(sql, conn)) {
    cmd.CommandType = CommandType.Text;
    conn.Open();
    cmd.ExecuteNonQuery();
 }

Instead use command parameters:

// Here the double is never converted to a string
string sql = "INSERT INTO mytable (id, value) VALUES (?, ?)";
using (OleDbCommand cmd = new OleDbCommand(sql, conn)) {
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("?", 1);
    cmd.Parameters.AddWithValue("?", 10.25);
    conn.Open();
    cmd.ExecuteNonQuery();
}

Of course, in the C# code the double is written in a textual representation. However, when compiled and at runtime the double exists in its pure binary form, not remembering the character of the decimal separator used in the code.

I can't post code, because I use a strongly typed dataset created by visualstudio. I use one of the functions provided by the dataset : dataset.nom_table.Addnom_tableRow(param1, param2, ...). Then I call dataset.UpdateAll(). This function calls this.Adapter.Update() for each tables in the dataset. And it's this function which throw the execption if one of its parameters is a double with decimal. I suspect a problem in the sql request (I can't see the final sql sdtring used by the dataset), something likes that : UPDATE nom_table SET value=10,25 — bubarnet, Jul 30 '15 at 14:23

comma vs point in double with an access database

1 Answers1