home / articles / frontpage

Next Ten Records With FrontPage 98 and ASP/ADO

This is the latest in a series of articles about using FrontPage 98 to create ASP/ADO pages. If you haven't read my earlier articles, you might want to before reading this one, because this is a bit more advanced, and if you don't have some foundation, you might get a bit overwhelmed.

This article is intended to do several things:

1. Demonstrate how to fetch 10 records at a time from a database with ASP/ADO. (duh!)
2. Demonstrate how various ASP techniques can be combined, with a bit of creativity, to get various effects. There's more than one way to skin a cat, or program a computer!

There are 3 basic "steps" which will be covered in this article:

1. Set up your query.
2. Display the data, 10 records at a time (if any)
3. Display a "Next 10 Records" button, only if there are any more to be displayed.

So, without any further ado, let's begin with Step 1. The illustration below shows the ASP scripting tags as they appear in FrontPage 98's Editor:

As I've mentioned before, the real "first step" to creating an ASP page with FrontPage 98 is to use the Editor to lay out your HTML, basically as you would like it to appear, without any scripting tags. Then go back and add the tags. Now, the code for the database operation is largely contained in the first tag, and I've reproduced it below:

'** Create your RecordSet object

Set rs = Server.CreateObject("ADODB.RecordSet")

name = Request.Form("name")
number = Request.Form("num")

'** Form validation - Make sure they put something in

if name = "" then
  flag = 0
else
  flag = 1
end if

'** Create the query by concatenating the form values into the query

q = "SELECT [recno],[name],[subject],[date] FROM messages WHERE name LIKE '" &_
name & "%' AND [recno]>" & number & " ORDER BY [recno] ASC"

'** Open the RecordSet

rs.Open q, "DSN=employment;", 3

if NOT rs.EOF then
  x = 0
  while NOT rs.EOF
    rs.MoveNext
    x = x + 1
    if x = 10 then
       num = rs("recno").value
       rs.MoveLast
    end if
  wend
  rs.MoveFirst
end if%>

Now, I'm not going to go over the stuff I've covered in previous articles, but there are number of "new" things I'd like to take a look at here. First of all, take a look at the "form validation" section. This is a very simple form, and a very simple validation script. But it should serve as an example of how you can perform server-side form validation. In this case, it is simply testing to make sure that the user has entered a value. Later, I'll show you how the "flag" variable is used in the display portion of the page.

You'll also notice that I've added a parameter to the Open method of the RecordSet. The third parameter of the Open method is called the CursorType property of the RecordSet. There are 4 CursorType properties available:

1. adOpenForwardOnly - There are no bookmarks available, and you can only move through the cursor using the MoveNext or GetRows methods. This is the default CursorType property. If you don't specify the CursorType, this is what you get. It is also the fastest-performing CursorType.
2. adOpenKeySet - Changes made by other users, except for additions are visible whenever the RecordSet fetches data. Bookmarks are always available, and you can move in any direction within the RecordSet, using any of the navigation methods.
3. adOpenDynamic - All changes made by other users are visible. Bookmarks are sometimes available, and the RecordSet is fully navigable.
4. adOpenStatic - Changes made by other users are not visible. Bookmarks are sometimes available. The RecordSet is fully navigable.

Now, here's where ADO can sometimes get tricky and frustrating, because depending on the version of ADO on the machine, some of the code you put in may mean one thing or another. Some versions refer to these as 1,2,3, and 4. Others refer to them as 0,1,2, and 3. Luckily for us, any of the last 3 will do, as all we want is the ability to move back and forth in the RecordSet. So, I picked 3, since it will work in any case, whether it means "3" or "4." Sheesh!

You'll also notice that I've ordered the results by the "recno" field. This is the easiest way of setting up your query so that it fetches the "Next Ten Records." The "recno" field is an AutoNumber field. It has 2 qualities I like: It is unique to each record, and it is easy to math with. See the code that gets a "num" value from the form which submits to it? The first time, that is set to 0, so the query fetches all records. As the form is submitted each time, the value changes to the "recno" value of the 10th record fetched. How?

Well, here's another tricky part. The simplest way to do this is to limit the number of records fetched. You should be able to do this with the MaxRecords property of the Recordset, which you can set before using it. However, again, this is not implemented correctly in all versions of ADO, so I've created a workaround. For this demonstration, I fetch all records higher than the "num" value, and use a few tricks to display only the first ten, and to get the "recno" value of the 10th record.

First, take a look at the section of code that gets the highest record number returned. I've added an incrementing variable (x) to the loop. When it reaches 10, I get the value of the "recno" field, and move to the last record (to speed things up). The next time through the loop, the loop ends. Later, I will place that "recno" value in the form which submits for the next ten records, and these first ten won't be fetched.

Now, in the second tag, I've added a bit of code to remind the user of what value he/she typed in as a condition for fetching the records. In this case, I've used a wildcard search to get any records with a "name" field beginning with the letters typed in by the user. The ASP tag simply reads "Response.Write name," with name being the variable I assigned the "name" form field to. While this isn't necessary, it's nice.

The next step is to test for form validation, and to see if any records were returned, to determine what to display on the page. Below is a screen shot of that portion of the page:

Remember that code which set a "flag" variable to "1" if there was nothing typed in? The first tag reads "if flag = 1 then." If the "flag" variable has been set to "1," only the first message will be displayed. The second tag reads "elseif rs.EOF then." this means that if no record are returned, display only the second message. The third tag reads simply "else." If the first 2 tests are passed, the rest of the page is displayed. Way at the bottom of the page is an "end if" tag.

The table contains an ASP/ADO loop, which you can't see in the WYSIWYG editor, and you have to go directly into the HTML to create it. Here's the code (I've made the ASP code bold):

<%y = 0
while NOT rs.EOF%>

<%rs.MoveNext
y = y + 1
if y = 10 then
  while NOT rs.EOF
    rs.MoveNext
  wend
end if
wend%>

First of all, notice that I've initialized a variable "y" to 0 at the top. this is the variable which counts how many records I've displayed. When it reaches 10, I create a loop which moves to the EOF part of the RecordSet, so that the outer loop will break. Again, if you can use the MaxRecords property of the RecordSet, this isn't necessary.

The hyperlinks are to a third page which gives a detailed view of the individual record. It passes the "recno" value of the field, as a parameter, to the third page. But that's another story, and not half as interesting as this one. Note also that I've placed the loop around the second row of the table. This repeats that row once per record.

The last stage is to place a button in a form below the table, which submits back to the same page, with the value typed in by the user, and the "recno" value of the last record. However, I only want this button to appear if there are more record to be fetched. Otherwise, I want to display an "End of List" message. Below is a screen shot of that portion of the page in FrontPage Editor:

Remember the variable "x?" That variable will have a value of at least 10 if you haven't reached the end of the list. So, the first tag reads "if x >= 10 then." The second tag reads "else," and the third is the obligatory "end if" tag.

The form contains the "num" variable in a hidden form field, as well as the "name" field. Its' ACTION property is this same page. Therefore, each time the button is pressed, the next ten records are fetched and displayed in this same page.

In conclusion, I want to point out what should be obvious from what I've said: There are any number of ways of doing this with ASP/ADO, and this isn't necessarily the best way. Each step could be done slightly differently, and if the server your page is on supports the MaxRecords property, for example, that would be the best way to go. Other sections of code could be changed as well. ADO is very flexible. All you really have to do is to keep in mind what the basic principles are, and the more you know about ADO, the more ways you can think up of achieving the same goal.

And that's the end of the story!

Author: Kevin Spencer
Date: 01/16/98

More articles about Microsoft FrontPage
More articles by Kevin Spencer
Author Biography