Access database doesn't save decimal values co

问题:

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.

回答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:\Users\Public\Database1.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:\Users\Public\Database1.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.

回答2:

The issue seems to be that .NET is expecting you to provide the Decimal with a dot instead of a comma, which is due to how .NET handles localization (according to this).

This means that .NET is stripping out the , and instead of inserting your expected 8.7 you get 87. This also explains why manually entering the data directly into the database works, but programmatically entering it does not.

Reading up on the issue it seems that using System.Globalization.CultureInfo.InvariantCulture works, but however after some time I was able to do this by converting the Decimalinto a String Constant:

Const price As String = "8,7"
Dim price2 As String
price2 = String.Format(price, _
price.ToString(System.Globalization.CultureInfo.InvariantCulture))
ProductsTableAdapter.Insert(id,name,price)

The MSDN documentation states that you also need to Import the Globalization namespace using Imports System.Globalization.

回答3:

I had the same issue but working on a C# application, just in case somebody has the same problem I'm gonna post what did the trick for me...

This is really bizzare but I found it on some forum with no explanation why it works.

When passing parameters of my SQL command all I did was add ToString() on the decimal parameter like this:

cmd.Parameters.AddWithValue("@DecimalParameter", model.DecimalParameter.ToString());

And decimal value is now stored correctly!