Access 2007和存储十进制值 [英] Access 2007 and storing decimal value

问题描述

我已经可以看到很多主题，但是没有一个解决我的问题.

I can see alot of topics already, but none solved my problem.

我来自斯洛文尼亚. 我正在使用英文版的Access 2007. 使用c#.

I'm from Slovenia. I'm using English version of Access 2007. Using c#.

我的语言环境设置全部设置为斯洛文尼亚语.

My locale settings are all set to Slovenian.

我有一个带十进制列的访问数据库. 当我尝试将5,4保存到其中时，它将忽略并存储54.如果我尝试将5.4存储到其中，它也将忽略.并存储54.

I have a access database with decimal column. When I try to save 5,4 into it, it ignores , and stores 54. If i try to store 5.4 into it, it also ignores . and stores 54.

我正在使用数据表来处理代码中的数据.

I'm using datatables to manipulate data in code.

public static DataSet DbData;
public static DataRow DbRow;

我尝试了以下解决方案，但都不起作用(始终使用5,4作为输入):

I have tried following solutions, none of them work (always using 5,4 for input):

DbRow[6] = Convert.ToDecimal(txtNovCena.Text); // value of DbRow[6] is 5,4
DbRow[6] = Decimal.Parse(txtNovCena.Text, new System.Globalization.CultureInfo("NL-nl").NumberFormat); // value of DbRow[6] is 5,4
DbRow[6] = Decimal.Parse(txtNovCena.Text, new System.Globalization.CultureInfo("EN-us").NumberFormat); // value of DbRow[6] is 54
DbRow[6] = Decimal.Parse(txtNovCena.Text, new System.Globalization.CultureInfo("sl-SI").NumberFormat); // value of DbRow[6] is 5,4

DbRow[6] = txtNovCena.Text.Replace(",", "."); // value of DbRow[6] is 54

之后存储在数据库中的内容

What is stored in DB after

DbAdapter.Update(DbData);

始终为54.

我还能尝试什么?

另外，如果我尝试直接在Access中运行此sql语句:

Also, if I try to run this sql statement directly in Access:

UPDATE Sifrant
SET cena = 1,1
WHERE ID = 2;

我在语句中收到语法错误

I get Syntax error in statement

但它适用于:

UPDATE Sifrant
SET cena = 1.1
WHERE ID = 2;

在打开的accdb文件的GUI中，小数分隔符显示为.

Decimal separator is visible as , in GUI of opened accdb file.

推荐答案

不幸的是，Decimal字段在Access中存在许多问题，通常建议

Unfortunately, Decimal fields have a number of issues in Access and it's generally recommended not to use them.
It seems like there is an underlying bug somewhere in how OleDB talks to the driver.

经过一些测试，我可以复制您的问题.
以下是一些解决方案:

After some testing I could replicate your problem.
Here are a couple of solutions:

1. 确定要在数据库中使用Decimal字段吗?
   似乎很愚蠢，但是您是否100％确定需要存储在数据库中的值范围确实需要Decimal而不是简单的Double?
   如果不需要，只需在Access中更改数据库字段的数字类型； 确实不需要十进制类型即可.

1. Are you sure you need a Decimal field in your database?
   Seems silly, but are you 100% sure that the range of values you need to store in the database really warrant a Decimal and not a simple Double instead?
   Just change the numeric type of your database field in Access if you don;t really need a Decimal type.

使用OleDbType.Double或OleDbType.Currency
在为该字段定义OldDbParameter时，请使用OledDbType.Double或OledDbType.Currency而不是OledDbType.Decimal.
请注意，尽管保存的值可能会四舍五入，但是如果您需要的精度超出.Net为这些类型提供的精度，请进行彻底测试.

Use OleDbType.Double or OleDbType.Currency
When defining the OldDbParameter for that field, use OledDbType.Double or OledDbType.Currency instead of OledDbType.Decimal.
Be careful though that the value saved may be rounded, so test thoroughly if you require precision beyond what .Net offers for these types .

附加测试:

• 我还尝试将执行线程的区域性更改为en-US或使用Invariant区域性，但是它不起作用，问题似乎确实是.Net与Jet/Ace对话方式之间的特定不匹配数据库引擎进行更新.
• 我尝试使用Odbc而不是OleDb，但是使用OdbcType.Decimal时出现错误ERROR [07006] [Microsoft][ODBC Microsoft Access Driver]Restricted data type attribute violation.

• I also tried to change the culture of the executing thread to en-US or use the Invariant culture, but it doesn't work, the issue does appear to be a specific mismatch between how .Net talks to the Jet/Ace database engine for its updates.
• I tried using Odbc instead of OleDb, but I got an error ERROR [07006] [Microsoft][ODBC Microsoft Access Driver]Restricted data type attribute violation when using OdbcType.Decimal.

测试代码:

我创建了具有表myTest的Access Database1.accdb，该表具有3个字段:

I created an Access Database1.accdb with a table myTest that has 3 fields:

• ID，一个自动编号
• ValueDecimal，定义为精度为6的十进制
• ValueDouble，定义为Double

• ID, an Autonumber
• ValueDecimal, defined as a Decimal with a Precision of 6
• ValueDouble, defined as a Double

在运行测试之前，我已将Windows区域设置切换为斯洛文尼亚语:

I switched my Windows regional settings to Slovenian before running the tests:

static void tryoledb() {
    var strAccessConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Database1.accdb";
    string strAccessSelect = "SELECT * FROM myTest";

    DataSet myDataSet = new DataSet();
    OleDbConnection myAccessConn = null;
    myAccessConn = new OleDbConnection(strAccessConn);
    OleDbCommand myAccessCommand = new OleDbCommand(strAccessSelect, myAccessConn);
    OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);

    myAccessConn.Open();
    myDataAdapter.Fill(myDataSet, "myTest");

    var mytable = myDataSet.Tables["myTest"];
    DataRowCollection dra = mytable.Rows;

    // Set the field ValueDecimal 
    dra[0][1] = 5.4;
    dra[1][1] = 5.4m;
    dra[2][1] = decimal.Parse("5.4");
    dra[3][1] = decimal.Parse("5,4");

    // Set the field ValueDouble 
    dra[0][2] = 5.4;
    dra[1][2] = 5.4m;
    dra[2][2] = double.Parse("5.4");
    dra[3][2] = double.Parse("5,4");

    var command = new OleDbCommand("UPDATE myTest SET ValueDecimal = ?, ValueDouble = ? WHERE ID = ?", myAccessConn);
    var p = command.Parameters.Add("ValueDecimal", OleDbType.Decimal); // Change to OldDbType.Double to make it work
    p.SourceColumn = "ValueDecimal";
    p.SourceVersion = DataRowVersion.Current;

    p = command.Parameters.Add("ValueDouble", OleDbType.Double);
    p.SourceColumn = "ValueDouble";
    p.SourceVersion = DataRowVersion.Current;

    p = command.Parameters.Add("ID", OleDbType.Integer);
    p.SourceColumn = "ID";
    p.SourceVersion = DataRowVersion.Original;

    myDataAdapter.UpdateCommand = command;
    myDataAdapter.Update(mytable);
    myAccessConn.Close();
}

这篇关于Access 2007和存储十进制值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！