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>.

Try It Out - Table Building with EOF

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>"
%>

How It Works - Table Building with EOF

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>"
%>

Try It Out - Sailors Table with EOF and a Counter

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 and a Counter

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>"
%>