[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '='

Question

I have started getting an error on a page and am really struggling to work out the cause of this. I have searched on Stack Overflow for this same error message and found some people that got it also, but there solutions were very different to mine. This is the error message I'm getting:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '='.

/test-page.asp, line 97

This related to this line on that .asp page:

set rsMainContact=oConn.execute 
    ("SELECT * FROM tbl_individual WHERE individual_id="&iMainContact)

Now the strange thing about this is that the page is actually bringing up the correct data, it's getting all the data it needs from that table. Which is why I don't understand why there's a problem.

Also, when I run the query using the "SELECT * FROM tbl_individual WHERE individual_id="&iMainContact" statement in MSSQL, replacing &iMainContact for an actual variable, it works absolutely fine.

Does anyone have any idea why this error might be occurring?

In case you want to know where the various variables come from then this is also some relevant code from the page:

SET rsOrganisationPendingOrganic=Server.CreateObject("ADODB.RecordSet")
rsOrganisationPendingOrganic.CursorType=3
rsOrganisationPendingOrganic.Open sSQL, oConn
iOrganisationPendingOrganicCount=rsOrganisationPendingOrganic.RecordCount

iMainContact=rsOrganisationPendingOrganic("organisation_maincontact")

Please use parameters instead of concatenating SQL query text and read about SQL Injection. — Lukasz Szozda
Hey @lad2025, thanks for that. What do you mean by "parameters instead of concatenating SQL query text". Do you mean using a different way of referencing the variable other than "&iMainContact"? — slaterino
Is that line of code on your asp page 2 lines like you have here or is it one line and formatting in the question breaks it up over 2? If it is 2 lines you need to use vbscript string concatenation to join them. See Breaking a String Across Multiple Lines Also what type is individual_id? Is it a string or an int/number? If its the latter then its ok, the former and you forgot your quotes around the value. Really though you need to use parameters unless you want to deal with possible sql injection attacks and escaping all string values manually — Igor

Josh Montgomery Josh Montgomery · Accepted Answer · 2017-08-25T19:56:53

if the following code is run BEFORE the error occurs, you most likely have no value for iMainContact:

SET rsOrganisationPendingOrganic=Server.CreateObject("ADODB.RecordSet") 
rsOrganisationPendingOrganic.CursorType=3

rsOrganisationPendingOrganic.Open sSQL, oConn
iOrganisationPendingOrganicCount=rsOrganisationPendingOrganic.RecordCount

iMainContact=rsOrganisationPendingOrganic("organisation_maincontact")

you can prove this by writing out the sql to the screen before executing the failing sql:

Response.Write "SELECT * FROM tbl_individual WHERE individual_id=" & iMainContact
Response.End

I also agree 1000% with lad2025, use parameterized queries to guard against sql injection

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '='

1 Answers