4

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) & ");"

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

Number of fields do not match

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) & ");"
Parfait
  • 104,375
  • 17
  • 94
  • 125
Guido
  • 41
  • 4
  • 2
    A solution would be to use a properly parameterized query instead of converting everything to strings and concatenating them - i.e. pass the float values as such to the server. That said the number of fields is incorrect even if your values are `216` and `0.8`: you're specifying 2 values for 5 fields. – Mathieu Guindon Nov 01 '17 at 20:21
  • use parameters (prepared query). It is basic protection for non-US applications (numbers, currecies, dates) – Jacek Cz Nov 01 '17 at 20:25
  • 3
    @JacekCz it's basic protection for anything that remotely deals with SQL, US or not. – Mathieu Guindon Nov 01 '17 at 20:26
  • Thanks. I will look at parametrizing. The error in the number of fielsds is due to my simplifying the query. Thanks to Mat for his added comment. – Guido Nov 02 '17 at 09:15

3 Answers3

4

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.

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
Parfait
  • 104,375
  • 17
  • 94
  • 125
1

Use Str to convert. It will always force a dot as the decimal separator:

mijnSQL = mijnSQL & " VALUES (" & NieuwOrderId& "', " & Str(MijnTempOrderLijn!Prijs * ((100 - Korting) / 100)) & ");"

Or build your concatenated SQL using my CSql function.

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Thanks. By using quotes, I basically did the same. The Csql is not an Access function to my knowledge. – Guido Nov 02 '17 at 09:13
  • Then, please add your basically same solution to your question. And right, CSql is not native to VBA, that's why it is written. – Gustav Nov 02 '17 at 10:24
0

This should work:

Dim Nettoprijs_temp as Single
Nettoprijs_temp = MijnTempOrderLijn!Prijs * ((100 - Korting) / 100)
mijnSQL = "INSERT INTO tblOrderLijnen (OrderID, Nettoprijs )VALUES (NieuwOrderId, " & Nettoprijs_temp & ");"
Charles_D
  • 11
  • 4