Problems with comma "," and dot "." in decimals-values when inserting data in Access-db from .net-code

Or you may get away passing integers only ...

/gustav

int a1 = _recipeID;
int a2 = (int)(_qty * 100);
int a3 = _ingredientID;
int a4 = _unitTypeID;
                        
OleDbCommand cmd = new OleDbCommand("INSERT INTO TableName (field1, field2, field3, field4) VALUES (?, ? / 100, ?, ?)", aConnection);
 
cmd.Parameters.Add("@a1", OleDbType.Integer).Value = a1;
cmd.Parameters.Add("@a2", OleDbType.Integer).Value = a2;
cmd.Parameters.Add("@a3", OleDbType.Integer).Value = a3;
cmd.Parameters.Add("@a4", OleDbType.Integer).Value = a4;

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

it-row, its all about regional settings.

double myValue = Convert.ToDouble(textBox1.Text);

If RegionalSettings are set to English(UnitedStates), and user types 123.45 myValue will be 123.45.
If RegionalSettings are set to Danish, and user types 123,45 myValue will be 123,45.

So, in both cases result will be correct, its up to application user to select proper regional settings.

Goran

My region-settings is danish and the user types a value with comma.
double a2 = Convert.ToDouble("3,4");
When I use the code above, the result (a2) is 3.4 and that is my problem. The result in .net is always saved with dot instead of a comma and the database is only saving decimals with comma.

Try this then........

cmd.Parameters.Add("@a2", OleDbType.Decimal).Value = a2.ToString()

Thanks
Ajitha

The result will be 3 and 4/10, which is a correct result. I dont understand what do you mean by: "The result in .net is always saved with dot instead of a comma". The result is not "saved  in .NET", its saved in MS Access database, and it has nothing to do with .NET. MS Access will interpret decimal sign based on the regional settings. Example, set the regional settings in English (United States). OPen database MS Access, open this table that contains decimal valkues, enter 3.4 . Value will be saved as decimal number. Close MS Access database, Change REgionalSettings to Danish, reopen MS Accesss, go to rhe table you were before. Now you can see that record you have endeterd just before is displayed as 3,4 which is again a decimal number. If you now try to enter decimal number, you will need to enter it with comma.

I never had any problem with above approach (converting to decimal and save it to database using parameters). IF I dont understand your problem, please be more precise.

In the above example I have used MS Access database (and left out .NET from scenario), so you can clearly see what is going one. When you see that it behaves correctly, you can then do the same with .NET and parameterized query. Just remember that when you change regional settings, its important to close and reopen application, since it needs to pick up new settings.

> its up to application user to select proper regional settings.

It just has to build a correct SQL string. This involves converting decimal values to string expressions where dot is the decimal separator.

> its up to application user to select proper regional settings.

No, it uses the regional settings if no other locale is specified which is the typical case. Thus, as in this case, when comma is the decimal separator, a decimal value typed by the user is expected to have a comma as the decimal separator.

> MS Access will interpret decimal sign based on the regional settings

Nothing specific to Access here. It is an SQL string that is created and this must contain a dot as the decimal separator. If a numeric is passed as a parameter, it will be casted to a string and it is done according to the locale in use which - here - is read from the regional settings.

All this mess can be avoided by using DataTableAdapters avoiding all the low level SQL stuff, but that is another discussion.

Two secure solutions exist. One is the work-around described previously (passing integers only), the other is to convert a decimal to a string expression in SQL by using the native function Str which always converts to a decimal expression with a dot.

/gustav

int a1 = _recipeID;
decimal a2 = _qty;
int a3 = _ingredientID;
int a4 = _unitTypeID;
			
OleDbCommand cmd = new OleDbCommand("INSERT INTO TableName (field1, field2, field3, field4) VALUES (?, STR(?), ?, ?)", aConnection);
 
cmd.Parameters.Add("@a1", OleDbType.Integer).Value = a1;
cmd.Parameters.Add("@a2", OleDbType.Decimal).Value = a2;
cmd.Parameters.Add("@a3", OleDbType.Integer).Value = a3;
cmd.Parameters.Add("@a4", OleDbType.Integer).Value = a4;
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

this smells like a debate to me. :)

> t just has to build a correct SQL string. This involves converting decimal values to string expressions where dot is the decimal separator.

No, it doesnt. MS Access will accept comma as decimal sign IF comma is the decimal sign set in regional settings.

> Nothing specific to Access here. It is an SQL string that is created and this must contain a dot as the decimal separator.

This is again incorrent, sql can contain comma as decimal separator. Try it yourself. Set comma as decimal sign in regional settings, and crate a quick parametrized query that qill insert some double value in MS Access. It will work correclty with comma as decimal, and incorrectly if dot is used as decimal separator. Then open database in MS Access, see the inserted value, and see how it is displayed (with comma). The main

> All this mess can be avoided by using DataTableAdapters avoiding all the low level SQL stuff, but that is another discussion.

Please explain what does table adapter do so special, that command object with parameterized query cant do (which is what it-row is using in code)? They all do the same thing, table adapter uses internally command object to do CRUD operations.

The information I have provided here is based on the fact that my programs is working flawlessly for years, without any problems with decimal sign, and most of my users use comma as decimal sign.

Interpreting numbers as strings is a very bad idea, and leads to many problems in the future, when the application starts growing, so it is the last option to consider.

What I believe it_row wants here, when user presses dot key on numeric keypad, is to convert this sign to comma, Is this correct?

> MS Access will accept comma as decimal sign IF comma is the decimal sign set in regional settings.

Not when passed as an SQL string as here.
SQL strings like this example will fail:
INSERT INTO TableName (field1, field2, field3, field4) VALUES (2, 0,75, 5, 11)
while these will succeed:
INSERT INTO TableName (field1, field2, field3, field4) VALUES (2, 0.75, 5, 11)
INSERT INTO TableName (field1, field2, field3, field4) VALUES (2, 75/100, 5, 11)

However, I found out that my previous other suggestion:

INSERT INTO TableName (field1, field2, field3, field4) VALUES (?, STR(?), ?, ?)

doesn't work because the casting to a string takes place before STR can do anything.

If you don't like the method for using integers only, the parameter must be passed as a string as shown below. The OleDb parser seems to do the casting correctly using the code below even though the string in the locale in question will use comma as the decimal separator. This can be verified by appending the line:
  Console.WriteLine(cmd.Parameters[1].Value);

This I would recommend as the solution because the code shows what is taken place.
The a2.ToString() could be reduced to just a2 if you prefer.

As for the DataTableAdapters, the use of these or even other strongly typed higher level code like the ADO.NET Entity Model framework is highly recommended as they automatically take care for cases like this. Just google for it to find numerous tutorials - far too much to go into here.

/gustav

int a1 = _recipeID;
decimal a2 = _qty;
int a3 = _ingredientID;
int a4 = _unitTypeID;
			
OleDbCommand cmd = new OleDbCommand("INSERT INTO TableName (field1, field2, field3, field4) VALUES (?, ?, ?, ?)", aConnection);
 
cmd.Parameters.Add("@a1", OleDbType.Integer).Value = a1;
cmd.Parameters.Add("@a2", OleDbType.VarChar).Value = a2.ToString();
cmd.Parameters.Add("@a3", OleDbType.Integer).Value = a3;
cmd.Parameters.Add("@a4", OleDbType.Integer).Value = a4;
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11: