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:

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

Ridership and Bus Useage

Costs

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.

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.

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):

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:

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

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.

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:

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