Access 数据库未正确保存十进制值

问题描述:

我在尝试在 Access 数据库的表中保存十进制值时遇到一些问题.

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

如果我输入值,没有问题,但是当我尝试使用我的程序保存它们时,数据库不会保存小数并删除(8,7变成87).

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).

表中的字段为小数、小数位数、精度 8 和小数位 2.

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)

我也尝试将价格设为 double,但我仍然遇到同样的问题.

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

我查看了很多地方并阅读了有关将 Access DB 上的数据类型更改为 Double 的内容,但没有奏效.

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

任何想法将不胜感激!

正如 cybermonkey 所说的 Decimal 值为 8,7 所以我试着用这种方式改变它.

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)

价格 2 是 8.7,但数据库再次显示 87,00

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

编辑 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.

之后,我调试程序以查看十进制值是如何存储的,并且是:8,7 而不是 8.7.然后我尝试在 db 上插入数据,如果你输入 8.78,7,它可以很好地处理这两个值,所以现在我不对为什么这不起作用有任何其他想法.

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.

我已经能够重现该问题.在 Windows 系统区域设置不使用句点字符 (.) 作为十进制符号的计算机上运行时,它似乎是 Access OLEDB 提供程序的不幸功能".

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.

使用以下代码

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)

当我在 Windows 设置为英语(美国)"的情况下运行它时,我看到了调试输出

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.

并且值 87 被插入到数据库中,因为字符串8,7"在该语言环境中不是有效的十进制数.

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

当 Windows 设置为西班牙语(西班牙)"时,现在生成相同的代码

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

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

但值 87 仍被插入.

but the value 87 is still inserted.

Windows 设置为法语(加拿大)"时,调试输出相同

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)

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

证明这是System.Data.OleDb 和Access OLEDB 提供程序之间的问题,而不仅仅是TableAdapter 的特性.然而,TableAdapters 似乎完全依赖于 OleDb,所以不幸的是,它们可能无法在这些条件下工作.

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.

好消息是,简单地将 OleDb 代码转换为 Odbc 似乎已经解决了西班牙语(西班牙)"和法语(加拿大)"Windows 语言环境的问题

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

因此一种可能的解决方法可能是使用 OdbcDataAdapter 而不是 TableAdapter.

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