When working with small Recordsets, it is often easier to use Active Server Pages in order to display those records with navigational buttons for the user to move forward and backward through the recordset.
For our example, I am using the Biblio.mdb database that ships with Visual Basic. I am only working with the ‘All Titles’ view and with books published on or after 1993, in order to keep the recordset manageable.
The key to using ASP to page through a recordset is to use Session variables in order to keep your place in the recordset. You can also keep the recordset itself in a Session variable, thus reducing the server activity and network traffic, however I have tried to keep this example simple. Figure 1 shows the HTML code behind a title page that simply sets some Session Variables and provides a link to display the initial page of records.
Figure 1 – default.aspNotice between the script delimiters we are setting two variables, the first one – Session("Place") is going to track our place in the recordset. The second variable – Session("Count") determines how many records to display at one time. By setting this amount in a variable, we can change it later in one place without having to work through our navigation code.
Figure 2 – DisplayMain.asp
The next step is to supply our main display file, I will present this to you in two phases. Figure 2 shows the first section of our DisplayMain.asp file.We are accomplishing a couple of major items here before we even get to the HTML header. First, we set up and open our ADO recordset. Next we move to the beginning of the recordset and then we set another Session variable. Session("TotalCount") is used later to prevent navigation beyond our last record. Finally, we move through the recordset to our place. We subtract 1 from the place so that initially we do not move past the first record. Figure 3 shows the rest of the DisplayMain.asp file.
Figure 3 – DisplayMain.asp Part 2
The body of our DisplayMain.asp starts out with a table definition and titles for our columns. We add script at this point to cycle through Session("Count") number of records and display them in the proper columns. After our table we have a simple form that consists of nothing but 4 submit buttons. Note that all of these inputs have the same name, but different values, this is important in our processform.asp.
Figure 4 shows the last piece of our puzzle – processform.asp.
Figure 4 – processform.asp
Processform.asp is really pretty simple. First we read in the value of the submit button that was pressed, and proceed based on that value. If the button was either the "Next" button or the "Previous" button, we add or subtract Session("Count") + 1 to our Session("Place") variable. If the "First" button is pressed, or "Previous" takes us below 0, we simply set the Session("Place") variable to 1. If the "Last" button is pressed, or our "Next" button goes beyond the range of the recordset, then we take the total count of records and subtract Session("Count")+1. It is important to note that we are subtracting Session("Count")+1 rather than just Session("Count") due to the fact that we subtract 1 in our routine that sets our place.
The next installment of this demo will show you how to use the same form and display file to integrate record editing, adding, and deletion.