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

MikeFantana

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

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

MikeFantana

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

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

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

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

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

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

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

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

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

MikeFantana

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

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

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

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

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

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

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

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

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

MikeFantana

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

Beano

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

So something like

If conditions <> "" Then

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

else

sSQL = sSQL & " ORDER BY ConBusName"

end if

Beano

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

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

MikeFantana

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

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

Berserker

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

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

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

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.

BoardsMember

Beano wrote: »

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.

100% agree, but based on posts to date, I think we have to deal with what we're dealing with.

Beano

BoardsMember wrote: »

100% agree, but based on posts to date, I think we have to deal with what we're dealing with.

good point.

MikeFantana

Graham wrote: »

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

So after

countyID = Request.QueryString("CountyID")

do a

response.write countyID

Ok so i have added the dims

dim countyID
dim ConTypeID
dim ConBusName
countyID = Request.QueryString("CountyID")
response.write countyID
ConTypeID = Request.QueryString("ConTypeID")
response.write ConTypeID       
ConBusname = Request.QueryString("ConBusName")
response.write ConBusName

and it writes back the corerct parameters to screen,

I tried to add the Response.Querysting to my conditions but alas no luck, making a right mess of it.

sSQL = "SELECT * FROM dbo.qrySearchResults "

            conditions = ""

           

            If Param1 <> "" Then           

                conditions = "CountyID=" Request.QueryString ('& MMColParam1&')

            End If

Beano

once you have taken the values from the querystring into the variables then there is no need to used the querystring after that. you then use the variables are you were using them before.

can i ask what this is for? It seems a very trivial example so is this a college project or something?

Graham

I strongly suspect you're trying to edit dreamweavers autowaffle ASP code here OP.

If that's the case, I'd recommend you stop, start again with a nice clean hand-coded example, there's millions of good ones still floating about on the net. Do a search for "Classic ASP Query SLQ Database" or something along those lines, add a -.net if required.

BoardsMember

There is no Param1...Parameters are for SPs and the like, you are trying to write inline query

dim countyID
dim ConTypeID
dim ConBusName

countyID = Request.QueryString("CountyID")
ConTypeID = Request.QueryString("ConTypeID")
ConBusname = Request.QueryString("ConBusName")



sSQL = "SELECT * FROM dbo.qrySearchResults "
if countyID <> "" then sSQL = sSQL & " WHERE CountyId =" & countyID
if ConTypeID <> "" then sSQL = sSQL & " AND ConTypeID=" & ConTypeID

etc...

Obviously you must have one "WHERE" and the rest "AND"s. And you need to sanitise the querystring values, as already posted.