您的位置: 首页  >  技术问答  >  Access数据库无法正确保存十进制值

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

分类: 技术问答 • 2022-04-23 15:48:27
问题描述：

尝试在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).

表中的字段为小数，小数位数2，精度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

我试图用不同的数据库创建另一个项目，以确定问题是否出在这个特定的适配器上，但是我又遇到了同样的问题.

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.然后，我尝试在数据库上插入数据，并且如果您将8.7或8,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.

在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:\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

证明这是System.Data.OleDb和Access OLEDB提供程序之间的问题，而不仅仅是TableAdapter的特性.但是，TableAdapter似乎完全依赖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:\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

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

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

相关推荐