哪个是区域设置感知的：OleDb.Currency 或 OleDb.Decimal？
这是双重的。 我有一个 Access 数据库和一个包含 MS Access 货币字段的表。 我在美国的客户使用 1.23 等十进制值，而我在厄瓜多尔的客户则使用 1.23 等十进制值。
我有一些 ADO 遗留代码，并且尝试使用 adDecimal 类型和 adCurrency 类型创建 ADODB 参数。 无论哪种情况，执行 ADODB 命令后，Access 中的数据对于美国为 1.23（预期），对于厄瓜多尔为 123.00（非预期）。
在我的 .NET 代码中，我尝试使用 OleDb.Currency 和 OleDb.Decimal 类型的 OleDb 参数。 看起来 OleDb.Currency 是区域设置感知的，但 OleDb.Decimal 不是。
我头晕目眩。 有谁知道我的遗留代码中国际货币的 ADO 正确用法，以及随着我们的代码库的发展编写 .NET 参数的正确方法？
谢谢！
This is two-fold. I have an Access database and a table containing MS Access Currency fields. My customers in the USA use decimal values like 1.23 and my customers in Ecuador use decimal values like 1,23.
I have some ADO legacy code and I've tried creating ADODB Parameters with type adDecimal and also with type adCurrency. In either case, after my ADODB Command is executed the data in Access comes in as 1.23 (expected) for USA and 123.00 for Ecuador (not expected).
In my .NET code, I've tried using OleDb parameters with type OleDb.Currency and OleDb.Decimal. It seems like the OleDb.Currency is locale-aware but OleDb.Decimal is not.
My head is spinning. Does anybody know the correct ADO usage for international currency for my legacy code and also the correct way to write the .NET parameters as our codebase moves forward?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
好的，经过一些额外的挖掘，似乎“货币”类型比“小数”类型更受青睐。 换句话说，将 adCurrency 用于 ADO 或 OleDb.Currency 将适用于保存到 MS Access 货币字段的十进制值。
以防万一其他人也遇到这种情况 - 我确实在 ADO 情况下发现了一个非常奇怪的行为，如下所示：
我的代码实例化了一个新的 ADODB 命令并设置了参数集合 - 包括一些类型为 adCurrency 的参数。 然后，我开始更新数据库中的几条记录。 我为每次更新重复使用相同的命令对象，并调用一个方法在下一个更新块开始之前将所有参数字段重置为 0。 这导致我的字段中出现错误数据（请参阅上面描述的初始问题）。 我尝试了很多东西来解决这个问题但没有成功。 我什至注释掉了对 ResetParameters 方法的调用，以确保行为不相关。
修复：我最终决定在每个更新块之前新建一个全新的命令对象，而不是重用命令对象。 突然间，无论我的区域设置如何，我的所有十进制值都以正确的方式出现在 Access 中。
总之，调用 Execute 方法后，我的命令对象似乎发生了某些修改，导致后续更新的 adCurrency 参数被破坏。 顺便说一句，结果如下：4,32 值在 Access 中显示为 432（这是厄瓜多尔货币格式）。 我的代码并不是非常复杂，我不会将其发布在这里，但我们两个人非常努力地研究这个问题，但我们只是没有看到任何可以解释它的东西。 事实上，当我们编写一个仅调用单个更新的测试应用程序并且在测试中运行的代码与预期完全相同时，创建新实例的想法就产生了。
如果您正在编写本地化应用程序，请注意重复使用 ADO 命令对象进行多次更新。
Ok, so after some additional digging, it appears that the "Currency" types are preferred over the "Decimal" types. In other words, using adCurrency for ADO or OleDb.Currency will work for decimal values being saved to MS Access Currency fields.
Just in case this comes up for anybody else - I did find a awfully strange behavior in the ADO case as follows:
My code instanced a new ADODB Command and set up the Parameters collection - including some typed as adCurrency. Then, I started updating several records in my database. I reused the same command object for each update and called a method to reset all the parameter fields to 0 before the next update block was started. This resulted in bad data in my fields (see the initial problem described above). I tried lots of stuff to fix this and was unsuccessful. I even commented out the call to my ResetParameters method just to make sure the behavior wasn't related.
Fix: Rather than reuse the command object, I finally decided to just new-up a brand-new command object prior to each update block. Suddenly, all of my decimal values appeared in Access in the proper way regardless of my locale settings.
In summary, it appears something was modified on my command object after calling the Execute method such that the adCurrency parameter on subsequent updates was broken. By the way, the results were as follows: a 4,32 value would appear in Access as 432 (this being the Ecuador currency formatting). My code wasn't ultra-complex and I won't post it here but there were two of us looking at this pretty hard and we just didn't see anything that could explain it. In fact, the idea of creating a new instance came about when we wrote a test app that only called a single update and the EXACT SAME CODE WORKED IN THE TEST AS EXPECTED.
Beware reuse of ADO command objects for multiple updates if you are writing a localized app.
内部类型不支持区域设置，它们只是数字。
区域设置会影响您从输入中捕获它们并将其显示到窗口或报告中的时间。
The internal types are no locale-aware, they are just numbers.
The locale affects when you capture them from the input and display them to a window or report.
@爱德华多，
我明白你所说的格式化。 在这种情况下，我根本不向 UI 或任何输出显示数据。 当我完全从代码发出数据库更新时，我遇到了意外的行为。
我真的不太熟悉像 Jet（用于 Access 数据库）这样的提供商底层发生的情况。 命令参数是否被转换为稍后解析的实际 SQL 字符串，或者参数是否以某种方式直接使用？ 我不知道。
我确信的是，我所看到的行为表明提供程序逻辑的作者区分“货币”和“小数”类型，从而将数字参数插入适当的 MS Access 类型。 当我们使用错误的参数类型时，内部会发生转换并弄乱结果。
一个看似更常见的问题（也更容易找到解决方案）是处理日期。 再次以厄瓜多尔为例，该地区的日期格式为日/月/年，而不是美国的月/日/年。 像这样的 SQL 字符串不会很好地工作：
UPDATE Customer SET DateOfBirth = #03/06/1970#
SQL 解析器将始终将此日期读取为 3 月 6 日（去美国！），但在厄瓜多尔，它实际上意味着 6 月 3 日。 解决此问题的方法是，在将日期添加到 SQL 字符串之前，始终将其转换为普遍接受的格式：
UPDATE Customer SET DateOfBirth = #1970-06-03# (June 3rd, 1970)
围绕货币的“陷阱”有点多微妙 - 事实上我的 ADO 参数在循环迭代期间没有表现（请参阅上面接受的答案），这很奇怪。
@Eduardo,
I understand what you are saying about formatting. In this case I'm not displaying data to a UI or any output at all. I'm getting the unexpected behavior when I issue a database update completely from code.
I'm really not that familiar with what happens low-level with a provider like Jet (used for Access database). Does the command parameter get translated into an actual SQL string which is parsed later or are the parameters consumed directly somehow? I'm not sure.
What I am sure of is that the behavior I'm seeing indicates that the authors of the provider logic differentiate between "currency" and "decimal" types in such a way that the numeric parameters are slotted into the appropriate MS Access type. When we use the wrong parameter type, a conversion occurs internally and messes up the result.
A seemingly more common issue (and easier to find fixes for) are dealing with dates. Using Ecuador again as an example, the date format in that region is day/month/year instead of month/day/year in the USA. This a SQL string like this isn't going to work well:
UPDATE Customer SET DateOfBirth = #03/06/1970#
The SQL parser will always read this date as March 6th (go USA!) but in Ecudaor it actually means June 3rd. The fix for this is to always convert dates to a universally accepted format before adding them to the SQL string:
UPDATE Customer SET DateOfBirth = #1970-06-03# (June 3rd, 1970)
The 'gotcha' around currency was a little more subtle - and the fact that my ADO parameter wasn't behaving during the loop iteration (see accepted answer above) was way strange.
使用 Double 而不是 Decimal 来声明数据库 MSAccess 中列的类型。
它有效并且独立于区域设置。
Use Double instead of Decimal to declare the type of the columns in the database MSAccess.
It works and it's independent of regional settings.