Access insert numeric with decimal comma into Postgresql

Question

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

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
– Mathieu Guindon, Commented Nov 1, 2017 at 20:21
use parameters (prepared query). It is basic protection for non-US applications (numbers, currecies, dates) — Jacek Cz
– Jacek Cz, Commented Nov 1, 2017 at 20:25
@JacekCz it's basic protection for anything that remotely deals with SQL, US or not. — Mathieu Guindon
– Mathieu Guindon, Commented Nov 1, 2017 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
– Guido, Commented Nov 2, 2017 at 9:15

Parfait · Accepted Answer · 2017-11-01 22:31:14Z

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

answered Nov 1, 2017 at 22:31

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Guido Over a year ago

Thanks a lot. This is what I needed

Guido Over a year ago

My special thanks for the excellent example. This really get me on the way.

Gustav · Accepted Answer · 2017-11-02 08:35:54Z

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.

answered Nov 2, 2017 at 8:35

Gustav

56.4k7 gold badges33 silver badges65 bronze badges

2 Comments

Guido Over a year ago

Thanks. By using quotes, I basically did the same. The Csql is not an Access function to my knowledge.

Gustav Over a year ago

Then, please add your basically same solution to your question. And right, CSql is not native to VBA, that's why it is written.

Charles_D · Accepted Answer · 2017-11-01 22:34:17Z

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

edited Nov 1, 2017 at 22:34

user6655984

answered Nov 1, 2017 at 22:12

Charles_D

114 bronze badges

Collectives™ on Stack Overflow

Access insert numeric with decimal comma into Postgresql

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related