Incorrect syntax near '' in vba code for sql

Question

I'm getting this error because try to put Itemnr2 variable as cell value. If I use plain value like in ItemNr1 all works. But I need to use value from cell because it will change time to time so don't want change code every time. How can I put value from cell to SQL code to get it correct?

It is working when I use it in other column where all values are numbers. In this column which I need there are values like b2b_pkl, and because of it I get those Conversion failed when converting the nvarchar value to data type int errors.

PA = get_market_setting(market, "PA")
POS = get_market_setting(market, "POS")
NetAmount = get_market_setting(market, "Net Amount")
CostAmount = get_market_setting(market, "Cost Amount")
qty = get_market_setting(market, "Quantity")
transNo = get_market_setting(market, "Transaction No.")
ItemNo = get_market_setting(market, "Item No.")
CustNo = "[Customer No_]"
ItemNr1 = "('80502842')"
Itemnr2 = ThisWorkbook.Sheets("Data").Range("C1").Value
ECPOS_arr = "(" & get_market_setting("LT", "ECPOS_Arr") & ")"



' SQL query string
    myquery = "SELECT -SUM(" & NetAmount & ") AS [Result] FROM " & table_TSE & _
        " WHERE " & ItemNo & " IN " & Itemnr2 & _
        " AND [Date] BETWEEN '" & date_from & "' AND '" & date_to & "'"
        

' Set Recordset to query set database on SQL string
    Set sourceRs = sourceDb.OpenRecordset(myquery)

'Assign Data to variable from RecordSet'
sourceRs.MoveFirst
NetAmount = sourceRs![Result]

sourceRs.Close
sourceDb.CloseRecordset
'Populate HFB Report file'

enter image description here

Please do not post pictures of code. Instead include the code itself so people can copy/paste it if they want. — braX, Dec 15 '21 at 09:43
Manually constructing the SQL string with data from a worksheet that you don't control is probably not a good idea because it would be at risk of SQL injection. See https://stackoverflow.com/questions/35163361/how-can-i-add-user-supplied-input-to-an-sql-statement for a discussion of that, which would probably also solve your problem here. — Christopher Hamkins, Dec 15 '21 at 09:51
When I put parentheses like in ECPOS_arr variable I got error "Conversion failed when converting the nvarchar value to data type int. — , Dec 15 '21 at 10:01
what is in cell C1? is it a single value or a comma separated list of values? Also please state what DBMS you are trying to access, is it SQL Server? — Jayvee, Dec 15 '21 at 10:22
In C1 is same value as in ItemNr1 (80502842), so no comma inside cell. I'm connecting to Navision by using SQL. — , Dec 15 '21 at 10:45
It seems that the problem is that some of the integers values are already between parentheses and/or single quotes. How does itemNo looks like? Also does ItemNr2 include the single quotes as does ItemNr1? — Jayvee, Dec 15 '21 at 11:06
It is working when I use it in other column where all values are numbers. In this column which I need there are values like b2b_pkl, and because of it I get those Conversion failed when converting the nvarchar value to data type int errors. — , Dec 15 '21 at 13:45

Jayvee · Accepted Answer · 2021-12-15T13:53:48.977

0

If ItemNo and Itemnr2 always have parentheses and no single quotes then should be treated as strings :

Itemnr2 = ThisWorkbook.Sheets("Data").Range("C1").Value

myquery = "SELECT -SUM(" & NetAmount & ") AS [Result] FROM " & table_TSE & _
    " WHERE '" & ItemNo & "' = '" & Itemnr2 & _
    "' AND [Date] BETWEEN '" & date_from & "' AND '" & date_to & "'"

edited Dec 15 '21 at 13:53

answered Dec 15 '21 at 10:49

Jayvee

10,670
3
29
40

Getting error "Conversion failed when converting the nvarchar value to data type int. " – Dec 15 '21 at 10:53
now the items are in between single quotes, so the only field that could be causing the issue is NetAmount. Are the values on this field always numeric? – Jayvee Dec 15 '21 at 13:59
Now with your code I don't get any error, but also macros doesn't take any value, because when paste it to other cell it is empty – Dec 15 '21 at 14:11
But It cannot be correct, because previous with old code and with Itemnr1 I got numbers from sql – Dec 15 '21 at 14:23
I think the problem is that ItemNo is never equal to Itemnr2 due to non numeric characters in one or the other. To troubleshoot it you can run it on debug and check how the variable myquery looks right after it was populated; or just display a msg showing myquery. If you can't figure out the error post the result of the myquery variable here. – Jayvee Dec 15 '21 at 15:43
So in msg I got that myquery = Select -SUM([Net Amount]) AS [Result] FROM [Trans_Sales Entry] WHERE '[Item No_]' = '80502842 AND [Date] BETWEEN '2021-12-13' AND '2021-12-13' – Dec 16 '21 at 07:49
It looks correct, but I don't understand why it is not specific value, but code. – Dec 16 '21 at 07:52
Sorry, my bad in typing it is Select -SUM([Net Amount]) AS [Result] FROM [Trans_Sales Entry] WHERE '[Item No_]' = '80502842' AND [Date] BETWEEN '2021-12-13' AND '2021-12-13' – Dec 16 '21 at 08:42
well, that is a perfectly well formed query so if it is not returning results is because at least one of the conditions is not true – Jayvee Dec 16 '21 at 08:50
Maybe you know if it's possible to take not just specific value wtih SQL, but to take all table and paste it in excel sheet? – Dec 16 '21 at 09:34
Yes, that should be possible using a recordset, for instance. Please accept this answer as the syntax issue has been solved, and raise a new one if you need help with the VBA code to read all the table and paste it in excel. – Jayvee Dec 16 '21 at 09:43
1

Thanks for your time, I posted new one. – Dec 16 '21 at 10:03

Incorrect syntax near '' in vba code for sql

1 Answers1