Classic ASP do while loop showing error

Question

I have a classic ASP page with a simple html table, and I want to loop the table rows based on a unknown number of records pulled from the database, however, when I am looping the records with a do/while loop, I am getting an error saying that Either BOF or EOF is True. I want every other row of the table to alternate background colors (the colors I have set in CSS).

<% do while not rsTest.eof %>
<tr class="odd">
<td colspan="5"><%=(rsTest.Fields.Item("field").Value)%></td>
</tr>

<% rsTest.moveNext
if not rsTest.eof then 
count = count + 1 %>
<tr class="even">
<td colspan="5"><%=(rsTest.Fields.Item("field").Value)%></td>
</tr>
<% end if %>

<% count = count + 1 
rsTest.moveNext 
loop %>

The error, according to the browser, is occuring on the last "rsRoster.moveNext" right before the loop. The loop doesn't error out if there are an even number of records being pulled from the database, but it errors if there are an odd amount of records being pulled. I have tried inserting some "if EOF then nothing, else execute code", but the code checking if EOF just seems to be getting ignored when I do that. Any suggestions would be appreciated.

Solution 1

I know I am rusty on this one but try this:

<% 
  Dim oddFlag
  oddFlag = 1
  do while not rsTest.eof 
  if oddFlag=1 Then 
    oddFlag=0
    Response.write("<tr class='odd'>")
    Response.write("<td colspan='5'>")
    Response.write(rsTest.Fields.Item("field").Value)
    Response.write("</td></tr>")
  else 
    oddFlag=1 
    Response.write("<tr class='even'>")
    Response.write("<td colspan='5'>")
    Response.write(rsTest.Fields.Item("field").Value)
    Response.write("</td></tr>")
  end if
  rsTest.moveNext 
 loop 
%>

Solution 2

Since the other answers don't mention this: the problem with your code is that you're doing MoveNext twice, and the second one doesn't test if the first one already reached the EOF.

In any case, that's a needlessly complicated way to do alternating colors.

dim i, rs
'... database stuff, table header, etc.
i = 0
Do Until rs.EOF
   i = i + 1
   Response.Write "<tr class='"
   If i Mod 2 = 0 Then Response.Write "even" Else Response.Write "odd" End If
   Response.Write "'>"
   '... write out the actual content of the table
   Response.Write "</tr>"
   rs.Movenext
Loop
'... clean up database, close table

With this method, your counter variable (i) is available as an actual, well, counter - so for example if you want to write out a "number of rows returned" message at the end, you can.

Solution 3

Little bit sloppy here but this is how I would normally accomplish this:

<%
Dim i
i = 1
do while not rsTest.eof
If i = 1 Then %>
<tr class="odd">
<% Else %>
<tr class="even">
<% End If %>
<td colspan="5"><%=(rsTest.Fields.Item("field").Value)%></td>
</tr>
<% 
i = i + 1
If i = 3 Then i = 1
count = count + 1
rsTest.moveNext 
loop %>

Solution 4

Why not just use:

While not rs.EOF
'stuff
rs.movenext
wend

Or to make sure:

if not rs.eof then
   while not rs.eof
    'stuff
   rs.movenext
   wend
end if

And even better way is to cache everyting and keep connection very short:

'... set global base (include file)

dim dbcon, rs, rsSQL, rsArray

Function openCon()
    set dbcon = server.createobject("ADODB.Connection")
    dbcon.open Application("YOURDB_Connectionstring")
End Function

Function closeCon()
    dbcon.Close
    set dbcon = nothing
End Function

function rw(stringwriteshortcut)
    response.write(stringwriteshortcut)
end function
'... end global


'... Database interaction:
rsSQL = "SELECT item1, item2 FROM table where ID = 1"

openCon()
set rs = dbcon.execute(rsSQL)
if not rs.eof then
    rsArray = rs.getRows();
end if
closeCon()

dim items
if isarray(rsArray) then

    for items = 0 to UBound(rsArray, 2)

        rw(rsArray(0,items) &"<br>")
        rw(rsArray(1,items) &"<br>")

    next

else
   rw("nothing there")
end if

View more solutions

Comments

[user2762748]

The number of records being called from the database is unknown. There could be 3 records, or there could be 100. I wouldn't want to have to declare "i" for every possible odd number.

[HK1]

You're not. i increments from 1 to 2 and then back to 1. You don't need to know how many records are in the database.

[user2762748]

This seems to work. I am a little confused on the logic of it though, specifically the first part of the "if" statement, where "if oddFlag = 1 then oddFlag=0". Could you explain the logic of how this is being interpreted.

[Edper]

I started with the oddFlag=1 which means I'll start with odd row in this case. So when it is odd row it will show the odd class row then turn oddFlag to 0 which means in the next loop it would be for the even row. So, on the next loop it will display the even row and then turn the oddFlag to 1 so that the next loop would be for odd again. And so on and so forth.

[Shadow The Kid Wizard]

Yep, better way indeed.

[easleyfixed]

Also people need to know the difference between UNTIL and WHILE. WHILE gets the last record, and UNTIL usually STOPS because of the last record (NOT including it in the recordset). Likely the reason for even working but odd was not.

[Martha]

Note that nowadays, you can do alternating colors with a few lines of CSS, so there's no need to do it in ASP...