Later we will open recordsets with only one record, and we will also study how to find a specific record, but for now we need to understand which record we are on.
The first two methods to learn when shifting the focus, or pointer, from one record to another are simple.
oRSp.movefirst
oRSp.MoveNext
oRS.movefirst jumps the cursor to the first record. If we then request data from a field we will get the data from the first record. MoveNext jumps the pointer down to the next record of the recordset. By dong a series of MoveNext commands we can work our way through the entire record set.
Note that in most tables you can not assume that when you open a recordset that you will be on the first record. In fact, you cannot be sure that you know what the first record. You may think that the first record is determined by the alphabetical order of the NameLast field, but the records may have actually been entered into the database in chronological order and therefore the first person to be entered is the oldest record, even if her last name is Zyminski. Therefore, you should generally use a RS.MoveFirst prior to starting a walk through the data.
Try It Out - Moving Through Three Records
In this example we'll write a script that produces a page that lists the first three vendors from the Clothier database. For this exercise we can assume that there will be at least three vendors in the database.
<TITLE>2726-03-SimpleRS TIO Move Through 3 Records</TITLE>
</HEAD><BODY>
<H1>Chapter 3 Simple Recordsets</h1>
<H3>Try It Out - Moving Through Three Records</H3>
<%
dim oRSv
set oRSv=server.CreateObject("adodb.recordset")
oRSv.open "vendors", "DSN=clothier"
oRSv.MoveFirst
Response.Write "Next line walks through the first 3 records:<BR>"
Response.Write oRSv("VendorName") & ", "
oRSv.MoveNext
Response.Write oRSv("VendorName") & ", "
oRSv.MoveNext
Response.Write oRSv("VendorName")
oRSv.MoveNext
oRSv.close
set oRSv=nothing
%></BODY></HTML>
The above code produces the following screen.
How It Works - Moving Through Three Records
The first few lines, below, create the recordset, this time from the Vendors table and thus with the object named oRSv.
<H3>Try It Out - Moving Through Three Records</H3>
<%
dim oRSv
set oRSv=server.CreateObject("adodb.recordset")
oRSv.open "vendors", "DSN=clothier"
In order to be sure we are starting at the beginning we must use the following line:
oRSv.MoveFirst
Now we can write the data from the first record along with a following comma and space. Then we move to the next record and print its data, on then once again for the third record.
Response.Write "Next line walks through the first 3 records:<BR>"
Response.Write oRSv("VendorName") & ", "
oRSv.MoveNext
Response.Write oRSv("VendorName") & ", "
oRSv.MoveNext
Response.Write oRSv("VendorName")
oRSv.MoveNext
oRSv.close
set oRSv=nothing
%>
Of course we will soon learn to do this by looping.
A Note on Moving Through Recordsets
Note here that different types of recordsets support different types of methods. We have been using the simplest of recordsets and by default that means that the pointer can only go forward through the data or back all the way to the start. Here are the key points to remember about the default cursor type of the recordset that we've used here:
- You can use the MoveFirst method, as well as MoveNext and MoveLast
- You can use these methods multiple times on one recordset. However, once you have walked through a recordset (using MoveNext), you can't go through it again without first calling MoveFirst again
- You cannot use MovePrevious, bookmarks or other types of moving methods
- The RecordCount property lacks meaning with the default cursor
