Paging- last page (Or record with only 1 page) gives BOF/EOF

endoflux · Nov 22, 2006

I've got paging implemented to limit each page to 8 records. If (for example) my results are 5 pages long, pages 1-4 work fine, and page 5 gives me the error below. Also, if my results only produce 1 page, I immediately get the error below:

"ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."

Help!

Code (HTML truncated for readability)


<%
Dim iPageSize
Dim iPageCount
Dim iPageCurrent
Dim strSQL
Dim objPagingRS
Dim iRecordsShown
Dim I
Dim Recnum
Recnum = Request("Recnum")
iPageSize = 8

' Open DB and Set Password
Dim adoCon
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../sterilization.mdb")

' Create the Recordset
Set objPagingRS = Server.CreateObject("ADODB.Recordset")
objPagingRS.PageSize = iPageSize
objPagingRS.CursorLocation = adUseClient
objPagingRS.CacheSize = iPageSize

' Select Records

strSQL = "SELECT * FROM PartShipForm WHERE Sterile_Lot_Number = '" & RecNum & "'"
objPagingRS.Open strSQL, adoCon

' Page number

If Request.QueryString("page") = "" Then
iPageCurrent = 1
Else
iPageCurrent = CInt(Request.QueryString("page"))
End If

' Page Count
iPageCount = objPagingRS.PageCount

' No records found
If iPageCount = 0 Then
Response.Write "No records found!"
Else
' Move to the selected page
objPagingRS.AbsolutePage = iPageCurrent

' Page Header
%>
<html>
<head>
<title>TX8391 Sterilization Tracking Log Database</title>
<link rel="stylesheet" href="../style.css" type="text/css">
</head>
<body>

<% ' Detail header %>
<table class="detail" width="760" border=1>
<tr>
<td width="100" align="center">Quantity</td>
<td width="500" align="left"> Product Code/Description (to appear on certificate)</td>
<td width="160" align="center">Lot Number</td>
</tr>
<%
' Detail Rows

iRecordsShown = 0
Do While iRecordsShown < iPageSize And Not objPagingRS.EOF
%>
<tr>
<td width="100" align="center"> <% Response.Write objPagingRS("Quantity") %></td>
<td width="500" align="left"> <% Response.Write objPagingRS("Product_Name") %></td>
<td width="160" align="center"> <% Response.Write objPagingRS("Packaging_Lot_Number") %></td>
</tr>
<%

' Increment the number of records we've shown & move to next record
iRecordsShown = iRecordsShown + 1
objPagingRS.MoveNext
Loop
' Shipping Footer
%>

</table>
<table width="760">
<tr>
<td width="200" align="right" class="thick">TOTAL  </td>
<td width="560" align="right" class="thick">Circle One:             PRODUCTION  /  TEST     </td>
</tr>
<tr>
<td colspan="2" align="left">Any change from your standard dose parameter, box dimensions or density must be advised in writing and could require dose 
mapping. If a dose map is performed there may be a charge associated with this service.</td>
</tr>
</table>

<% ' Footer %>
<table width="760">
<tr>
<td align="left">F001888 Ver E. Form, Production</td>
<td align="right" valign="top">Page <%= iPageCurrent %> of <%= iPageCount %> 
<a href="../index.asp">Home</a>  
<%
If iPageCurrent > 1 Then
%>
<a href="view_ship_gammatrbl.asp?page=<%= iPageCurrent - 1 %>&Recnum=<%= Recnum%>">[<< Prev]</a>
<%
End If

' Page numbers:
For I = 1 To iPageCount
If I = iPageCurrent Then
Response.Write ("")
Else
%>
<a href="view_ship_gammatrbl?page=<%= I %>&Recnum=<%= Recnum%>"><%= I %></a>
<%
End If
Next 'I

If iPageCurrent < iPageCount Then
%>
<a href="view_ship_gammatrbl?page=<%= iPageCurrent + 1 %>&Recnum=<%= Recnum%>">[Next >>]</a>
<%
End If
%>
</td>
</tr>
<tr>
<td colspan=2 align="center">This confidential document...</td>
</tr>
</table>
</body>
</html>

<% End If
' Close DB Objects
objPagingRS.Close
Set objPagingRS = Nothing
adoCon.Close
Set adoCon = Nothing

%>

Abraxus · Nov 22, 2006

Based on the error it seems it's looping too far, despite your careful conditioning. This first thing I would try is to make the Do statement more explicit to ensure that it's processing it as you intend:

Code:

Do While [COLOR=red]([/color][COLOR=blue]([/color]iRecordsShown < iPageSize[COLOR=blue])[/color] And [COLOR=blue]([/color]Not objPagingRS.EOF[COLOR=blue])[/color][COLOR=red])[/color]

Or perhaps since you're getting the EOF error you could try it with just the EOF condition, since I've had some abiguous difficulties combining conditions when Not is involved:

Code:

Do While Not objPagingRS.EOF

And see if that gives you a different result. Or maybe I'm getting a little turned around and the error is actually on a different line?

endoflux · Nov 22, 2006

No go...I tried both suggestions, in addition to just

Do While iRecordsShown < iPageSize

and all produce the same error.

Abraxus · Nov 22, 2006

All right well I'm happy to keep reaching...
Can you confirm the line number of the error, so we're sure which line is throwing the error?
Maybe try running it with the CacheSize line commented out, I haven't worked with cachesize but I could believe in my imagination that it might trick the recordset into thinking it has more records than it does in this case.

I guess that's all for the moment, if you could highlight the exact line that might help, at least I would feel certain I was after the right section.

endoflux · Nov 22, 2006

Unfortunately it doesn't give me a record number; Full error posted below.

ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/sterilization/reports/view_ship_gammatrbl.asp

Commented out the Cache earlier...didn't change anything. However, an item of interest is that for a sample record I verified 19 records existing in the DB, changed my PageSize to "1", and got 19 pages. 18 pages work, 19 gives me the EOF/BOF error.

Given that all but the last page works, I can rule out data selection errors, and given that the correct # of pages are calculated, it's more or less narrowed down to somewhere in the Loop...I'll keep working on it and respond if I figure it out!

Thanks for all your help so far!

endoflux · Nov 22, 2006

This is lame...and it reinforces again the idea that you should always post ALL your code: I cut the following line out of my code in my original post because it's a simple piece and there was a whole load of html surrounding it.

<td colspan="2" align="left">Special Requests: <% Response.Write objPagingRS("Special_Handling_Requirements") %></td>

When I took the page apart section by section, omitting

<% Response.Write objPagingRS("Special_Handling_Requirements") %>

fixed the problem. Then it hit me: If I've gone through all my records in the Do...While Loop further up the page, no more records will exist to call this against.

Since this value doesn't change for the selected parameter, I worked around this by storing the field value in a variable earlier in the page, then calling the variable in place of the Response.Write.

Thanks for all your help!!

endoflux · Nov 27, 2006

I figured I'd drop back by and present yet another solution: Close the RS and reopen it...

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Paging- last page (Or record with only 1 page) gives BOF/EOF

endoflux

Technical User

Abraxus

Programmer

endoflux

Technical User

Abraxus

Programmer

endoflux

Technical User

endoflux

Technical User

endoflux

Technical User

Similar threads

Part and Inventory Search

Sponsor