Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: SQL help in ASP

  1. #1
    Join Date
    Oct 2004
    Posts
    12

    Unanswered: SQL help in ASP

    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?

  2. #2
    Join Date
    Oct 2004
    Posts
    12
    sorry for the table, here goes

    flight
    flight_id
    flight_name
    etc.

    customer
    customer_id
    customer_fname
    etc.

    booking
    flight_id
    customer_id
    etc.

  3. #3
    Join Date
    May 2004
    Posts
    125
    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

  4. #4
    Join Date
    Oct 2004
    Posts
    12
    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

  5. #5
    Join Date
    Oct 2004
    Posts
    12
    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?

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    the problem is you are only asking for one field in your select statement
    Code:
    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%'"
    if you want anything other then customer_id you need to change your select statement to include these fields eg...
    Code:
    sqlstring="SELECT customer.customer_id, customer.customer_name 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%'"
    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.

  7. #7
    Join Date
    May 2004
    Posts
    125
    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.

  8. #8
    Join Date
    Oct 2004
    Posts
    12
    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

  9. #9
    Join Date
    Oct 2004
    Posts
    12
    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 &"%'

  10. #10
    Join Date
    May 2004
    Posts
    125
    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

  11. #11
    Join Date
    Oct 2004
    Posts
    12
    '%"& 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

  12. #12
    Join Date
    Oct 2004
    Posts
    12
    Microsoft OLE DB Provider for SQL Server (0x80040E14)
    Line 1: Incorrect syntax near 'dsf'.

    'dsf' being the string passed in criteria

  13. #13
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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....
    Code:
    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%'"
    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....

  14. #14
    Join Date
    Oct 2004
    Posts
    12
    <%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

  15. #15
    Join Date
    Oct 2004
    Posts
    12
    i'm using microsoft sql server 2000

    the only part that is causing problem is the where clause

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •