Building an Internet Database Drilldown Application, Part 5
|Roger Drye||Directed Minds|
|Homepage: www.directedminds.com||Email: firstname.lastname@example.org|
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|
|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:
route=Request.QueryString("BusRouteNum") ' request that value passed from the form and store it in a variable
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
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
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 append 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 & " "
' Now move down the ladder to SBO name and determine
If a value was sent from the
' and so on ...
' and so on !
' then finish it up with
a nice ORDER BY
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|
|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 ...
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.