When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Sunday, July 18, 1999

Building an Internet Database Drilldown Application, Part 5

  • 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 4
  • Read Building an Internet Database Drilldown Application, Part 6

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

    Building an SQL query from dropdown boxes ...

    ... or something like that. Basically, If you remember we had dropdown boxes for selecting which records we wanted to view, and then drill down into. We had something like this:

    Bus Route Quick Find
    Route   Name: BusRouteNum
    SBO   Name: SBOName
    School Year   Name: SchYear
    Trans Type   Name: TransTypeDesc
    form method="get" action="BusRouteQFindResults.asp">

    The trick we have to work out now is how to build an SQL query, based on the user's selection. The user may select none, 1, or more drop down categories. We take these values, passed when we click the 'Search' button, and sEnd them to our new page for processing (BusRouteQFindResults.asp).

    First step is to store these values is variables:

    BUSROUTEQFINDRESULTS.ASP

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

    <%
    route=Request.QueryString("BusRouteNum") ' request that value passed from the form and store it in a variable
    sbo=Request.QueryString("SBOName")
    sy=Request.QueryString("SchYear")
    tt=Request.QueryString("TransTypeDesc")

    Next, we can start to build the SQL query. Don't let the query scare you. It was built with Microsoft Access. Once you build a query in Access, all you have to do is go to  the SQL view in design mode, and it has the SQL version of the query. Then it is just a matter of converting the Access SQL query into a string in your ASP pages.

    mySQL = "SELECT DISTINCTROW t_BusRoute.UserId, "
    mySQL = mySQL & "t_BusRoute.BusRouteId_bt, "

    mySQL = mySQL & "t_BusRoute.BusRouteNum, "
    mySQL = mySQL & "t_BusRoute.BusRouteType, "
    mySQL = mySQL & "t_SboList.SboName, "
    mySQL = mySQL & "t_SchoolYrCode.SchYear, "
    mySQL = mySQL & "t_VehicleType.VehTypeDesc, "
    mySQL = mySQL & "t_TransTypeCd.TransTypeDesc, "
    mySQL = mySQL & "t_BusRoute.BusRouteDesc, "
    mySQL = mySQL & "t_BusRoute.BusRouteCmt, "
    mySQL = mySQL & "t_BusRoute.BusRtFundNum, "
    mySQL = mySQL & "t_BusRoute.BusRouteId "
    mySQL = mySQL & "FROM (((t_BusRoute "
    mySQL = mySQL & "INNER JOIN t_SboList "
    mySQL = mySQL & "ON t_BusRoute.UserId = t_SboList.UserId) "
    mySQL = mySQL & "INNER JOIN t_TransTypeCd "
    mySQL = mySQL & "ON t_BusRoute.TransTypeCd = t_TransTypeCd.TransTypeCd) "
    mySQL = mySQL & "INNER JOIN t_VehicleType "
    mySQL = mySQL & "ON t_BusRoute.VehTypeCode = t_VehicleType.VehTypeCode) "
    mySQL = mySQL & "INNER JOIN t_SchoolYrCode "
    mySQL = mySQL & "ON t_BusRoute.SchYearAbbr = t_SchoolYrCode.SchYearAbbr "

    What you should notice at this point is that there is no 'WHERE' clause. That's because we have to build this clause based on the values passed from our query form. Let's look at that next.

    routetemp = "" ' clear the variable that will be used to determine whether route information was passed to this page
    If trim(route) <> "" then ' If any route value was passed from the form then
       ' start building the WHERE clause for route number and app
    end to our mySQL string
         mySQL = mySQL & " WHERE t_BusRoute.BusRouteNum"
       
    ' store a value in routetemp to show that it isn't empty.
    If it isn't empty then there must have been route info passed
         routetemp = mySQL
       
    '  finish building the route section of the WHERE clause

         mySQL = mySQL & " = " & route & " "
    End If

    ' Now move down the ladder to SBO name and determine If a value was sent from the previous page
    sbotemp = ""  ' again, clear the variable for later use
    If trim(sbo) <> "" then ' If any SBO value was passed from the form then
         ' was there ROUTE information? If
    not then we start the WHERE clause now.
        
    If trim(routetemp) = "" then
              mySQL = mySQL & " WHERE t_SboList.SboName"
              sbotemp = mySQL
              mySQL = mySQL & " = '" & sbo & "' "
         End If

        
    '
    If there was ROUTE information, then the WHERE clause has already be started, so we need to append to it
        
    If trim(routetemp) <> "" then
              mySQL = mySQL & " AND t_SboList.SboName"
              sbotemp = mySQL
              mySQL = mySQL & " = '" & sbo & "' "
         End If
    End If

    ' and so on ...
    sytemp = ""
    If trim(sy) <> "" then
         If trim(sbotemp) = "" and trim(routetemp) = "" then
    ' there are no SBO or ROUTE values? Then start the WHERE clause.
              mySQL = mySQL & " WHERE t_SchoolYrCode.SchYear"
              sytemp = mySQL
              mySQL = mySQL & " = '" & sy & "' "
         End If
         If trim(sbotemp) <> "" or trim(routetemp) <> "" then
    ' there is either an SBO or ROUTE value, or both, so append to the clause
              mySQL = mySQL & " AND t_SchoolYrCode.SchYear"
              sytemp = mySQL
              mySQL = mySQL & " = '" & sy & "' "
         End If
    End If

    ' and so on !
    tttemp = ""
    If trim(tt) <> "" then
         If trim(sytemp) = "" and trim(sbotemp) = "" and trim(routetemp) = "" then
              mySQL = mySQL & " WHERE t_TransTypeCd.TransTypeDesc"
              tttemp = mySQL
              mySQL = mySQL & " = '" & tt & "' "
         End If
         If trim(sytemp) <> "" or trim(sbotemp) <> "" or trim(routetemp) <> "" then
              mySQL =mySQL & " AND t_TransTypeCd.TransTypeDesc"
              tttemp = mySQL
              mySQL = mySQL & " = '" & tt & "' "
         End If
    End If

    ' then finish it up with a nice ORDER BY statement
    mySQL = mySQL & "ORDER BY t_BusRoute.BusRouteNum;"

    Hopefully, you see how the process builds. In my query form I actually had 8 dropdown boxes. You can see how the code can start to stack up.

    If you remember the results page, the table had a link in which you could view a particular record:

      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

    Next ... build the results form and dynamically generate the drilldown links ...


  • 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 4
  • Read Building an Internet Database Drilldown Application, Part 6


  • 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.


    ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article