criteria= trim(request.QueryString("name"))


sqlstring="SELECT customer.customer_id from flight inner join booking on flight.flight_id=booking.flight_id inner join customer ON booking.customer_id = customer.customer_id WHERE flight.flight_name LIKE '%criteria%'"


set objrsflight = server.CreateObject("ADODB.Recordset")
objrsflight.Open sqlstring, strconnect , adOpenDynamic , adLockOptimistic , adCmdText

objrsflight.Close
set objrsflight = nothing

i tried to run that code in asp with the following tables in sql server 2000

flight booking customer
flight_id flight_id customer_id
flight_name customer_id customer_name
etc. etc. etc.

the sql runs fine but when i use recordcount or pagecount i get -1

but there is no error message, but when i try to retrive the value using

response.Write objrsflight("customer_name") i get

ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

can anyone help i've noticed that when i remove the where clause this works then i have to filter the recordset again, i would have prefferred to do it in the sql statement itself

can anyone help?

sorry for the table, here goes

flight
flight_id
flight_name
etc.

customer
customer_id
customer_fname
etc.

booking
flight_id
customer_id
etc.

Try this:

criteria= trim(request.QueryString("name"))


sqlstring="SELECT customer.customer_id from flight inner join booking on flight.flight_id=booking.flight_id inner join customer ON booking.customer_id = customer.customer_id WHERE flight.flight_name LIKE '%" & criteria & "%'"

Also, you are only pulling back customer.customer_id so when you try to pull customer.customer_name, its not in the recordset hence the error. Add the field to your select statement.

HTH
DMWCincy

i already tried that one

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near 'dsf'.

yeah your right for the customer_id i didn't notice while changing the values

if i remove the where clause it works fine but then i will have to filter it again, also i'll get a very large recordset with all those columns that i won't use

still i get the same error if trying to retrieve any field when i use the where cause, could there be any possibility of conflicts with the where clause when used in asp?

the problem is you are only asking for one field in your select statement
if you want anything other then customer_id you need to change your select statement to include these fields eg...
Please also note the recordcount is going to equal -1 if you are not using the correct cursortypes or the correct cursorlocations for your recordset.

do a response.write(sqlstring) and let us see what the string looks like with the where cause that you are trying to send to SQL.

when i use

sqlstring="SELECT customer.* from flight inner join booking on flight.flight_id=booking.flight_id inner join customer ON booking.customer_id = customer.customer_id WHERE flight.flight_name LIKE '%"& criteria &"%'"

i get

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near 'dsf'.

when i use

sqlstring="SELECT customer.* from flight inner join booking on flight.flight_id=booking.flight_id inner join customer ON booking.customer_id = customer.customer_id WHERE flight.flight_name LIKE '%criteria%'"

i get

SELECT customer.* from flight inner join booking on flight.flight_id=booking.flight_id inner join customer ON booking.customer_id = customer.customer_id WHERE flight.flight_name LIKE '%criteria%'

i tried to remove the where clause, the sql works fine but it returns me all the columns and i have to filter it then

for the second sql when i replace '%criteria%' with a flight name i get a result, this means that the value '%"& criteria &"%' is not being passed to the sql, and i can't see any error in '%"& criteria &"%'

You have

'%"& criteria &"%'

You are missing the double quote at the end.

Do this:
'%"& criteria &"%'"

Just for readability I replace ' and " with words to help out a bit.
<single quote>%<double quote> & criteria & <double quote>%<single quote><double quote>

HTH

'%"& criteria &"%' was just meant to be an extract, yes i have a double quote at the end of the sql, the thing that bugs me is that it keeps telling me

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near 'dsf'.

when i run the sql

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near 'dsf'.

'dsf' being the string passed in criteria

as someone said about. do a response.write on your query string before you execute it. it looks to me like when you are adding your string is something "unsual" is happening which is causing a malformed piece of sql to be sent to the database.

I could be that the flight name contains invalid characters such as comma's or single quotes or something similar.

Or it could be that you are using something like this....
and doing some sort of manual find and replace and accidently replacing the wrong section so attaching your entire code might help us figure this out....

<%Option Explicit%>
<!--
Author: Michael Bruce
Cohort:BIS03FT
Project:AirM Online Travel
-->
<!-- #INCLUDE FILE="..\datastore.asp" -->
<!-- METADATA TYPE="typelib" FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->
<%
dim criteria,sqlstring
Dim objrsflight,i,y
'Extract the querystring value from the url supplied
criteria= trim(request.QueryString("name"))


sqlstring = "SELECT customer.* from flight inner join booking on flight.flight_id=booking.flight_id inner join customer ON booking.customer_id = customer.customer_id WHERE flight.flight_name LIKE '%"& criteria &"%'"

set objrsflight = server.CreateObject("ADODB.Recordset")
objrsflight.Open sqlstring, strconnect , adOpenDynamic , adLockOptimistic , adCmdText


response.Write (sqlstring)


objrsflight.Close
set objrsflight = nothing
%>

when i try to run that code i get:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near 'dsf'.
/airm/administrator/listpassenger.asp, line 19

i'm using microsoft sql server 2000

the only part that is causing problem is the where clause