Building an Internet Database Drilldown Application, Part 3
|Roger Drye||Directed Minds|
|Homepage: www.directedminds.com||Email: firstname.lastname@example.org|
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
|Summary Pages||Detail Pages|
|District Transportation Office||Bus Routes|
|School Bus Office||Students|
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
the type of data that is displayed on this page:
Basic DTO Information
|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|
|Daily Pax: 48293|
|District||Approx Daily Cost||Approx Weekly Cost||Daily Cost / Mile||Daily Cost / Pax||Cost Per Pax / Mile|
|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.
the page header. Most importantly it includes the statement:
Response.Buffer = True
'The ADO constants used by this
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
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:
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
and adOpenKeySet. You will get a recordcount of -1 if you
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:
<% for i = 0 to UBound(vcaption1) ' UBound is the last element of the caption array%>
<% response.write(vcaption1(i)) ' Write the column headers %>
<% next %>
<% '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 %>
<% for col = 0 to UBound(vreturnedrows, 1) ' then for each column of a returned record %>
<% = vreturnedrows(col, row) %> ' write the field data
<% next %>
'Test to see if null
values exist in
the numeric fields that are returned. If they do store them as 0.
return2 = vreturnedrows(2, row)
end if if isnull(vreturnedrows(3, row)) then
return3 = vreturnedrows(3, row)
if isnull(vreturnedrows(4, row)) then
return4 = vreturnedrows(4, row)
end if if isnull(vreturnedrows(5, row)) then
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
<td><% =("Routes: " & numRoutes) %></td>
<td><% =("Runs: " & numRuns) %></td>
<td><% =("Stops: " & numStops) %></td>
<td><% =("Miles: " & numMiles) %></td>
<td><% =("Riders: " & numRiders) %></td>
set rstemp = Nothing
set dbConnect = Nothing
There you have it. Like I said, it is a little more
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
an array, you can manipulate it without making another call to the database
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
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
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.