By building a table that displays the first five records of a
recordset, we get some idea of the techniques and practice with the HTML codes
for tables. However, most of the time you will have no idea of how many records
are in your recordset – so you won't know how many times to repeat the loop.
ADO provides a way to test whether you are at the end of the records, and thus
should stop building rows. The property to use is called EOF (which is short for 'End Of
File'). The recordset object's EOF property evaluates to True
after you have moved beyond the last row. Take a look at the following test:
Do while NOT oRSp.EOF
Response.Write oRSp("PeopleNameLast") &
"<BR>"
oRSp.MoveNext
Loop
The above code is a little tricky to understand if you have
never used this construction before, so refill the coffee cup and follow this
closely:
1.
The way the DO WHILE works, in general, is
that before each cycle of the loop ASP-ADO checks the test expression on the DO
WHILE line. If the test is True, ASP-ADO will perform the loop
again.
2.
But we are using the value of the EOF property
of oRSp
as the test object. rs.EOF is False when we are still in the data and
True when we are done with the records. That is the opposite of what we want
for the DO WHILE. We want to continue looping when the rs.EOF
is False (we are in middle of records) and we want to stop looping when EOF
is True (at end of records).
3.
VBScript provides us with the word NOT to
reverse the value of the rs.EOF. Now when rs.EOF
returns a False (in middle of
records) NOT changes that into a True and the loop is performed again. When the rs.EOF
is set to True (at end of records), NOT turns
that into a False and the looping
stops.
Remember that every loop must have a way to end. In this
case we include the rs.MoveNext line to tell ADO's cursor to go to the
next record. Eventually ASP-ADO will move beyond the last record and then the rs.EOF
will turn to true. Our test will "NOT"
that True into a False and end
the loop right there at the end of the records.
Common Errors
Leaving the "RS." off of rs.EOF
Leaving RS.MoveNext out of the loop
Leaving out the "NOT" from the expression
Putting <TABLE> inside the loop
Leaving out </TABLE>.
In this example we'll build a table that lists the first and
last names of all of the sailors in the People table
of the sailors database.
<%
dim oRSeof
set
oRSeof=Server.createObject("ADODB.recordset")
oRSEOF.Open "PEople",
"DSN=sailors"
oRSeof.MoveFirst
Response.Write "<TABLE
BORDER='1'>"
Do while NOT oRSeof.EOF
Response.Write "<TR><TD>" &
oRSeof("PeopleNameFirst") & "</TD>"
Response.Write "<TD>" &
oRSeof("PeopleNameLast") & "</TD></TR>"
oRSeof.MoveNext
Loop
Response.Write
"</TABLE>"
%>
The first few lines create the record set.
<%
dim oRSeof
set
oRSeof=Server.createObject("ADODB.recordset")
oRSEOF.Open "People",
"DSN=sailors"
Then with the following line we ensure that we are on the
first record, followed by the tag to start the table
oRSeof.MoveFirst
Response.Write "<TABLE
BORDER='1'>"
Now we begin the loop. We want to loop if the opposite (NOT)
of rs.EOF
is true. In other words when EOF is False (not
yet at end of records, or in other words not yet beyond the last record) we
want ASP-ADO to see that as true and do another loop. When EOF
is True
(we are now done with the records, or in other words, beyond the last record)
then we want ASP-ADO to see that as false and stop cycling.
Do while NOT oRSeof.EOF
Within each cycle we will build a row. That means start with
the <TR> tag. Then add three items for each cell: <TD>,
data and </TD>. At the end of the row we put in a </TR>
Response.Write "<TR><TD>" & oRSeof("PeopleNameFirst")
& "</TD>"
Response.Write "<TD>" &
oRSeof("PeopleNameLast") & "</TD></TR>"
Without the next line ASP-ADO will cycle forever, writing
more rows of the information for sailor number one. We must instruct your code
to move down to the next sailor record after building the table row.
oRSeof.MoveNext
Will Bad
Loops Cycle Forever?
We've all
done it; forget to put in the .MoveNext and run the page, causing an infinite
loop. The server is working away, perhaps putting up thousands of duplicate
lines and we suddenly realize our mistake. At this point you have several
options. First, understand that ASP scripts time out after about 2 minutes. If
you are running a page on a remote host you can stop your browser, correct the
problem and then revisit the page. If you are running PWS you can speed things
up by Start/Programs/MS Personal Web Server/Personal Web Manager/Stop. Then
start it again.
That is it for the loop,
and we write the table closing tag to the page.
Loop
Response.Write "</TABLE>"
%>
Now we can improve on the last code listing by adding a
numbering column and a note on the number of sailors at the bottom. Later we
will learn the use of the recordcount property, but for now we will use a
counter variable.
<%
dim oRSeofc
set
oRSeofc=Server.createObject("ADODB.recordset")
oRSEOFc.Open "People",
"DSN=sailors"
oRSeofc.MoveFirst
Response.Write "<TABLE
BORDER='1'>"
Dim PersonCounter
PersonCounter = 0
Do while NOT oRSeofc.EOF
PersonCounter =PersonCounter + 1
Response.Write "<TR><TD>" & PersonCounter
& "</TD>"
Response.Write "<TD>" &
oRSeofc("PeopleNameFirst") & "</TD>"
Response.Write "<TD>" &
oRSeofc("PeopleNameLast") & "</TD></TR>"
oRSeofc.MoveNext
Loop
Response.Write
"</TABLE><BR>"
Response.Write PersonCounter &
" Sailors in this list"
%>
How
it Works - Sailors Table with EOF an
The set up of the recordset is the same as the last code
listing, but now we dim another variable for the purpose of keeping count of
the people and initialize that to zero:
Dim PersonCounter
PersonCounter = 0
Now when we do our loops we start by increasing that PeopeCounter
by 1 and printing that in its own cell. Then we finish off the row as before.
Do while NOT oRSeofc.EOF
PersonCounter =PersonCounter + 1
Response.Write "<TR><TD>" & PersonCounter
& "</TD>"
Response.Write "<TD>" &
oRSeofc("PeopleNameFirst") & "</TD>"
Response.Write "<TD>" &
oRSeofc("PeopleNameLast") & "</TD></TR>"
oRSeofc.MoveNext
Loop
Response.Write
"</TABLE>"
%>
Visual Basic Books 
