ASP/VBScript query problem for an Access database... 
Author Message
 ASP/VBScript query problem for an Access database...

Can anyone help me with the query in the following snippet of code from my
ASP page:

<%
 Response.write("<select name=flightNum size=10>")

 if Len(sSelectedAirline) > 0 then
  dim objRSFlights
   objCommand.CommandText = "SELECT DISTINCT SummerFlights.FlightNum,
SummerFlights.DeptFrom, SummerFlights.DeptWebTime, SummerFlights.ArrAt,
SummerFlights.ArrWebTime, Airports.City as strDeptFrom, Airports.City as
strArrAt FROM SummerFlights, Airports WHERE SummerFlights.DeptFrom =
Airports.AirportCode AND AirlineCode = '" & sSelectedAirline & "'"
   objCommand.CommandType = adCmdText
   Set objRSFlights = objCommand.Execute

   while not objRSFlights.EOF
     Response.write("<option value=" & objRSFlights("FlightNum") & ">" &
objRSFlights("FlightNum") & ": &nbsp;" & objRSFlights("strDeptFrom") & " -
" & objRSFlights("DeptWebTime") & " to " & objRSFlights("strArrAt") & " - "
& objRSFlights("ArrWebTime") & "</option>")
    objRSFlights.MoveNext
   wend
 end if
 Response.write("</select>")
  Set objCommand = Nothing

%>

I am trying to display in the Select box a flight's flight number,
departure city, departure time, arrival city, and arrival time.

The following link shows my problem in the page's Select box <I am able to
determine the departure city but not the arrival city; obviously the
arrival city is not the same as the departure city>:
http://www.*-*-*.com/ ~lkkl/tpqueryimage.jpg

How must I write the query/subquery so that the ArrAt's Airport.City also
gets passed into strArrAt correctly as the DeptFrom does?

Thanks, any help is greatly appreciated!

Kevin



Fri, 06 Feb 2004 13:49:55 GMT  
 ASP/VBScript query problem for an Access database...
Sorry, I just don't have enough info to diagnose your problem. When you run
the query in Access, does it give you the proper result? Can you show us a
little sample data from each table in your query? And perhaps explain the
relationship between the tables?

One thing I would do is turn this query into a saved parameter query (call
it qFlightOptions), and create the option tags within the query itself. Like
this:

SELECT DISTINCT "<option value=" & t1.FlightNum & ">" &
t1.FlightNum & ": ?" & t2.City  & " - " &
t1.DeptWebTime & " to " & t2.City & " - " & t1.ArrWebTime
FROM SummerFlights As t1 INNER JOIN Airports As t2
ON t1.DeptFrom = t2.AirportCode
WHERE AirlineCode = [pAirlineCode]

In addition to making debugging easier, this will allow you to use GetString
to create your Options string in one fell swoop instead of using the slow
recordset loop. Like this:
 <%
  Response.write("<select name=flightNum size=10>")

  if Len(sSelectedAirline) > 0 then
   dim objRSFlights, sOptionList
   Set objRSFlights = server.createobject("ADODB.Recordset")
   objConnect.qFlightOptions sSelectedAirline, objRSFlights
   sOptionList = objRSFlights.GetString
    Response.Write sOptionList
    objRSFlights.Close
    Set objRSFlights =Nothing
End If

HTH,
Bob Barrows

PS. The biggest problem I now see is that you're using the same field in
Airports for both the From and To cities.


Quote:
> Can anyone help me with the query in the following snippet of code from my
> ASP page:

> <%
>  Response.write("<select name=flightNum size=10>")

>  if Len(sSelectedAirline) > 0 then
>   dim objRSFlights
>    objCommand.CommandText = "SELECT DISTINCT SummerFlights.FlightNum,
> SummerFlights.DeptFrom, SummerFlights.DeptWebTime, SummerFlights.ArrAt,
> SummerFlights.ArrWebTime, Airports.City as strDeptFrom, Airports.City as
> strArrAt FROM SummerFlights, Airports WHERE SummerFlights.DeptFrom =
> Airports.AirportCode AND AirlineCode = '" & sSelectedAirline & "'"
>    objCommand.CommandType = adCmdText
>    Set objRSFlights = objCommand.Execute

>    while not objRSFlights.EOF
>      Response.write("<option value=" & objRSFlights("FlightNum") & ">" &
> objRSFlights("FlightNum") & ": ?" & objRSFlights("strDeptFrom") & " -
> " & objRSFlights("DeptWebTime") & " to " & objRSFlights("strArrAt") & " -
"
> & objRSFlights("ArrWebTime") & "</option>")
>     objRSFlights.MoveNext
>    wend
>  end if
>  Response.write("</select>")
>   Set objCommand = Nothing

> %>

> I am trying to display in the Select box a flight's flight number,
> departure city, departure time, arrival city, and arrival time.

> The following link shows my problem in the page's Select box <I am able to
> determine the departure city but not the arrival city; obviously the
> arrival city is not the same as the departure city>:
> http://www.mindspring.com/~lkkl/tpqueryimage.jpg

> How must I write the query/subquery so that the ArrAt's Airport.City also
> gets passed into strArrAt correctly as the DeptFrom does?

> Thanks, any help is greatly appreciated!

> Kevin



Fri, 06 Feb 2004 19:38:21 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. How do I use asp files to query a secure Access Database w/IIS 4.0

2. Problem Using Access Query in ASP

3. Problem Using an Access Query from an ASP page

4. populating asp page with access database query ?

5. PROBLEM: Accessing database through Java-class in ASP (Jscript)

6. error doing a FILTER on an Access database via Asp/VBScript and ADO

7. error doing a FILTER on an Access database via Asp/VBScript and ADO

8. accessing linked fields in databases in ASP/VBScript

9. lame newbie question: ASP, VBScript and Access database

10. Basic database access using asp and vbscript

11. problems updating an Access database using VBScript

12. Problem Using Access Query in ASP

 

 
Powered by phpBB® Forum Software