Which Record? - Page 6
November 27, 2000
In the last section we discussed obtaining data from fields. But
which record was providing the data? Were we on the first record,
the last or somewhere in between? 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>
[Note: The 11th and 12th lines above are one line. They were
split for formatting purposes]
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
In Chapter 8 we will discuss using parameters to create
recordsets enabled with other movement methods.
Using Recordset Data - Page 5
Beginning ASP Databases
Building Tables with Data - Page 7
|
