0

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")
Community
  • 1
  • 1
slaterino
  • 11
  • 1
  • 2
  • 5
    Please use parameters instead of concatenating SQL query text and read about SQL Injection. – Lukasz Szozda Aug 25 '17 at 13:26
  • 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 Aug 25 '17 at 13:29
  • 3
    **[Parameterized Queries](http://use-the-index-luke.com/sql/where-clause/bind-parameters)** – Lukasz Szozda Aug 25 '17 at 13:30
  • 1
    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](https://stackoverflow.com/a/37565683/1260204) 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 Aug 25 '17 at 13:30
  • What is the value of `iMainContact` when the error occurs? – Tab Alleman Aug 25 '17 at 13:39
  • Do you have apostrophes around your string values? – Jacob H Aug 25 '17 at 14:19
  • Thanks for all your help with this. I am looking into Parameterized Queries at the moment. However, I have only just taken over this site from someone else and it's a huge site so the migration to using parameters will be something we need to work out at a later date. In regards to your help though. The asp code is just one line that follows onto a second line. I have tried breaking it and using the code like suggested but it hasn't helped. Also, it is an integer so should be fine without quotes. – slaterino Aug 28 '17 at 09:11
  • Hey @JacobH are there any particular values that should have apostrophes around them? I've copied the relevant code above. Should any of those values have apostrophes around them? – slaterino Aug 28 '17 at 09:14
  • @TabAlleman The problem occurs when iMainContact is 19438. Not sure if that's helpful or not?! – slaterino Aug 28 '17 at 09:32

1 Answers1

0

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

Josh Montgomery
  • 882
  • 5
  • 10
  • Hey Josh, thanks for the help. I might have confused with my first post, but essentially the RecordSet code you just posted is first, then the iMainContact variable is defined and then there's the SQL query which has the error message. That's the correct order isn't it? – slaterino Aug 28 '17 at 09:16
  • are you assigning a value to the variable iMainContact? what do you get when you write out the sql to the screen? – Josh Montgomery Aug 30 '17 at 15:53