13.3.2- Moving Through Records

by NT Community Manager.
Last Updated by Jim Minatel.

Public Categorized as 13. Using Recordsets.

Not tagged.

Moving Through Records

In our first example, we saw how to step through a recordset using the MoveNext method. But what about other ways of moving? In fact, the Recordset object gives us four very easy-to-use methods that allow us to move the cursor backwards and forwards through its records:

The MovePrevious and MoveNext methods allow us to move the cursor from the current record to the record immediately preceding or immediately after
The MoveFirst and MoveLast methods move the cursor directly to the first or last record in the recordset

These methods don't require any arguments. For example, and as we've already seen, we call the MoveNext method, pure and simple, like this:

objRS.MoveNext

In addition, to these, there is a fifth method, called Move. The Move method allows us to make the cursor jump over a specified number of records from its current position (or some other specified position). It has two parameters, and its syntax is as follows:

objRecordset.Move NumRecords, Start

Here, objRecordset is the name of our Recordset object. The first parameter, NumRecords, specifies the size of the jump – that is, the number of records that we want to jump. The second parameter, Start, specifies the point that you wish the move to start from. For example, if we want the cursor to jump forward two places from the current record we'd use the following:

objRS.Move 2

Alternatively, if we wanted to jump back three records (and if our recordset doesn't have a 'forward-only cursor type!) then we could use this:

objRS.Move -3

Note that the Start parameter is optional. We'll be looking at some possible values for this parameter when we look at bookmarks, later in this chapter.

Note also that the availability of these methods is dependent upon the recordset type. For example, it's impossible to use MoveFirst or MovePrevious on a 'forward-only' cursor, because it involves moving the cursor backwards! In the case of MoveLast to find the last record in a recordset you actually have to move one beyond the last record in the recordset, to discover that it is the last record. Bizarrely, some forward-only recordsets(such as those used by a SQL Server provider) do allow MoveFirst as they allow the query to be resubmitted to the server. It's best to be careful if you plan to do a lot of moving around the recordset.

Later in the chapter we'll meet the Find method, which is another useful technique for moving the cursor around.

So let's try an example in which we'll exercise these methods. This example provides a departure from the recordsets that we've seen so far, in that it's the first one we've created that doesn't have a forward-only cursor.

Try It Out – Moving Through Records (Data store Viewer)

We'll create a recordset that contains film details taken from the AllMovies table of our database. Then we'll simply step through all the records in the recordset, displaying the values of each record's TitleID, Director and Title fields as we go.

We'll add a little extra option that allows the user to decide whether they want to step through the recordset forwards or backwards, and which record they want to start with. For example, if the user selects 14 and Reverse, we'll begin our list at the 14th film in the database, not the first; then we'll step through all the films in the recordset backwards, till we reach the first – then we'll jump to the very last film and step (backwards) through the rest.

In the course of the action, we'll exercise all of the five Move… methods we mentioned above, and the BOF and EOF properties too.

1. We're going to use the Movies database again, so make sure you've set it up using the Movie2000.mdb or Movie2000.mdf database file as described in Chapter 12 .

2. We also need the connection string details, so ensure that the DataStore.asp file (from Chapter 12) is saved into the folder \inetpub\wwwroot\BegASPFiles.

3. Open up your editor, create a new file called Moving.asp, and enter the following into it:

Option Explicit

Dim strConnect

<!-- METADATA TYPE="typelib"

FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->

<HTML>

<HEAD>

<TITLE>Working your Way round a Recordset</TITLE>

</HEAD>

<BODY>

Dim intChosenRecord, strDirection, strOutputString, intCounter, intNoOfRecords

If Request.Form("ChosenRec") <> "" Then

intChosenRecord = Request.Form("ChosenRec")

strDirection = Request.Form("Dir")

Else

intChosenRecord = 1

strDirection = "Forward"

End If

Dim objRS

Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.Open "AllMovies", strConnect, adOpenStatic, adLockReadOnly, adCmdTable

intNoOfRecords = objRS.RecordCount

objRS.Move intChosenRecord-1

strOutputString = "<TABLE BORDER=1>" & _

"<TR><TD WIDTH=""30%""><B>Director</B></TD>" & _

"<TD><B>Film</B></TD></TR>"

If strDirection = "Forward" Then

While Not objRS.EOF

strOutputString = strOutputString & "<TR>" & _

"<TD> " & objRS("TitleID") & ": " & objRS("Director") & "</TD>" & _

"<TD>" & objRS("Title") & "</TD>" & _

"</TR>"

objRS.MoveNext

Wend

objRS.MoveFirst

For intCounter = 1 To intChosenRecord-1

strOutputString = strOutputString & "<TR>" & _

"<TD> " & objRS("TitleID") & ": " & objRS("Director") & "</TD>" & _

"<TD>" & objRS("Title") & "</TD>" & _

"</TR>"

objRS.MoveNext

Else

While Not objRS.BOF

strOutputString = strOutputString & "<TR>" & _

"<TD> " & objRS("TitleID") & ": " & objRS("Director") & "</TD>" & _

"<TD>" & objRS("Title") & "</TD>" & _

"</TR>"

objRS.MovePrevious

Wend

objRS.MoveLast

For intCounter = intNoOfRecords To intChosenRecord+1 Step -1

strOutputString = strOutputString & "<TR>" & _

"<TD> " & objRS("TitleID") & ": " & objRS("Director") & "</TD>" & _

"<TD>" & objRS("Title") & "</TD>" & _

"</TR>"

objRS.MovePrevious

End If

strOutputString = strOutputString & "</TABLE>"

objRS.Close

Set objRS = Nothing

Response.Write strOutputString

<H2>Format the list!</H2>

Where do you want ths list to begin? Record

For intCounter=1 To intNoOfRecords

Response.Write "<OPTION VALUE=" & intCounter & ">" & intCounter & "</OPTION>"

</SELECT><BR><BR>

Do you want the records to be listed

in <INPUT TYPE="RADIO" NAME="Dir" VALUE="Forward" CHECKED><B> forward</B></INPUT>

or <INPUT TYPE="RADIO" NAME="Dir" VALUE="Reverse"> <B>reverse</B></INPUT>

order (select one)?

</FORM>

</BODY>

</HTML>

4. Save Moving.asp into the same folder: \inetpub\wwwroot\BegASPFiles.

5. Now view the page Moving.asp in your browser. You'll see a long table, listing all 330 records in order from 1 to 330 (you can see the last few rows of the table in the following screenshot). At the end, you'll see the form that allows us to choose where to start the listing, and in what order:

Chapter13_image005

6. Select a record from the drop-down list and select either Forward or Reverse. Then click on View the List, which refreshes the page according to your options:

Chapter13_image006

Try it a few times, just to get a feeling for how the example works. The numbers on the left of the table rows are the record numbers as they appear in the database and in the table – we've displayed them here so you can see how the cursor is moving around the recordset.

How It Works – the Form

This is quite a long example, but if you examine the code you'll see that it's not complicated. Let's start with the very last part of the code, by getting the form out of the way. The form is really very simple: it asks the user for two values. First, it asks for an integer which will specify which record appears first in our table, when we submit to refresh the page:

<H2>Format the list!</H2>

Where do you want ths list to begin? Record

For intCounter=1 To intNoOfRecords

Response.Write "<OPTION VALUE=" & intCounter & ">" & intCounter & "</OPTION>"

</SELECT><BR><BR>

When we submit the form, this will create an entry in the Request.Form collection, called ChosenRec. Its value is taken from the value selected in the drop-down list. We've created the drop-down list using ASP – creating one line of the list for each integer between 1 and intNoOfRecords. The variable intNoOfRecords was populated earlier on in the page (when the recordset was open), by using the Recordset object's RecordCount property:

intNoOfRecords = objRS.RecordCount

Second, it asks whether we want the records listed in forward- or reverse-order:

Do you want the records to be listed

in <INPUT TYPE="RADIO" NAME="Dir" VALUE="Forward" CHECKED><B> forward</B></INPUT>

or <INPUT TYPE="RADIO" NAME="Dir" VALUE="Reverse"> <B>reverse</B></INPUT>

order (select one)?

</FORM>

This creates another variable in the Request.Form collection, called Dir. If we select Forward, then the cursor will move forwards through the recordset; If we select Reverse, then the cursor will move backwards through the recordset.

How It Works – the Recordset

OK, assuming we've made those choices, what happens when we submit them? First, we #INCLUDE the DataStore.asp file that contains our connection string details, reference the ADO constant library msado15.dll, and we create the page head:

Option Explicit

Dim strConnect

<!-- METADATA TYPE="typelib"

FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->

<HTML>

<HEAD>

<TITLE>Working your Way round a Recordset</TITLE>

</HEAD>

Now we can set the variables that we'll need. The intChosenRecord and strDirection variables are used to contain the values passed into the page from the HTML form (and found in the Request.Form collection):

<BODY>

Dim intChosenRecord, strDirection, strOutputString, intCounter, intNoOfRecords

If Request.Form("ChosenRec") <> "" Then

intChosenRecord = Request.Form("ChosenRec")

strDirection = Request.Form("Dir")

Else

intChosenRecord = 1

strDirection = "Forward"

End If

Note that the first time you call the page, you don't get a chance to fill in a form – so we set default values for the intChosenRecord and strDirection variables.

The default behavior, as you probably noticed the first time you called the page, is to start at the first record and iterate in a forward direction.

Now we can create the Recordset object, and Open it:

Dim objRS

Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.Open "AllMovies", strConnect, adOpenStatic, adLockReadOnly, adCmdTable

Let's look more carefully that the five parameters we've chosen here. Since we studied them earlier in the chapter, they should now be a little less mysterious to you:

The first parameter specifies the AllMovies table of the database
The second parameter is the strConnect connection string which is specified in the DataStore.asp SSI, and which specifies the location of the database
The third parameter specifies the value adOpenStatic, which requests a static cursor type. This will allow the forward and backward movement through the recordset that we need.

We could have selected adOpenDynamic or adOpenKeyset – but we're not interested in viewing changes made to records (either by ourselves or by other users of the database) so neither are necessary.

The fourth parameter specifies the value adLockReadOnly, which specifies that we can't use this recordset to make changes to the database. That's fine, because we're only using it to read data from the database
The fifth parameter specifies the value adCmdTable, which tells ADO that the data source specified in the first parameter is a database table

Once the recordset is open and contains the necessary data, we can begin to write it all to the browser. We can build a table to display all the film details. In this example we built a string called strOutputString, over a sequence of statements, which contains all the HTML for the entire table. When we've finished writing the string, we Response.Write the whole thing in one go.

So here goes. The first thing to do is position the cursor on the record that will be the first to appear in the table:

objRS.Move intChosenRecord-1

Remember that the expression intChosenRecord-1 is just an integer. For example, if intChosenRecord is 14, then this moves the cursor 13 places from the first record in the recordset to 14th record.

Now we write the body of the table into our string. The overall structure of this part is as follows:

strOutputString = "<TABLE BORDER=1>" & _

"<TR><TD WIDTH=""30%""><B>Director</B></TD>" & _

"<TD><B>Film<B></TD></TR>"

If strDirection = "Forward" Then ' iterate forward through the RS

' write one table row for each record from the chosen record to the end

' jump to the beginning of the recordset

' write one line for each record from the beginning to the chosen record

Else ' iterate backward through the RS

' write one table row for each record from the chosen record to the beginning

' jump to the end of the recordset

' write one line for each record from the end to the chosen record

End If

strOutputString = strOutputString & "</TABLE>"

Let's fill in the gaps here. If we're iterating forwards through the recordset, we do so using the MoveNext method. When we get to the end of the recordset, objRS.EOF becomes True. At this stage, we jump to the first record using the MoveFirst method and then iterate through the remaining records using MoveNext again:

While Not objRS.EOF

strOutputString = strOutputString & "<TR>" & _

"<TD> " & objRS("TitleID") & ": " & objRS("Director") & "</TD>" & _

"<TD>" & objRS("Title") & "</TD>" & _

"</TR>"

objRS.MoveNext

Wend

objRS.MoveFirst

For intCounter = 1 To intChosenRecord-1

strOutputString = strOutputString & "<TR>" & _

"<TD> " & objRS("TitleID") & ": " & objRS("Director") & "</TD>" & _

"<TD>" & objRS("Title") & "</TD>" & _

"</TR>"

objRS.MoveNext

On the other hand, if we're iterating backwards through the recordset then we do things the other way around. Iterate from the chosen records, backwards towards the first record, using the MovePrevious method. When we get past the first record, objRS.BOF becomes True. Then, we jump to the last record using the MoveLast method and then iterate backwards through the remaining records using MovePrevious again:

While Not objRS.BOF

strOutputString = strOutputString & "<TR>" & _

"<TD> " & objRS("TitleID") & ": " & objRS("Director") & "</TD>" & _

"<TD>" & objRS("Title") & "</TD>" & _

"</TR>"

objRS.MovePrevious

Wend

objRS.MoveLast

For intCounter = intNoOfRecords To intChosenRecord+1 Step -1

strOutputString = strOutputString & "<TR>" & _

"<TD> " & objRS("TitleID") & ": " & objRS("Director") & "</TD>" & _

"<TD>" & objRS("Title") & "</TD>" & _