To read the article online, visit http://www.4GuysFromRolla.com/webtech/071399-1.shtml

Building an Internet Database Drilldown Application, Part 4

  • Read Building an Internet Database Drilldown Application, Part 1
  • Read Building an Internet Database Drilldown Application, Part 2
  • Read Building an Internet Database Drilldown Application, Part 3
  • Read Building an Internet Database Drilldown Application, Part 5

  • Roger Drye Directed Minds
    Homepage: www.directedminds.com Email:  info@directedminds.com

    Creating a query by form ...

    Let's say you want to build a query form like this to quickly find a bus route. Once the route is found. the user can drill down into runs, stops, students, etc. The idea is that the user can select categories from one or all of the dropdown boxes and these will be use to build a query that displays results on a separate page.

    Bus Route Quick Find
    Route  
    SBO  
    School Year  
    Trans Type  

    For example, let's say the user makes the following selection from the query form:

    Bus Route Quick Find
    Route  
    SBO  
    School Year  
    Trans Type  

    The resultant page will look something like this:


    Your Requested Query

    Route

    SBO School Year Vehicle Type Trans Type Description Comments Fund Source
    * SBO 1 1998-1999 * Contract * * *

    Results: 60 records found

      ID Route SBO School Year Vehicle Type Trans Type Description Comments Fund Source
    Route 9973 1 SBO 1 1998-1999 Bus/Coach Contract Daily Commuting - Zone A ALIBUS s.n.c. MDA412-96-D-0014 CLIN-0261AD
    Route 9968 2 SBO 1 1998-1999 Bus/Coach Contract Daily Commuting - Zone A ALIBUS s.n.c. MDA412-96-D-0014 CLIN-0201AD
    Route 9974 3 SBO 1 1998-1999 Bus/Coach Contract Daily Commuting - Zone A ALIBUS s.n.c. MDA412-96-D-0014 CLIN-0201AD
    Route 9996 18 SBO 1 1998-1999 Bus/Coach Contract Daily Commuting - A ALIBUS s.n.c. MDA412-96-D-0014 CLIN-0201AD
    Route 9997 19 SBO 1 1998-1999 Bus/Coach Contract Daily Commuting - Zone A ALIBUS s.n.c. MDA412-96-D-0014 CLIN-0201AD
    Route 10012 20 SBO 1 1998-1999 Bus/Coach Contract Daily Commuting - Zone A ALIBUS s.n.c. MDA412-96-D-0014 CLIN-0201AD
    ... and so on until you get to 60 records!

    You'll notice that the result page displays all records that match the query, and gives the user the option of picking a route and drilling down further into it ( the hyperlinked Route column ). All of this page is generated dynamically from the query results.

    Before we can take a look at the results page though, we have to understand how we built the query form and filled it with data:

    Bus Route Quick Find
    Route  
    SBO  
    School Year  
    Trans Type  

    <!--#include file="include/security.inc"-->
    <
    !-- #include file="include/top.inc" -->
    <
    !--#include file="include/adovbs.inc"-->

    <%
    ' the tables that will be queried to populate the dropdown boxes
    vtables = Array("t_BusRoute", "t_SBOList", "t_SchoolYrCode", "t_TransTypeCd")

    ' fields that will be used to populate the dropdown boxes
    vfields = Array("BusRouteNum", "SBOName", "SchYear", "TransTypeDesc")

    ' captions for the dropdown box description
    vcaption = Array("Route", "SBO", "School Year", "Trans Type")

    dbConnect="DSN=myDSN;uid=something;pwd=whatever&quo t;

    %>
    <
    table>
           <tr>
              <td>Bus Route Quick Find </td>
               ' send the query results to this file. GET or POST can be used.
              <td><form method=
    "get" action="BusRouteQFindResults.asp"></td>
         </tr>

    <%

    Set rstemp = Server.CreateObject("adodb.RecordSet")

    ' now we start building our 4 queries and populating the dropdown boxes
    For i = 0 to UBound(vtables)
         
    ' i.e. the first select statement will be: SELECT DISTINCT BusRouteNum FROM t_BusRoute ORDER BY BusRouteNum;
          mySQL =
    "SELECT DISTINCT " & vfields(i) & " FROM " & vtables(i) & " ORDER BY " & vfields(i) & ";"
          rstemp.Open mySQL, dbConnect, adopenstatic
          response.write("<tr>")
          tempReturn = rstemp(0) ' store the returned column value
          response.write("<td>")
         
    response.write(vcaption(i)) ' write the caption
          response.write("</td>")
          response.write("<td>")
          response.write("<select name=""" & vfields(i) & """>"
    ' write the select name     
          While Not rstemp.EOF
    ' move through the recordset and populate the option values
                  
    ' replace single quote with double quote so that when this value is sent to database later it won't cause an error
                  
    myReturn = replace(tempReturn, "'","''")  ' type this as: replace(tempReturn, "'","''")
                   response.write("<option value = """)
                   response.write(myReturn)
                   response.write(""" >")
                   response.write(myReturn)
                   response.write(
    "</option>")
                   rstemp.MoveNext
          Wend
          response.write(
    "</select>")
          response.write(
    "</td>")
          response.write(
    "</tr>")
          rstemp.Close

    Next ' move on to the next query and dropdown box

    %>

         <tr>
               <td><input type=
    "submit" value="Search">
                     <input type=
    "reset" value=" Clear "
    ></td>
         </tr>
    </table>

    <%

    Set rstemp=Nothing
    dbConnect.Close
    Set
    dbConnect=Nothing

    So now we have populated our dropdown boxes from our database. Remember, the idea here is to make the HTML maintenance free. By modifying the database, we automatically update the values in our dropdown boxes.

    Next ... building the SQL query from the items selected in our drop down boxes ...


  • Read Building an Internet Database Drilldown Application, Part 1
  • Read Building an Internet Database Drilldown Application, Part 2
  • Read Building an Internet Database Drilldown Application, Part 3
  • Read Building an Internet Database Drilldown Application, Part 5


  • Directed Minds is a web development company specializing in hosting and developing ASP, database and ecommerce solutions for large and small organizations.

    Roger Drye is the owner of  Directed Minds. He has been developing Internet solutions for small and large companies and government entities for many years.


    Article Information
    Article Title: Building an Internet Database Drilldown Application, Part 4
    Article Author: Roger Drye
    Published Date: Tuesday, July 13, 1999
    Article URL: http://www.4GuysFromRolla.com/webtech/071399-1.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers