GetRows VBScript Class - Part III: Paging the results
In Part I of this series, we saw how to create a VBScript class to query our database using the very fast GetRows() method, and return a recordset as a local array. In Part II, we extended the class to allow ADDing and UPDATEing a row in the database. In this Part III, we will expand the class further to allow pagination of the returned recordset.
Published: Jan 16, 2003
Tested with: ASP 3.0, VBScript 5.5
Category: ASP
28,489 views
Introduction
In Part I of this series, we saw how to create a VBScript class to query our database using the very fast GetRows() method, and return a recordset as a local array. In Part II, we extended the class to allow ADDing and UPDATEing a row in the database. In this Part III, we will expand the class further to allow pagination of the returned recordset.
Why GetRows()?
This is an obvious question to ask, especially as we move into the third installment of the series. Why persist on using this method with ASP 3.0, when there are many ways to create a recordset and many more to paginate through it? The reason to use this method for the entire class, is that it is the fastest way to get a recordset out. We can call this method, asking it to return only how many records we want, and it will only fetch that many records.
Class Database
We will add numerous new methods and properties to our class to achieve this. Let's first take a look at the complete structure of the class.
| Properties | |||
| Visibility | Type | Name | Accesibility |
| public | String | AlternateRowColor | Set |
| public | Integer | CellPadding | Set |
| public | Integer | CellSpacing | Set |
| public | Integer | CurrentPage | Get |
| public | Integer | EndingRecord | Get |
| public | String | Output | Get |
| public | String | Query | Set |
| public | Integer | RecordsPerPage | Get, Set |
| public | String | RowColor | Set |
| public | Integer | StartingRecord | Get |
| public | Integer | TotalPages | Get |
| public | Integer | TotalRecords | Get |
| Methods | |||
| Visibility | Return | Name | Parameters |
| public | Boolean | AddNew | (String table, Array fields, Array values) |
| private | --- | Class_Initialize | () |
| private | --- | Class_Terminate | () |
| public | String | DisplayResults | () |
| public | String | GetPagingHTML | () |
| public | Array | GetRecordArray | (Integer intRecordsToShow) |
| public | String | GetRecordPositionHTML | () |
| public | Boolean | Update | (String table, Double id, Array fields, Array values) |
Automatic paging display
Without going through the entire code, let's see the simplest way to query our database and display paginated results using this class. I will be using a simple MS Access database file. All the samples displayed here are available in the download zip file.
First we include the file which has the class code, clsDatabase.asp. Then we create a new instance of the class, and set the Query property of the class to a SQL query. The DisplayResults() method puts everything in a pre-formatted table and returns the full HTML code back to the browser. Our page will now look like this:
By default, the class shows 5 records per page. The first row of the table displays the current resultset location. Then follow the column titles. The rows with the results alternate in colors to improve visibility. The last row of the table shows what page we are on and allows us to move to another page.
Changing the look of the default table
The class allows us to slightly alter the look of the default table. We can change the cellpadding, cellspacing, the row color, and the alternating row color of the results. Let's see the code to do this:
The code above will produce the following display:
Complete customization of the display
Although the DisplayResults() method is simple to use, there might be cases where we need to customize the look of the table even more. For example, we might want to set the width of a particular column. One way to do this is to edit the class code and maybe add more properties. Another way is to move the customization to the presentation layer of ASP. Let's see how we can use the rest of the properties of the class to create a very flexible paging view of our recordset.
So, first we create a local array variable, lclArray, and we populate it using the method GetRecordArray(4). The 4 means that we want to show 4 records per page. The rest of the logic is pretty much the same as the one existing inside the class - we just move it outside for more flexibility. We get the following display from the code above:
Returning all the records
In the previous series, the way to return all the records of the query was by a custom method which we called GetRows(strQuery). This method, as you may have noticed, no longer exists. It has been replaced with GetRecordArray(intRecordsToShow). This was necessary to accomodate the paging mechanism. So, how do we achieve the same effect now if we want to simply return all the records into a local array?
The code above is using a little trick to pass conditional parameters to the GetRecordArray() method. Since it is not possible with VBScript to pass conditional parameters, we simple pass a NULL value if we want to leave a certain parameter empty. Inside the GetRecordArray() method we have some checking if something is passed or not and if NULL is passed, then we set the parameter to some default value. Feel free to explore this method inside the class to see how it is done. In our class, the normal behavior is to pass the number of records we want to see on each page to this method. Passing a value of NULL, simply makes that number something big, like 3,000 records.
As a result, we get all the records in our database returned and displayed:
If we wanted to show only the first 3 of the records, we would use the same code as above except for one line:
| 1 | ... |
| 2 | lclArray = clsDatabase.GetRecordArray(3) |
| 3 | ... |
The 3 as a parameter tells the method to retrieve only the first 3 records.
Conclusion
Until now, we could use this class to add a new row to our database, edit a row, and return a recordset in the form of a local array. Now, we can also paginate the returned recordset easily and fast. We can further expand on this class by adding more properties if we like. For example, we saw that moving some of the code outside of the class, we can gain more flexibility in the display of the contents. Instead of doing that, we can leave the code inside the class, and add some more properties, which will allow us to customize the display even more. Examples that could be implemented right away are whether to show the results position, the pagination links, or the column headers. Maybe even be able to add custom titles to the columns, define a width, nowrap, or be able to sort them by ascending or descending order.
