Paging won't quite page problem

[Fred]

Hi,
I'm trying to get this script to run which it almost does but
it won't display the records for the last page i.e. page 2 of 2 or 3 of 3
etc
with error: Either BOF or EOF is True, or the current record has been
deleted. Requested operation requires a current record.
If someone could have a look at it at tell me what the problem is I would be
most appreciative.
I'm not new to ASP I just still can't do it very well.

Regards,

Fred (Ireland)

--Script--

<%' Paging Code
Dim strsubcat
if Request.Querystring("subcat") = "" then
strsubcat = "Accountants"
else
strsubcat = Request("subcat")
end if

Dim iPageSize 'Size of pages (Number of Records)
Dim iPageCount 'Number of Pages
Dim iPageCurrent 'The page we want to show
Dim strOrderBy 'A fake parameter used to illustrate passing them
Dim strSQL 'SQL command to execute
Dim objPagingConn 'The ADODB connection object
Dim objPagingRS 'The ADODB recordset object
Dim iRecordsShown 'Loop controller for displaying just iPageSize records
Dim I

iPageSize = 6

If Request.QueryString("page") = "" Then 'if page is value blank display as
1
iPageCurrent = 1
Else
iPageCurrent = CInt(Request.QueryString("page")) 'else use posted value as
current page
End If

strSQL = "SELECT * FROM Master WHERE Subcat LIKE '" & strsubcat & "' " &_
" OR AltST1 LIKE '" & strsubcat & "' " &_
" OR AltST2 LIKE '" & strsubcat & "' ORDER BY Name ASC" 'select garbage from
DB

Set objPagingConn = Server.CreateObject("ADODB.Connection") 'open connection
objPagingConn.Open strConnect

Set objPagingRS = Server.CreateObject("ADODB.Recordset") ' create set of
records
objPagingRS.PageSize = iPageSize ' set size of pages to (6) records
objPagingRS.CacheSize = iPageSize ' stops rs from getting more than 6

objPagingRS.Open strSQL, objPagingConn, adOpenStatic, adLockReadOnly,
adCmdText

' Find out how many pages...
iPageCount = objPagingRS.PageCount

' If the request page falls outside the acceptable range,
' give them the closest match (1 or max)
If iPageCurrent > iPageCount Then iPageCurrent = iPageCount
If iPageCurrent < 1 Then iPageCurrent = 1

' Check page count to prevent bombing when zero results are returned!
If iPageCount = 0 Then
Response.Write "Sorry, there are currently no records in this section"
Else
' Move to the selected page
objPagingRS.AbsolutePage = iPageCurrent

' Start output with a page x of n line 1 of 2 / 2 of 2 bit
%>
<span class="h1_red">Page <strong><%= iPageCurrent %></strong> of
<strong><%= iPageCount %></strong></span> </p>
<%
' Continue with a title row in our table
Response.Write "<table border=""0"">" & vbCrLf

' Loop through our records and ouput 1 row per record
iRecordsShown = 0
Do While iRecordsShown < iPageSize

%>
</p>
<td>
<span class="h1"><%= objPagingRS("Name") %></span><br>

<% if not objPagingRS("Address") = " " Then
Response.Write objPagingRS("Address") & ", "
End If %>

<% If not objPagingRS("Street") = " " Then
Response.Write objPagingRS("Street") & ", "
End If %>
<%= objPagingRS("Town") %>, Co. <%= objPagingRS("county") %><br><P></P>


<%
Response.Write "</td>" & vbCrLf
Response.Write vbTab & "</tr>" & vbCrLf

' Increment the number of records we've shown
iRecordsShown = iRecordsShown + 1
' Can't forget to move to the next record!
objPagingRS.MoveNext
Loop
' All done - close table
Response.Write "</table>" & vbCrLf
End If

' Close DB objects and free variables



' Show "previous" and "next" page links which pass the page to view
' and any parameters needed to rebuild the query. You could just as
' easily use a form but you'll need to change the lines that read
' the info back in at the top of the script.
If iPageCurrent > 1 Then
%> <a href="paging_debugger.asp?subcat=<%= objpagingRS("subcat")
%>&page=<%= iPageCurrent - 1 %>&order=<%= Server.URLEncode(strOrderBy) %>"
class="orangenav">[&lt;&lt;
Prev]</a> <%
End If

' You can also show page numbers:
For I = 1 To iPageCount
If I = iPageCurrent Then
%>
<span class="red">
<%= I %>
</span>
<%
Else
%> <a href="paging_debugger.asp?subcat=<%= objpagingRS("subcat")
%>&page=<%= I %>" class="orangenav"><%= I %></a> <%
End If
Next 'I

If iPageCurrent < iPageCount Then %>
<a href="paging_debugger.asp?subcat=<%= objpagingRS("subcat") %>&page=<%=
iPageCurrent + 1 %>" class="orangenav">[Next
&gt;&gt;]</a>
<% End If

' END RUNTIME CODE %>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004

[Aaron Bertrand - MVP]

[url]http://www.aspfaq.com/2120[/url]

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"Fred" <someone@microsoft.com> wrote in message
news:ap8Ub.1413$rb.55000@news.indigo.ie...

> Hi,
> I'm trying to get this script to run which it almost does but
> it won't display the records for the last page i.e. page 2 of 2 or 3 of 3
> etc
> with error: Either BOF or EOF is True, or the current record has been
> deleted. Requested operation requires a current record.
> If someone could have a look at it at tell me what the problem is I would

be

> most appreciative.
> I'm not new to ASP I just still can't do it very well.
>
> Regards,
>
> Fred (Ireland)
>
> --Script--
>
> <%' Paging Code
> Dim strsubcat
> if Request.Querystring("subcat") = "" then
> strsubcat = "Accountants"
> else
> strsubcat = Request("subcat")
> end if
>
> Dim iPageSize 'Size of pages (Number of Records)
> Dim iPageCount 'Number of Pages
> Dim iPageCurrent 'The page we want to show
> Dim strOrderBy 'A fake parameter used to illustrate passing them
> Dim strSQL 'SQL command to execute
> Dim objPagingConn 'The ADODB connection object
> Dim objPagingRS 'The ADODB recordset object
> Dim iRecordsShown 'Loop controller for displaying just iPageSize records
> Dim I
>
> iPageSize = 6
>
> If Request.QueryString("page") = "" Then 'if page is value blank display

as

> 1
> iPageCurrent = 1
> Else
> iPageCurrent = CInt(Request.QueryString("page")) 'else use posted value

as

> current page
> End If
>
> strSQL = "SELECT * FROM Master WHERE Subcat LIKE '" & strsubcat & "' " &_
> " OR AltST1 LIKE '" & strsubcat & "' " &_
> " OR AltST2 LIKE '" & strsubcat & "' ORDER BY Name ASC" 'select garbage

from

> DB
>
> Set objPagingConn = Server.CreateObject("ADODB.Connection") 'open

connection

> objPagingConn.Open strConnect
>
> Set objPagingRS = Server.CreateObject("ADODB.Recordset") ' create set of
> records
> objPagingRS.PageSize = iPageSize ' set size of pages to (6) records
> objPagingRS.CacheSize = iPageSize ' stops rs from getting more than 6
>
> objPagingRS.Open strSQL, objPagingConn, adOpenStatic, adLockReadOnly,
> adCmdText
>
> ' Find out how many pages...
> iPageCount = objPagingRS.PageCount
>
> ' If the request page falls outside the acceptable range,
> ' give them the closest match (1 or max)
> If iPageCurrent > iPageCount Then iPageCurrent = iPageCount
> If iPageCurrent < 1 Then iPageCurrent = 1
>
> ' Check page count to prevent bombing when zero results are returned!
> If iPageCount = 0 Then
> Response.Write "Sorry, there are currently no records in this section"
> Else
> ' Move to the selected page
> objPagingRS.AbsolutePage = iPageCurrent
>
> ' Start output with a page x of n line 1 of 2 / 2 of 2 bit
> %>
> <span class="h1_red">Page <strong><%= iPageCurrent %></strong> of
> <strong><%= iPageCount %></strong></span> </p>
> <%
> ' Continue with a title row in our table
> Response.Write "<table border=""0"">" & vbCrLf
>
> ' Loop through our records and ouput 1 row per record
> iRecordsShown = 0
> Do While iRecordsShown < iPageSize
>
> %>
> </p>
> <td>
> <span class="h1"><%= objPagingRS("Name") %></span><br>
>
> <% if not objPagingRS("Address") = " " Then
> Response.Write objPagingRS("Address") & ", "
> End If %>
>
> <% If not objPagingRS("Street") = " " Then
> Response.Write objPagingRS("Street") & ", "
> End If %>
> <%= objPagingRS("Town") %>, Co. <%= objPagingRS("county") %><br><P></P>
>
>
> <%
> Response.Write "</td>" & vbCrLf
> Response.Write vbTab & "</tr>" & vbCrLf
>
> ' Increment the number of records we've shown
> iRecordsShown = iRecordsShown + 1
> ' Can't forget to move to the next record!
> objPagingRS.MoveNext
> Loop
> ' All done - close table
> Response.Write "</table>" & vbCrLf
> End If
>
> ' Close DB objects and free variables
>
>
>
> ' Show "previous" and "next" page links which pass the page to view
> ' and any parameters needed to rebuild the query. You could just as
> ' easily use a form but you'll need to change the lines that read
> ' the info back in at the top of the script.
> If iPageCurrent > 1 Then
> %> <a href="paging_debugger.asp?subcat=<%= objpagingRS("subcat")
> %>&page=<%= iPageCurrent - 1 %>&order=<%= Server.URLEncode(strOrderBy) %>"
> class="orangenav">[&lt;&lt;
> Prev]</a> <%
> End If
>
> ' You can also show page numbers:
> For I = 1 To iPageCount
> If I = iPageCurrent Then
> %>
> <span class="red">
> <%= I %>
> </span>
> <%
> Else
> %> <a href="paging_debugger.asp?subcat=<%= objpagingRS("subcat")
> %>&page=<%= I %>" class="orangenav"><%= I %></a> <%
> End If
> Next 'I
>
> If iPageCurrent < iPageCount Then %>
> <a href="paging_debugger.asp?subcat=<%= objpagingRS("subcat") %>&page=<%=
> iPageCurrent + 1 %>" class="orangenav">[Next
> &gt;&gt;]</a>
> <% End If
>
> ' END RUNTIME CODE %>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).
> Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004
>
>

[Roland Hall]

"Fred" wrote:
: I'm trying to get this script to run which it almost does but
: it won't display the records for the last page i.e. page 2 of 2 or 3 of 3
: etc
: with error: Either BOF or EOF is True, or the current record has been
: deleted. Requested operation requires a current record.
: If someone could have a look at it at tell me what the problem is I would
be
: most appreciative.
: I'm not new to ASP I just still can't do it very well.

Fred...

If your records are stored with 0 being a valid record, instead of 1 being
the first record and you use 0 index in a loop, then you need to include the
record since you're using DO WHILE...

Try changing:

Do While iRecordsShown < iPageSize

To:

Do While iRecordsShown <= iPageSize

Or:

Do While iRecordsShown < iPageSize + 1

However, if you're getting all of the records and you're just not display
them with your next loop...

For I = 1 To iPageCount
If I = iPageCurrent Then
%>
<span class="red">
<%= I %>
</span>
<%
Else
%> <a href="paging_debugger.asp?subcat=<%= objpagingRS("subcat")
%>&page=<%= I %>" class="orangenav"><%= I %></a> <%
End If
Next 'I

If iPageCurrent < iPageCount Then %>
<a href="paging_debugger.asp?subcat=<%= objpagingRS("subcat") %>&page=<%=
iPageCurrent + 1 %>" class="orangenav">[Next
&gt;&gt;]</a>
<% End If

....then it is because you're indexing at 1 instead of zero.

Try changing:

For I = 1 To iPageCount
..
..
..
If iPageCurrent < iPageCount Then %>

To:

For I = 1 To iPageCount + 1
..
..
..
If iPageCurrent < iPageCount + 1 Then %>

I looked through this quickly and didn't test but if you're not getting the
last record, either you're not reading it, due to a limitation at the upper
boundary of your loop or you're not displaying it with an upper boundary
limitation. You cannot start with zero in one loop and 1 in another and
expect to see all records.

HTH...


--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - [url]http://www.microsoft.com/technet/scriptcenter/[/url]
WSH 5.6 Documentation - [url]http://msdn.microsoft.com/downloads/list/webdev.asp[/url]
MSDN Library - [url]http://msdn.microsoft.com/library/default.asp[/url]

[Fred]

Thanks for your help have got it working,

Fred

"Roland Hall" <nobody@nowhere> wrote in message
news:#MmAWB06DHA.1556@tk2msftngp13.phx.gbl...

> "Fred" wrote:
> : I'm trying to get this script to run which it almost does but
> : it won't display the records for the last page i.e. page 2 of 2 or 3 of

3

> : etc
> : with error: Either BOF or EOF is True, or the current record has been
> : deleted. Requested operation requires a current record.
> : If someone could have a look at it at tell me what the problem is I

would

> be
> : most appreciative.
> : I'm not new to ASP I just still can't do it very well.
>
> Fred...
>
> If your records are stored with 0 being a valid record, instead of 1 being
> the first record and you use 0 index in a loop, then you need to include

the

> record since you're using DO WHILE...
>
> Try changing:
>
> Do While iRecordsShown < iPageSize
>
> To:
>
> Do While iRecordsShown <= iPageSize
>
> Or:
>
> Do While iRecordsShown < iPageSize + 1
>
> However, if you're getting all of the records and you're just not display
> them with your next loop...
>
> For I = 1 To iPageCount
> If I = iPageCurrent Then
> %>
> <span class="red">
> <%= I %>
> </span>
> <%
> Else
> %> <a href="paging_debugger.asp?subcat=<%= objpagingRS("subcat")
> %>&page=<%= I %>" class="orangenav"><%= I %></a> <%
> End If
> Next 'I
>
> If iPageCurrent < iPageCount Then %>
> <a href="paging_debugger.asp?subcat=<%= objpagingRS("subcat") %>&page=<%=
> iPageCurrent + 1 %>" class="orangenav">[Next
> &gt;&gt;]</a>
> <% End If
>
> ...then it is because you're indexing at 1 instead of zero.
>
> Try changing:
>
> For I = 1 To iPageCount
> .
> .
> .
> If iPageCurrent < iPageCount Then %>
>
> To:
>
> For I = 1 To iPageCount + 1
> .
> .
> .
> If iPageCurrent < iPageCount + 1 Then %>
>
> I looked through this quickly and didn't test but if you're not getting

the

> last record, either you're not reading it, due to a limitation at the

upper

> boundary of your loop or you're not displaying it with an upper boundary
> limitation. You cannot start with zero in one loop and 1 in another and
> expect to see all records.
>
> HTH...
>
>
> --
> Roland Hall
> /* This information is distributed in the hope that it will be useful, but
> without any warranty; without even the implied warranty of merchantability
> or fitness for a particular purpose. */
> Technet Script Center - [url]http://www.microsoft.com/technet/scriptcenter/[/url]
> WSH 5.6 Documentation -

[url]http://msdn.microsoft.com/downloads/list/webdev.asp[/url]

> MSDN Library - [url]http://msdn.microsoft.com/library/default.asp[/url]
>
>

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004

[Roland Hall]

"Fred" wrote:
: Thanks for your help have got it working,

You're welcome. That's good to hear.

Roland