1. Home
  2. Forums Index
  3. Server Side / Microsoft IIS Web Server and ASP.NET 5:20 am Apr 28, 2026

Forum Moderators: open

Message Too Old, No Replies

ASP 3.0 ADO Paging problem

asp paging problem

         

socoolbrewster

7:08 pm on Aug 2, 2003 (gmt 0)

10+ Year Member



Can anybody spot the bug in my code? On running the script all records are displayed for the first page, but then shows an error an BOF / EOF error should I attempt to load the next page.

To stop the end of file error I have now included an IF statement to stop such errors, but how can I get it to move to the next page.

The error message was:

Error Type:
ADODB.Recordset (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/socoolbrew/testcab.asp, line 61

Line 61 being: rsData.AbsolutePage = intPage

Which has been replaced with:

MyPageSize = 10

on the recommendation of Smokin but still no joy :-(

<%@LANGUAGE="VBSCRIPT"%>
<html><title></title>
<%
'Option Explicit
'Response.Expires = 0
%>
<!--#include file="adovbs.inc" -->
<%
Dim StrSearchtxt
StrSearchtxt = Request.Form("cool") ' Get text to define SQL filter search

'Should a user try and submit a blank field for a search
If StrSearchtxt = "" Then
Response.Redirect("index.html")
End If

Dim rsData
Dim intPage
Dim intTotalPages
Dim fldF
Dim intRec
Dim strQuote
Dim strScriptName
Dim StrQ
Dim StrConn
Dim MyPageSize

StrQuote = Chr(34) ' The double quote character

Set rsData = Server.CreateObject("ADODB.Recordset")

' Setting the page size

MyPageSize = 10
rsData.CursorLocation = adUseClient

' Define connection string to database

StrConn = "DSN=brewster"

' Open the recordset and define SQL statement
strQ = "SELECT * FROM review WHERE title = '" & StrSearchtxt & "'"
rsData.Open StrQ, strConn, 3, 1
%>
<%
' Count how many records obtained from database if 0 then redirect script to prevent error
Dim strRecordsCount
strRecordsCount = rsData.RecordCount
If strRecordsCount = 0 Then
rsData.Close
Set rsData = Nothing
Response.Redirect("index.html")
End If
%>

<body>

<%
If Request.QueryString("PAGE") = "" Then
intPage = 1
Else
' Protect against out of range pages, in case of a user specified page number
If intPage < 1 Then
intPage = 1
Else
If intPage > rsData.PageCount Then
intPage = rsData.PageCount
Else
intPage = CInt(Request.QueryString("PAGE"))
End If
End If
End If

' Set the absolute page number to the requested page

rsData.AbsolutePage = intPage

' Start building the table

Response.Write "<TABLE BORDER=1><THEAD<TR>"
For Each fldF In rsData.Fields
Response.Write "<TD>" & fldF.Name & "</TD>"
Next
Response.Write "</TR></THEAD><TBODY>"

'Looping through

For intRec = 1 To MyPageSize
If Not rsData.EOF Then
Response.Write "<TR>"
For Each fldF In rsData.Fields
Response.Write "<TD>" & fldF.Value & "</TD>"
Next
Response.Write "</TR>"
rsData.MoveNext
End If
Next
Response.Write "</TBODY></THEAD></TABLE><P>"

'Now some paging controls

strScriptName = Request.ServerVariables("SCRIPT_NAME")
Response.Write "&nbsp;<A HREF= " & strQuote & strScriptName & "?PAGE=1" & strQuote & ">First Page</A>"

'Only give an active previous page if there are previous pages

If intPage = 1 Then
Response.Write "&nbsp;<SPAN>Previous Page</SPAN>"
Else
Response.Write "&nbsp;<A HREF=" & strQuote & strScriptName & "?PAGE=" & intPage - 1 & strQuote & ">Previous Page</A>"
End If

'Only give an active next page if there are more pages
If intPage = rsData.PageCount Then
Response.Write "&nbsp;<SPAN>Next Page</SPAN>"
Else
Response.Write "&nbsp;<A HREF=" & strQuote & strScriptName & "?PAGE=" & intPage + 1 & strQuote & ">Next Page</A>"
End If

Response.Write "&nbsp;<A HREF=" & strQuote & strScriptName & "?PAGE=" & rsData.PageCount & strQuote & ">Last Page</A>"

' Dispose of all objects & connections

rsData.Close
Set rsData = Nothing
%>
</html>

TheDave

8:05 am on Aug 4, 2003 (gmt 0)

10+ Year Member



Just looking at this, there is something wrong:

If Request.QueryString("PAGE") = "" Then
intPage = 1
Else
' Protect against out of range pages, in case of a user specified page number
If intPage < 1 Then
intPage = 1
Else
If intPage > rsData.PageCount Then
intPage = rsData.PageCount
Else
intPage = CInt(Request.QueryString("PAGE"))
End If
End If
End If

I would use:

'set the page to 1 to start with
intPage = 1

'see if the user has specified a different page
if not Request.QueryString("PAGE") = "" then
'your taking a gamble they dont enter a text string here
intPage = CInt(Request.QueryString("PAGE"))
if intPage < 1 then intPage = 1
if intPage > rsData.PageCount then intPage = rsData.PageCount
end if

socoolbrewster

9:52 am on Aug 4, 2003 (gmt 0)

10+ Year Member



TheDave, I have tried your suggestion but it still gives me errors. Where it will not go to the next page of a reocrdset. The code now stands as follows:

<%@LANGUAGE="VBSCRIPT"%>
<html><title></title>
<%
'Option Explicit
'Response.Expires = 0
%>
<!--#include file="adovbs.inc" -->
<%
Dim StrSearchtxt
StrSearchtxt = Request.Form("cool") ' Get text to define SQL filter search

'Should a user try and submit a blank field for a search
If StrSearchtxt = "" Then
Response.Redirect("index.html")
End If

Dim rsData
Dim intPage
Dim intTotalPages
Dim fldF
Dim intRec
Dim strQuote
Dim strScriptName
Dim StrQ
Dim StrConn
Dim MyPageSize

StrQuote = Chr(34) ' The double quote character

Set rsData = Server.CreateObject("ADODB.Recordset")

' Setting the page size

MyPageSize = 5
rsData.PageSize = MyPageSize

'rsData.CursorLocation = adUseClient

' Define connection string to database

StrConn = "DSN=brewster"

' Open the recordset and define SQL statement
strQ = "SELECT * FROM review WHERE title = '" & StrSearchtxt & "'"
rsData.Open StrQ, strConn, 3, 1
%>
<%
' Count how many records obtained from database if 0 then redirect script to prevent error
Dim strRecordsCount
strRecordsCount = rsData.RecordCount
If strRecordsCount = 0 Then
rsData.Close
Set rsData = Nothing
Response.Redirect("index.html")
End If

%>

<body>

<%
'set the page to 1 to start with
intPage = 1

'see if the user has specified a different page
if not Request.QueryString("PAGE") = "" then
'your taking a gamble they dont enter a text string here
intPage = CInt(Request.QueryString("PAGE"))
if intPage < 1 then intPage = 1
if intPage > rsData.PageCount then intPage = rsData.PageCount
end if

'rsData.Move (intPage - 1) * MyPageSize + 1

' Start building the table

Response.Write "<TABLE BORDER=1><THEAD<TR>"
For Each fldF In rsData.Fields
Response.Write "<TD>" & fldF.Name & "</TD>"
Next
Response.Write "</TR></THEAD><TBODY>"

'Looping through

intRec = 0
Do Until intRec = 5 Or rsData.EOF
Response.Write "<TR>"
For Each fldF In rsData.Fields
Response.Write "<TD>" & fldF.Value & "</TD>"
Next
Response.Write "</TR>"
rsData.MoveNext
intRec = intRec + 1
Loop
Response.Write "</TBODY></THEAD></TABLE><P>"

'Now some paging controls

strScriptName = Request.ServerVariables("SCRIPT_NAME")
Response.Write "&nbsp;<A HREF= " & strQuote & strScriptName & "?PAGE=1" & strQuote & ">First Page</A>"

'Only give an active previous page if there are previous pages

If intPage = 1 Then
Response.Write "&nbsp;<SPAN>Previous Page</SPAN>"
Else
Response.Write "&nbsp;<A HREF=" & strQuote & strScriptName & "?PAGE=" & intPage - 1 & strQuote & ">Previous Page</A>"
End If

'Only give an active next page if there are more pages
If intPage = rsData.PageCount Then
Response.Write "&nbsp;<SPAN>Next Page</SPAN>"
Else
Response.Write "&nbsp;<A HREF=" & strQuote & strScriptName & "?PAGE=" & intPage + 1 & strQuote & ">Next Page</A>"
End If

Response.Write "&nbsp;<A HREF=" & strQuote & strScriptName & "?PAGE=" & rsData.PageCount & strQuote & ">Last Page</A>"

' Dispose of all objects & connections

rsData.Close
Set rsData = Nothing
%>
</html>

TheDave

10:50 am on Aug 4, 2003 (gmt 0)

10+ Year Member



Don't modify a single thing, and let me know if this works:

dim rs
dim f

set rs = Server.CreateObject("ADODB.Recordset")
rs.Fields.Append "field1", 3
rs.Fields.Append "field2", 3

rs.PageSize = 15

rs.Open

'fill the recordset with some numbers
c = 0
while c < 100
rs.AddNew
rs("field1") = c
rs("field2") = 100-c
c = c + 1
wend

'this is how you should test for an empty recordset
if not (rs.BOF and rs.EOF) then

'set the page to 1 to start with
intPage = 1

'see if the user has specified a different page
if not Request.QueryString("PAGE") = "" then
'your taking a gamble they dont enter a text string here
intPage = CInt(Request.QueryString("PAGE"))
if intPage < 1 then intPage = 1
if intPage > rs.PageCount then intPage = rs.PageCount
end if

rs.AbsolutePage = intPage

response.write "<table><tr>"
for each f in rs.Fields
response.write "<th>" & f.name & "</th>"
next

c = 0
while c < rs.PageSize and not rs.EOF
response.write "</tr><tr>"
for each f in rs.Fields
response.write "<td>" & f.value & "</td>"
next
rs.MoveNext
c = c + 1
wend

response.write "</tr></table>"

if intPage > 1 then response.write "<a href=" & chr(34) & Request.ServerVariables("SCRIPT_NAME") & "?page=" & (intPage - 1) & chr(34) & ">Prev</a>"

if intPage < rs.PageCount then response.write "<a href=" & chr(34) & Request.ServerVariables("SCRIPT_NAME") & "?page=" & (intPage + 1) & chr(34) & ">Next</a>"

end if

socoolbrewster

2:03 pm on Aug 4, 2003 (gmt 0)

10+ Year Member



Yeah that code works fine. I guess it's me I not that great at ASP paging!

TheDave

2:32 pm on Aug 4, 2003 (gmt 0)

10+ Year Member



It may be also that your database provider isn't capable of paging. Instead of appending fields (delete all rs.fields.append), open a recordset across your connection with my code, and see if that works.

socoolbrewster

2:54 pm on Aug 4, 2003 (gmt 0)

10+ Year Member



Hi TheDave I have amended your code as you suggested and still have the same problem. Whereby it loads the first and on clicking the next page throws it out.

The database I am having to use for cost issues is MS Access 2000. And am testing my code on my PC using a localhost option whereby I would type for example:

[localhost...]

I'm not sure whether it is my code or the PC, That said it has worked before and as stated if I use a basic SQL statement such as SELECT title from USER paging works fine. It only seems to happen when an SQL statment has a variable passed to it.

The attempted code was as follows:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<html>
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<%
'Response.Expires = 0
%>
<!--#include file="adovbs.inc" -->
<%
Dim StrSearchtxt, strConn, rsData, strQ
StrSearchtxt = Request.Form("cool") ' Get text to define SQL filter search

'Should a user try and submit a blank field for a search
If StrSearchtxt = "" Then
Response.Redirect("index.html")
End If

'Establish connection to datbase using DSN link
strConn = "DSN=brewster"

' Create a recordset object
Set rsData = Server.CreateObject("ADODB.Recordset")

' Open recordset based on SQL statement
strQ = "SELECT [title], [rating] FROM [review] WHERE [title] = '" & StrSearchtxt & "'"
rsData.PageSize = 5
rsData.Open strQ, strConn, 3, 1

'test for an empty recordset
if not (rsData.BOF and rsData.EOF) then

'set the page to 1 to start with
intPage = 1

'see if the user has specified a different page
if not Request.QueryString("PAGE") = "" then
'your taking a gamble they dont enter a text string here
intPage = CInt(Request.QueryString("PAGE"))
if intPage < 1 then intPage = 1
if intPage > rsData.PageCount then intPage = rsData.PageCount
end if

rsData.AbsolutePage = intPage

response.write "<table><tr>"
for each f in rsData.Fields
response.write "<th>" & f.name & "</th>"
next

c = 0
while c < rsData.PageSize and not rsData.EOF
response.write "</tr><tr>"
for each f in rsData.Fields
response.write "<td>" & f.value & "</td>"
next
rsData.MoveNext
c = c + 1
wend

response.write "</tr></table>"

if intPage > 1 then response.write "<a href=" & chr(34) & Request.ServerVariables("SCRIPT_NAME") & "?page=" & (intPage - 1) & chr(34) & ">Prev</a>"

if intPage < rsData.PageCount then response.write "<a href=" & chr(34) & Request.ServerVariables("SCRIPT_NAME") & "?page=" & (intPage + 1) & chr(34) & ">Next</a>"

end if

%>

<body>

</body>
</html>

socoolbrewster

3:08 pm on Aug 4, 2003 (gmt 0)

10+ Year Member



It's defo something to do with accepting the variable from a form as if I:

Replace this:

StrSearchtxt = Request.Form("cool")

With this:

StrSearchtxt = "Terminator 3"

The paging works fine. Any ideas what I could try?

TheDave

3:27 pm on Aug 4, 2003 (gmt 0)

10+ Year Member



Well I was going to suggest using a DSN-less connection:

set cn = Server.CreateObject("ADODB.Connection")
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open "Data Source=" & Server.MapPath("db.mdb")

Because it works fine for me like that. But now your saying it's when you get something from the form... Well all I can suggest is check your case (ie UPPER/lower), and validate user input (important - see sql injection). I dunno, its 1:00am and Im going to bed so dont expect any more quick answers :P

socoolbrewster

4:43 pm on Aug 4, 2003 (gmt 0)

10+ Year Member



I have now modified the SQL statement to accept parameters in another way. By doing so the script no longer crashes but on loading the second page a blank page is displayed. Any suggestions anyone? ASP Paging is my new nightmare!

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<html>
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<%
'Response.Expires = 0
%>
<!--#include file="adovbs.inc" -->
<body>
<%
Dim StrSearchtxt, strConn, rsData, strQ, brewster

'Establish connection to datbase using DSN link
strConn = "DSN=brewster"

' Create a recordset object
Set rsData = Server.CreateObject("ADODB.Recordset")

' Open recordset based on SQL statement
strQ = "SELECT [title], [rating] FROM [review] WHERE [title] ='" & Ucase(request.form("cool")) & "'"
rsData.PageSize = 5
rsData.Open strQ, strConn

'test for an empty recordset
if not (rsData.BOF and rsData.EOF) then

'set the page to 1 to start with
intPage = 1

'see if the user has specified a different page
if not Request.QueryString("PAGE") = "" then
'your taking a gamble they dont enter a text string here
intPage = CInt(Request.QueryString("PAGE"))
if intPage < 1 then intPage = 1
if intPage > rsData.PageCount then intPage = rsData.PageCount
end if

rsData.AbsolutePage = intPage

response.write "<table><tr>"
for each f in rsData.Fields
response.write "<th>" & f.name & "</th>"
next

c = 0
while c < rsData.PageSize and not rsData.EOF
response.write "</tr><tr>"
for each f in rsData.Fields
response.write "<td>" & f.value & "</td>"
next
rsData.MoveNext
c = c + 1
wend

response.write "</tr></table>"

if intPage > 1 then response.write "<a href=" & chr(34) & Request.ServerVariables("SCRIPT_NAME") & "?page=" & (intPage - 1) & chr(34) & ">Prev</a>"

if intPage < rsData.PageCount then response.write "<a href=" & chr(34) & Request.ServerVariables("SCRIPT_NAME") & "?page=" & (intPage + 1) & chr(34) & ">Next</a>"

end if

%>

</body>
</html>

socoolbrewster

3:59 pm on Aug 5, 2003 (gmt 0)

10+ Year Member



Over the page few days I have tried a number of paging script variations and most suffer from the same common problem: that of when loading the second page of records an EOF or BOF is displayed.

What I didn't realise was that if my script has to keep calling itself doesn't that mean that the value passed from the form will become a NULL value therefore passing strSearchtxt = NULL?

Having read Professional Active Server Pages 3.0 from cover to cover I thought I would have it sorted and have since constructed a simple script with the problem stated above of:

Error Type:
ADODB.Recordset (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/socoolbrew/fiveon.asp, line 45

Might creating a session variable be the answer? and if so how?
<%@ Language=VBScript %>
<HTML>
<HEAD>
<TITLE></TITLE>
</HEAD>
<BODY>

<%

Dim strSearchtxt
strSearchtxt = Request.Form("cool") ' Get text to define SQL filter search
Dim Con
Dim rsPage
Dim Page
Dim RowCount
Dim PageCounter

'Get the Current page
Page = Request.QueryString("Page")

'If there is no page set it to page 1
If Page = "" then Page = 1

RowCount = 0

set con = server.CreateObject("ADODB.Connection")
Set rsPage = Server.CreateObject("ADODB.Recordset")

lsSQL = "SELECT * FROM review WHERE title = '" & strSearchtxt & "'"

Con.Open "DSN=brewster"

'Need a rich cursor type to support paging
rsPage.CursorType = 3 'adOpenStatic

'Set the number of records in each page to 10
rsPage.PageSize = 5

'Open recordset
rsPage.Open lsSQL, Con

'Set the current page based on the QueryString value
'Must cast it as an integer or else it will have problems.
rsPage.AbsolutePage = cInt(Page)

If Response.IsClientConnected = true then
Response.Write "<TABLE>"

'Loop though each of the records and break out when we
'have reached the max for this page
Do while not rsPage.eof and RowCount < rsPage.PageSize

'Write out content
Response.Write "<tr><td>" & rsPage("title") & "</td></tr>"

rsPage.Movenext
RowCount = RowCount + 1
Loop

Response.Write "</TABLE><p>"

'Page to determine the AbsolutePage to display.
For PageCounter = 1 to rsPage.PageCount
Response.Write "<a href='fiveon.asp?Page=" & PageCounter & "'>" & _
PageCounter & "</a> "
Next

rsPage.Close
set rsPage = Nothing
End if

%>

</body>
</html>

cococure

10:15 pm on Aug 5, 2003 (gmt 0)

10+ Year Member



Try adding:

rsData.CacheSize = MyPageSize
rsData.CursorLocation = adUseServer 'or whatever the # is

TheDave

10:52 pm on Aug 5, 2003 (gmt 0)

10+ Year Member



You'll need to include the search terms in the next and prev button's querystrings,?page=1&search=word

socoolbrewster

6:04 pm on Aug 6, 2003 (gmt 0)

10+ Year Member



Firstly thanks to everyone above for offering their assistance to my problem. After many hours and days even of changing code and changing code, and with the help of my new best friend from another ASP forum we have cracked it.
Basically the problem with the script was mainly due to nature of the scripts function. As on passing the variable value from the form to the script on loading the next page(s) the value for the strSearchtxt variable became NULL hence causing the script not to proceed and display an EOF or BOF error. Therefore the script need to call that value from the form again.

Anyway will let the code speak for itself:

<%@ Language=VBScript %>
<HTML>
<HEAD>
<TITLE></TITLE>
</HEAD>
<BODY>

<%

Dim strSearchtxt

strSearchtxt = Request("cool") ' Get text to define SQL filter search
Dim Con
Dim rsPage
Dim Page
Dim RowCount
Dim PageCounter
Dim strQuote

'Get the Current page
Page = Request.QueryString("Page")

'If there is no page set it to page 1
If Page = "" then Page = 1

RowCount = 0

set con = server.CreateObject("ADODB.Connection")
Set rsPage = Server.CreateObject("ADODB.Recordset")

lsSQL = "SELECT * FROM review WHERE title = '" & strSearchtxt & "'"

Con.Open "brewster"

'Set the number of records in each page to 10
rsPage.PageSize = 5

'Open recordset
rsPage.Open lsSQL, Con,3,3

rsPage.AbsolutePage = cInt(Page)

if not rsPage.eof or rsPage.bof then

Response.Write "<TABLE>"

'Loop though each of the records and break out when we
'have reached the max for this page
Do while not rsPage.eof and RowCount < rsPage.PageSize

'Write out content
Response.Write "<tr><td>" & rsPage("title") & "</td></tr>"

rsPage.Movenext
RowCount = RowCount + 1
Loop

Response.Write "</TABLE><p>"

For PageCounter = 1 to rsPage.PageCount
%>

<a href="fiveon.asp?Page=<%=PageCounter%>&cool=<%=request("cool")%>"><%=PageCounter%></a>
<%
Next
rsPage.Close
set rsPage = Nothing
End if

%>
</body>
</html>

TheDave

11:01 pm on Aug 6, 2003 (gmt 0)

10+ Year Member



You should probably use: &cool=<%=Server.URLEncode(request("cool"))%>

Also, the & symbol, when used in html, even in a url, should be written as &amp;

ie

&amp;cool=<%=Server.URLEncode(request("cool"))%>

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / Microsoft IIS Web Server and ASP.NET 5:20 am Apr 28, 2026