访问将带有小数点逗号的数字插入到Postgresql中 [英] Access insert numeric with decimal comma into Postgresql

问题描述

我需要使用Access VBA在订单行上插入链接的PostgreQL表中.为了便于报告，我决定将netto价格包括在数值18,2字段中. 我的计算机有一个比利时时期，使用逗号作为小数点分隔符.即0.8表示为0,8

I need to insert on orderline into a linked PostgreQL table using Access VBA. For easy reporting, I decided to include the netto price which is a Numeric 18,2 field. My computer has a Belgian period using comma as decimal separator. i.e. 0.8 is represented as 0,8

如果插入语句，这是有问题的部分

This is the problematic part if the insert statement

mijnSQL = "INSERT INTO tblOrderLijnen (OrderID, Nettoprijs )"
mijnSQL = mijnSQL & " VALUES (" & NieuwOrderId& "', " & MijnTempOrderLijn!Prijs * ((100 - Korting) / 100) & ");"

计算结果为0.8(在我的计算机上为0.8)

The result of the calculation is 0.8 (on my computer 0,8)

DoCmd.RunSQL mijnSQL

转换为十进制值无效的查询，因为小数点是逗号.我该如何解决?

Translates into a query where the decimal value is invalid because the decimal point is a comma. How can I solve this?

INSERT INTO tblOrderLijnen (OrderID, OrderNr,ArtikelID,Aantal,Nettoprijs ) 
VALUES (216, 0,8);

字段数不匹配

我将插入更改为引用十进制值.这似乎可行，但这是否有效?我以后可以遇到问题吗?

I changed the insert to quoting the decimal value. This seems to work, but is it valid? Can I run into problems later?

如果插入语句，这是有问题的部分

This is the problematic part if the insert statement

mijnSQL = "INSERT INTO tblOrderLijnen (OrderID, Nettoprijs )"
mijnSQL = mijnSQL & " VALUES (" & NieuwOrderId& "', " & MijnTempOrderLijn!Prijs * ((100 - Korting) / 100) & ");"

推荐答案

除了简单地防止SQL注入外，还考虑SQL参数化(应用程序层代码(如VBA)中使用的任何SQL语句的行业标准).不仅限于Access或Postgres.参数化有助于避免用引号引起来，转义特殊字符，字符串串联以及指定数据类型以与区域设置保持一致.

Consider SQL parameterization (an industry standard for any SQL statement used in application layer code like VBA) beyond simply protecting against SQL injection. And not just for Access or Postgres. Parameterization helps avoid quote enclosures, escaping special characters, string concatenation, and specifying data types to align with regional settings.

在MS Access中，您可以使用PARAMETERS子句(在Access SQL方言中有效)并使用

In MS Access, you can use the PARAMETERS clause (valid in Access SQL dialect) and bind values in VBA using querydefs. Additionally, as seen code is cleaner and more maintainable.

Dim qdef As QueryDef
...

' PREPARED STATEMENT (NO DATA)
mijnSQL = "PARAMETERS [firstparam] Long, [secondparam] Double;" _
           & " INSERT INTO tblOrderLijnen (OrderID, Nettoprijs)" _ 
           & " VALUES ([firstparm], [secondparam]);"

' INITIALIZE QUERYDEF
Set qdef = CurrentDb.CreateQueryDef("", mijnSQL)

' BIND PARAMS
qdef![firstparam] = NieuwOrderId
qdef![secondparam] = MijnTempOrderLijn!Prijs * ((100 - Korting) / 100)

' EXECUTE ACTION QUERY
qdef.Execute dbFailOnError

Set qdef = Nothing

这篇关于访问将带有小数点逗号的数字插入到Postgresql中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！