vb.net - Access database doesn't save decimal values correctly

Question

I'm having some problems trying to save decimal values within a table in my Access database.

If I type the values, there is no problem, but when I try to save them using my program, the database won't save decimals and removes the ,(8,7 turns into 87).

The field in the table is Decimal, Scale 2, Precision 8, and Decimal places 2.

I'm using this code to insert data:

Dim price as Decimal
ProductsTableAdapter.Insert(id,name,price)

I have tried to put the price as a double too and I still have the same problem.

I have looked on many places and read about changing the Datatype on the Access DB to Double but it didn't work.

Any ideas would be appreciated!

Edit:

As cybermonkey said the Decimal value is 8,7 so I tried to change it this way.

I changed the code to replace , for a .:

Dim price2 As String
price2 = price.ToString.Replace(",", ".")
ProductTableAdapter.Insert(id, name, price2)

Price 2 is 8.7, but again the database shows 87,00

Edit 2:

I have tried to create another project with a different db to determinate if the problem was this specific adapter or not, but i have the same problem again.

After that, i debug the program to see how the decimal value is stored and is: 8,7 instead of 8.7. Then i try to insert data on the db and its the same if you put 8.7or 8,7, it works fine with those two values, so now i don't have any other idea of why this doesn't work.

Answer 1

I have been able to recreate the issue. It appears to be an "unfortunate feature" of the Access OLEDB provider when operating on a machine where the Windows system locale does not use the period character (.) as the decimal symbol.

Using the following code

Dim price As Decimal = Convert.ToDecimal("8,7")
Dim sb As New System.Text.StringBuilder("""price"" is a ")
sb.Append(price.GetType.FullName)
sb.Append(" whose value is ")
If price < 1 Or price > 10 Then
    sb.Append("NOT ")
End If
sb.Append("between 1 and 10.")
Debug.Print(sb.ToString)

ProductsTableAdapter.Insert("myProduct", price)

when I run it with Windows set to "English (United States)" I see the debug output

"price" is a System.Decimal whose value is NOT between 1 and 10.

and the value 87 is inserted into the database because the string "8,7" is not a valid decimal in that locale.

With Windows set to "Spanish (Spain)" the same code now produces

"price" is a System.Decimal whose value is between 1 and 10.

but the value 87 is still inserted.

With Windows set to "French (Canada)" the debug output is the same

"price" is a System.Decimal whose value is between 1 and 10.

however, the insert fails with "Data type mismatch in criteria expression."

The exact same results were achieved by replacing

ProductsTableAdapter.Insert("myProduct", price)

with

Dim myConnStr As String
myConnStr =
        "Provider=Microsoft.ACE.OLEDB.12.0;" &
        "Data Source=C:UsersPublicDatabase1.accdb"
Using con As New OleDbConnection(myConnStr)
    con.Open()
    Using cmd As New OleDbCommand("INSERT INTO Products ([name], price) VALUES (?,?)", con)
        cmd.Parameters.AddWithValue("?", "oledbTest")
        cmd.Parameters.AddWithValue("?", price)
        cmd.ExecuteNonQuery()
    End Using
End Using

proving that this is a problem between System.Data.OleDb and the Access OLEDB provider, and not merely an idiosyncrasy of the TableAdapter. However, TableAdapters seem to rely exclusively on OleDb, so unfortunately they probably will just not work under these conditions.

The good news is that simply converting the OleDb code to Odbc appears to have fixed the issue for both the "Spanish (Spain)" and "French (Canada)" Windows locales

Dim myConnStr As String
myConnStr =
        "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" &
        "DBQ=C:UsersPublicDatabase1.accdb"
Using con As New OdbcConnection(myConnStr)
    con.Open()
    Using cmd As New OdbcCommand("INSERT INTO Products ([name], price) VALUES (?,?)", con)
        cmd.Parameters.AddWithValue("?", "odbcTest")
        cmd.Parameters.AddWithValue("?", price)
        cmd.ExecuteNonQuery()
    End Using
End Using

so one possible workaround might be to use an OdbcDataAdapter instead of a TableAdapter.