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

Building an Internet Database Drilldown Application, Part 3

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

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

    Building the data display page ... using GetRows and arrays

    To give you an idea of how the data will be viewed here is a sample of what the start page of our bus tracking system might look like:

    Summary Pages Detail Pages
    District Transportation Office Bus Routes
    School Bus Office Students
    Route
    Run

    Each school district has a transportation office.
            Each transportation office has a number of school bus offices.
                    Each school bus office is responsible for a number of routes.
                             Each route has a series of morning and afternoon runs.
                                      Each run has a number of stops.
                                               Each stop has a number of students.
                                                       Each student has a guardian.

    The summary pages show aggregate data for the requested level. Detail pages allow you to query by form to find your information and drilldown as necessary.

    Let's start with the District Transportation Office Summary. Here's the type of data that is displayed on this page:

    Basic DTO Information

    District SBOs Routes Runs Stops Miles Riders
    A 3 97 220 1443 6554 1805
    B 5 85 218 1231 4720 2373
    C 6 137 312 2109 8763 3813
    D 7 187 486 3032 14333 4705
    E 4 97 237 1583 4976 2471
    F 3 146 366 1972 6534 4092
    G 7 211 437 3506 11693 4641
     Totals
    Routes: 960 Runs: 2276 Stops: 14876 Miles: 57573 Riders: 23900

    Ridership and Bus Useage

    District Daily Pax Avg Pax / Run % Ridership Avg % of Bus Size Miles / Week
    A 3747 17 85.5 52 29536
    B 4778 21.9 46.3 64.5 23142
    C 7619 24.4 79.5 68 42943
    D 9436 19.6 83.3 61.7 69670
    E 4959 21.3 72.8 68.7 23899
    F 8227 22.5 82.9 92 32079
    G 9527 22.1 57.9 65 55264
     Totals
    Daily Pax: 48293

    Costs

    District Approx Daily Cost Approx Weekly Cost Daily Cost / Mile Daily Cost / Pax Cost Per Pax / Mile
    A 18492 87361 5.85 11.53 1.6
    B 19345 96071 8.06 12.42 1.33
    C 18560 92498 5.2 5.37 0.37
    D 29016 143290 4.25 7.58 0.52
    E 26349 130918 8.94 11.29 0.74
    F 18605 90971 4.35 6.28 0.57
    G 32000 152987 4.83 8.57 0.42
     Totals
    Daily Cost: 162367 Weekly Cost: 794096

    The focus at this point is to show how this page is built dynamically from an SQL query with ASP. This particular page is built without any SQL parameters or passed variables. In later examples we will look at how a page can be built dynamically with variables passed from another page.

    Let's get started. As mentioned before we have some files to include at the top of the page.

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

    'This is the page header. Most importantly it includes the statement: Response.Buffer = True
    <!--#INCLUDE file="include/top.inc"-->

    'The ADO constants used by this application
    <!--#INCLUDE file="include/adovbs.inc"-->

    Next, create arrays for the display of data. This is overkill for this page, but I used it to maintain a consistent technique for when the pages became more complex. It serves its purpose here because it introduces you to using arrays to build pages. Let's see how the Basic DTO Information HTML table is built.

    'Basic DTO Information Table
    vcaption1 = Array("District", "SBOs", "Routes", "Runs", "Stops", "Miles", "Riders")
    vshowfields1 = Array(0,1,2,3,4,5,6)
    ' what fields will be displayed (field names could be used here, instead of column numbers)

    The caption array stores the column headers for the table. The showfields array determines which fields of the returned recordset will be displayed and in what order. You will see that in use in just a minute.

    Now we build our SQL statement (for this example, retrieving only DTO Information):

    mySQL = "SELECT DISTINCTROW t_Dto.DistrictCode, "
    mySQL = mySQL & "t_Dto.Sbos, "
    mySQL = mySQL & "t_Dto.Routes, "
    mySQL = mySQL & "t_Dto.Runs, "
    mySQL = mySQL & "t_Dto.Stops, "
    mySQL = mySQL & "t_Dto.Miles, "
    mySQL = mySQL & "t_Dto.RegisteredRiders, "
    mySQL = mySQL & "FROM t_Dto "
    mySQL = mySQL & "ORDER BY t_Dto.DistrictCode
    ;"

    Next we create a DSN connection and retrieve the recordset. After doing some research I decided to use a DSN connection, primarily because it is easy to maintain, I had control of the server,  and I would have few concurrent users. To learn more about whether you should choose a DSN or DSN-less connection, see this article.

    Next was the decision whether to use or not to use session state for maintaining the database connection. My first thought was that with few concurrent users, using session variables to maintain the connection would not tax the server. This article describes the burden on the server  that can be created when many users maintain connection by use of session variables. In the end I decided not to use session objects (in fact this application does not make use of a global.asa), because connection state for this application is very easy to maintain. This application displays read only data. You simply open a connection, retrieve the recordset, store the data in arrays, write the page, and close the recordset and database connection. So, as stated, I decided that using session variables in this case added no benefit.

    So next we create the connection:

    dbConnect="DSN=myDSN;uid=something;pwd=whatever"

    set rstemp=Server.CreateObject("adodb.RecordSet")
    rstemp.open mySQL, dbConnect, adopenstatic

    Then I want to know how many records are being returned by this query, so:

    NumOfRecs = rstemp.RecordCount

    Remember, RecordCount works with adOpenStatic and adOpenKeySet. You will get a recordcount of -1 if you use adOpenDynamic or adOpenForwardOnly. (See Recordset Cursors: Choose the Right Cursor for the Right Job for more information on using .RecordCount with your ADO recordsets.)

    Now I want to store the returned rows in an array with the GetRows function (in this case I am using all returned records. GetRow can also be used to return a defined number of records). This is done to speed up the process. It is significantly faster than looping through a recordset with MoveNext commands.

    vreturnedrows = rstemp.GetRows(NumOfRecs, , vshowfields1)

    The complete syntax for GetRows is: array = recordset.GetRows( Rows, Start, Fields ). So what we are saying is to get all of the rows, start at the beginning, and return all of the fields listed in the vshowfields1 array.

    Now we have records stored and we can begin writing the page:

    <h4>Basic District Information</h4>
    <
    table>
    <
    tr>
         <% for i = 0 to UBound(vcaption1) ' UBound is the last element of the caption array%>
                     <
    td>
                     <
    % response.write(vcaption1(i)) ' Write the column headers %>
                     <
    /td
    >
         <% next %>
    <
    /tr
    >

    <
    % 'zero out all variables used for creating totals at bottom of table
           numRoutes = 0
           numRuns = 0
           numStops = 0
           numMiles = 0
           numRiders = 0

          
    ' vreturnedrows is an array with 2 dimensions. Dimension 1 is columns, dimension 2 is rows.
           nRow = UBound(vreturnedrows, 2)
      'nRow will be the same as NumOfRecs (just a safety check)
          
           for row = 0 to nRow
    ' for each returned record %>
                     <tr>
                     <% for col = 0 to UBound(vreturnedrows, 1)
    ' then for each column of a returned record %>
                                <td>
                                <
    % = vreturnedrows(col, row) %>    ' write the field data
                                <
    /td>
                     <
    % next %>
                     </tr>

                     <% 'Test to see if null values exist in the numeric fields that are returned. If they do store them as 0.
                           'You could use a case statement or elseif statement hear as well.

                           if isnull(vreturnedrows(1, row)) then
                                    return1 ="0"
                           else
                                   return1 = vreturnedrows(1, row)
                           end if

                           if isnull(vreturnedrows(2, row)) then
                                    return2 ="0"
                           else
                                   return2 = vreturnedrows(2, row)
                           end if

                           if isnull(vreturnedrows(3, row)) then
                                    return3 ="0"
                           else
                                    return3 = vreturnedrows(3, row)
                           end if

                           if isnull(vreturnedrows(4, row)) then
                                    return4 ="0"
                          else
                                  return4 = vreturnedrows(4, row)
                          end if

                          if isnull(vreturnedrows(5, row)) then
                                  return5 ="0"
                          else
                                  return5 = vreturnedrows(5, row)
                          end if

                         'Store totals for display at end of table
                         numRoutes = numRoutes + return1
                         numRuns = numRuns + return2
                         numStops = numStops + return3
                         numMiles = numMiles + return4
                         numRiders = numRiders + return5

           next %>

    <tr>
           <td><% =("Routes: " & numRoutes) %></td>
           <td><% =("Runs: " & numRuns) %></td>
           <td><% =("Stops: " & numStops) %></td>
           <td><% =("Miles: " & numMiles) %></td>
           <td><% =("Riders: " & numRiders) %></td>
    <
    /tr
    >
    <
    /table>

    <% rstemp.Close
         set rstemp = Nothing
         dbConnect.Close
         set dbConnect = Nothing
    % >

    There you have it. Like I said, it is a little more complicated than was needed to create this page, but you get to see how arrays and the GetRows function can add to the speed and flexibility of your ASP pages. Because the data is now displayed in an array, you can manipulate it without making another call to the database (which will always be the slowest part of your applications). See this article on how to perform a sort on a two dimensional array. With this type of sorting, you could for example, click on a column heading and have the data re-sorted using that column as the order by, without fetching from the database.

    Next ... Building result pages from a query by form

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


  • 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 3
    Article Author: Roger Drye
    Published Date: Sunday, July 11, 1999
    Article URL: http://www.4GuysFromRolla.com/webtech/071199-2.shtml


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