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

MikeFantana · 11-09-2014 9:27pm #1

Hi All,

Im getting an error in my code (Classic ASP...I know, get wth the times)...

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

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'ORDER'.

/searchresults.asp, line 60

The line of code in question is as follows...

sSQL = sSQL & " Where " & conditions & " ORDER BY ConBusName"

Any ideas. I have tried every combination i can think of...

Beano · 11-09-2014 9:49pm

what is the value of sSQL after that line is executed? In other words what sql is actually being executed?

MikeFantana · 11-09-2014 9:53pm

Beano wrote: »

what is the value of sSQL after that line is executed? In other words what sql is actually being executed?

Here is the full script

Dim rsSearchRes
Dim rsSearchRes_cmd
Dim rsSearchRes_numRows

Dim sSQL

Dim conditions

sSQL = "SELECT * FROM dbo.qrySearchResults "

conditions = ""

If Param1 <> "" Then

conditions = "CountyID=" & MMColParam1

End If

If Param2<> "" Then

If conditions <> "" Then

conditions = conditions & " AND "

End If

conditions = conditions & "ConTypeID=" & MMColParam2

End If

If Param3<> "" Then

If conditions <> "" Then

conditions = conditions & " OR "

End If

conditions = conditions & "ConBusName like '%" & MMColParam3 & "%'"

End If

sSQL = sSQL & " Where " & conditions &

Set rsSearchRes_cmd = Server.CreateObject ("ADODB.Command")

rsSearchRes_cmd.ActiveConnection = abc_connection_STRING

rsSearchRes_cmd.CommandText = sSQL

MikeFantana · 11-09-2014 9:55pm

Beano wrote: »

what is the value of sSQL after that line is executed? In other words what sql is actually being executed?

Sorry lft some of the script out

Dim rsSearchRes
Dim rsSearchRes_cmd
Dim rsSearchRes_numRows

Dim sSQL

Dim conditions

sSQL = "SELECT * FROM dbo.qrySearchResults "

conditions = ""

If Param1 <> "" Then

conditions = "CountyID=" & MMColParam1

End If

If Param2<> "" Then

If conditions <> "" Then

conditions = conditions & " AND "

End If

conditions = conditions & "ConTypeID=" & MMColParam2

End If

If Param3<> "" Then

If conditions <> "" Then

conditions = conditions & " OR "

End If

conditions = conditions & "ConBusName like '%" & MMColParam3 & "%'"

End If

sSQL = sSQL & " Where " & conditions & " ORDER BY ConBusName"

Set rsSearchRes_cmd = Server.CreateObject ("ADODB.Command")

rsSearchRes_cmd.ActiveConnection = MM_BWCONN_STRING

rsSearchRes_cmd.CommandText = sSQL

Set rsSearchRes = rsSearchRes_cmd.Execute

rsSearchRes_numRows = 0

MikeFantana · 11-09-2014 9:57pm

Beano wrote: »

what is the value of sSQL after that line is executed? In other words what sql is actually being executed?

Sorry lft some of the script out

Dim rsSearchRes
Dim rsSearchRes_cmd
Dim rsSearchRes_numRows

Dim sSQL

Dim conditions

sSQL = "SELECT * FROM dbo.qrySearchResults "

conditions = ""

If Param1 <> "" Then

conditions = "CountyID=" & MMColParam1

End If

If Param2<> "" Then

If conditions <> "" Then

conditions = conditions & " AND "

End If

conditions = conditions & "ConTypeID=" & MMColParam2

End If

If Param3<> "" Then

If conditions <> "" Then

conditions = conditions & " OR "

End If

conditions = conditions & "ConBusName like '%" & MMColParam3 & "%'"

End If

sSQL = sSQL & " Where " & conditions & " ORDER BY ConBusName"

Set rsSearchRes_cmd = Server.CreateObject ("ADODB.Command")

rsSearchRes_cmd.ActiveConnection = abc_connection_STRING

rsSearchRes_cmd.CommandText = sSQL

Set rsSearchRes = rsSearchRes_cmd.Execute

rsSearchRes_numRows = 0

Beano · 11-09-2014 9:57pm

Yeah, but what sql is actually being executed? this is obviously dependent on what parameters are passed so giving me the script is not enough

MikeFantana · 11-09-2014 10:08pm

Beano wrote: »

Yeah, but what sql is actually being executed? this is obviously dependent on what parameters are passed so giving me the script is not enough

Sorry. OK there are 3 parameters being passed from a form. Param 1 and 2 are integers and the third is a text field. Is this what you are looking for Beano?

Beano · 11-09-2014 10:26pm

MikeFantana wrote: »

Sorry. OK there are 3 parameters being passed from a form. Param 1 and 2 are integers and the third is a text field. Is this what you are looking for Beano?

I'm presuming you are quite new to the whole development thing?

What i need to know is what value is in the sSQL variable when this line is executed :

Set rsSearchRes = rsSearchRes_cmd.Execute

The error is in the sql you are trying to execute. To find the problem we need to know what that sql is.

MikeFantana · 11-09-2014 10:35pm

Beano wrote: »

I'm presuming you are quite new to the whole development thing?

What i need to know is what value is in the sSQL variable when this line is executed :

Set rsSearchRes = rsSearchRes_cmd.Execute

The error is in the sql you are trying to execute. To find the problem we need to know what that sql is.

Damn my cover is blown

Yeh newbie alright.

The statement

Select * from tblABC where (CountyID = '1') and (ConTypeID = '7') and (ConBusName LIKE 'ABC')

Beano · 11-09-2014 10:38pm

The error says :

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'ORDER'.

Are you sure that is the full sql statement being executed? There is no Order By clause.

Beano · 11-09-2014 10:40pm

that query you posted also reads from a different table to the script.

MikeFantana · 11-09-2014 10:41pm

Beano wrote: »

The error says :

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'ORDER'.

Are you sure that is the full sql statement being executed? There is no Order By clause.

Actually just through trial and error I have adjusted the script text

sSQL = sSQL & " Where " = conditions & " ORDER BY ConBusName"

and now Im getting this error

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

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

/searchresults.asp, line 60

which refers to this line
Set rsSearchRes = rsSearchRes_cmd.Execute

MikeFantana · 11-09-2014 10:43pm

Beano wrote: »

that query you posted also reads from a different table to the script.

Sorry I hand wrote that

Select * FROM dbo.qrySearchResults where (CountyID = '1') and (ConTypeID = '7') and (ConBusName LIKE 'ABC')

Beano · 11-09-2014 10:45pm

MikeFantana wrote: »

Sorry I hand wrote that

Select * FROM dbo.qrySearchResults where (CountyID = '1') and (ConTypeID = '7') and (ConBusName LIKE 'ABC')

i didnt ask you to hand write what you think the query will be. I asked you to post the actual query. Its really simple.

I'm done here. I've given you a couple of chances to post the relevant information.

amen · 11-09-2014 10:59pm

Op, a few thins
1: the SQL you posted is not the same as the SQL your code will generate as on has a third and whereas the other has an or
2: the logic of the Code statement with the use of or is unclear. Use brackets to group logical statements
3: turn on SQL profiler to see the exact SQL string sent to SQL server or just put a watch on sSql and post the value here.

One of the most valuable ways to debug SQL is to take the exact statement and run in the database.

MikeFantana · 11-09-2014 11:01pm

Beano wrote: »

i didnt ask you to hand write what you think the query will be. I asked you to post the actual query. Its really simple.

I'm done here. I've given you a couple of chances to post the relevant information.

No need to be like that. For what its worth, managed to set up my profiler

ran a trace and it came back

Select * from 0

Mort5000 · 12-09-2014 5:19am

MikeFantana wrote: »

sSQL = sSQL & " Where " = conditions & " ORDER BY ConBusName"

Looks like it is evaluating the '='.

Should this be

sSQL = sSQL & " Where " & conditions & " ORDER BY ConBusName"

BoardsMember · 12-09-2014 8:59am

This "Select * from tblABC where (CountyID = '1') and (ConTypeID = '7') and (ConBusName LIKE 'ABC')" cannot be the script that is being executed as it would not be responsible for this "Select * from 0"

When using like, you need to use "%" as the wildcard, otherwise it just works like "=" operator. So like 'ABC%' would include anything starting with ABC, '%ABC' includes anything ending in ABC, and '%ABC%' is anything with ABC as part of the string.

put Response.Write sSQL in before these lines :

Set rsSearchRes_cmd = Server.CreateObject ("ADODB.Command")

rsSearchRes_cmd.ActiveConnection = abc_connection_STRING

rsSearchRes_cmd.CommandText = sSQL

It's much quicker than running a trace.

MikeFantana · 12-09-2014 9:53am

Hi,

Yeh it would appear the problem is indeed not with the Order By but with the condition I'm trying to pull in...See the printed SQL statement. Much quicker than running traces (thanks for the tip).

SELECT * FROM dbo.qrySearchResults Where ORDER BY ConBusName

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'ORDER'.
/searchresults.asp, line 64

Beano · 12-09-2014 10:01am

you dont need the WHERE if you have no conditions. So if you have no conditions dont add WHERE to your sql string.

MikeFantana · 12-09-2014 10:32am

The thing is I am trying to pass through up to 3 parameters so I need the WHERE in there

Beano · 12-09-2014 10:36am

MikeFantana wrote: »

The thing is I am trying to pass through up to 3 parameters so I need the WHERE in there

but not if there is nothing in those three parameters.

Beano · 12-09-2014 10:38am

So something like

If conditions <> "" Then

sSQL = sSQL & " Where " & conditions & " ORDER BY ConBusName"

else

sSQL = sSQL & " ORDER BY ConBusName"

end if

Beano · 12-09-2014 10:38am

Your where clause also mixes OR and AND. I can guarantee that there will be situations where that WHERE clause will not do what you think it will do.

rozeboosje · 12-09-2014 10:46am

Mixing ORs and ANDs? All ye who enter there, abandon all hope.

MikeFantana · 12-09-2014 11:18am

Thanks Beano for all your help(and everyone else too for that matter). Its shifted the error message. Hurray. But its not calling in my parameters. So there must be something up with the way its calling in the parameters...:( agghhhh!!!!!

If i look at my browser hyper link, it shows the following
/mysite/searchresults.asp?CountyID=2&ConTypeID=7&ConBusName=abc

Beano · 12-09-2014 11:54am

cant really help you with that. Classic ASP is not my thing.

Berserker · 12-09-2014 11:59am

MikeFantana wrote: »

Thanks Beano for all your help(and everyone else too for that matter). Its shifted the error message. Hurray. But its not calling in my parameters. So there must be something up with the way its calling in the parameters...:( agghhhh!!!!!

If i look at my browser hyper link, it shows the following
/mysite/searchresults.asp?CountyID=2&ConTypeID=7&ConBusName=abc

A long time since I have dabbled in Classic ASP but by the sounds of things you are not getting the parameters from your querystring, which you are then trying to pass into the SQL query you use to get the search results.

So, the code below will give you the 'CountyID' value from the querystring and you can pass that into your sql string :

dim countyID

countyID = Request.QueryString("CountyID")

Graham · 12-09-2014 12:11pm

Berserker wrote: »

A long time since I have dabbled in Classic ASP but by the sounds of things you are not getting the parameters from your querystring, which you are then trying to pass into the SQL query you use to get the search results.

So, the code below will give you the 'CountyID' value from the querystring and you can pass that into your sql string :

dim countyID

countyID = Request.QueryString("CountyID")

To follow on from Berserkers suggestion, if in doubt, response.write it out.

So after

countyID = Request.QueryString("CountyID")

do a

response.write countyID

BoardsMember · 12-09-2014 1:50pm

You also need to read up on SQL injection if passing in non-numeric querystring parameters. Ideally you would have some sort of sanitisation of querystring parameters based on expected type. For example, if you have an int value in CountyId in the querystring you might have a function called QSInt which takes sTargetName as an input parameter and goes something like this:

if isnumeric(QSValue(sTargetName)) and trim(QSValue(sTargetName)) <> "" then

QSInt = cint(QSValue(sTargetName))

else

QSInt = -1

end if

and then your sSQL build up would have

if CountyId > 0 then sSQL = sSQL & " and CountyId=" & CountyId

Beano · 12-09-2014 1:55pm

BoardsMember wrote: »

You also need to read up on SQL injection if passing in non-numeric querystring parameters. Ideally you would have some sort of sanitisation of querystring parameters based on expected type. For example, if you have an int value in CountyId in the querystring you might have a function called QSInt which takes sTargetName as an input parameter and goes something like this:

if isnumeric(QSValue(sTargetName)) and trim(QSValue(sTargetName)) <> "" then

QSInt = cint(QSValue(sTargetName))

else

QSInt = -1

end if

and then your sSQL build up would have

if CountyId > 0 then sSQL = sSQL & " and CountyId=" & CountyId

In fairness if you are concerned with sql injection (and you should be) then stored procs is the way to go. this whole idea of building up queries in code went out with the indians.

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

Comments