|
 |
asp_databases thread: Paging through results
Message #1 by "Grant I" <giswim1@a...> on Tue, 26 Jun 2001 17:04:56
|
|
OK boys and girls, another question :\ Now, I need to be able to page
through the results returned by the search. I put in the code to do that
and it sorta works. The DB will return the first page correctly.
However, if there is a second page, it looks like somehow the record set
is going away, not filled, dead, or something. I get this error:
----------------------------
ADODB.Field error '80020009'
Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.
?
---------------------------
Now I put in a statement that tests if it is at the end of the record set
right before where the fields are accessed and for some reason it is and
not where it is supposed to be. Anyone have any ideas why? Thanks in
advance!! My code's below...
------------
>-My Code-<
------------
<!--#include file="adovbs.inc"-->
<%
' Declare variables and open a connection to the database
Dim objConn, strConnection, Con
Dim objRS, strQuery
Dim ConnectionString
Dim strReq, searchAR
Const intPageSize = 10
Dim intCurrentPage, intTotalPages, intI
If Request.Form("Submit") <> "Previous" and Request.Form("Submit")
<> "Next" then
intCurrentPage = 1
Else
intCurrentPage = CInt(Request.Form("CurrentPage"))
Select Case Request.Form("Submit")
case "Previous"
intCurrentPage = intCurrentPage - 1
case "Next"
intCurrentPage = intCurrentPage + 1
End Select
End If
' /-----------------------------------------\
' |Read database information into record set|
' \-----------------------------------------/
strQuery = "SELECT * FROM rd_issues "
' Determine what conditionals to use in query
' -------------------------------------------
' Put in Where if necessary
if Request.Form("status")<>"0" or Request.Form("type")<>"0" or Request.Form
("query") <> "" then
strQuery = strQuery & "WHERE ("
end if
if Request.Form("status")<> "0" then
strQuery = strQuery & "((rd_issues.Status)= '" & Request.Form
("status") & "')"
end if
' Add the category to the query if user does not choose all
if Request.Form("type") <> "0" then
if Request.Form("status") <> "0" then
strQuery = strQuery & " AND"
end if
strQuery = strQuery & " (rd_issues.Category)=('"
strQuery = strQuery & Request.Form("type") & "')"
end if
' Add user-supplied search terms if search is not empty
if Request.Form("query") <> "" then
'Parse the search string into an array so each term will be
seached for
searchAR = SPLIT(Request.Form("query"))
dim i
for i=0 to UBOUND(searchAR)
if Request.Form ("type") <> "0" or Request.Form ("status") <> "0"
then
strQuery = strQuery & " AND"
end if
strQuery = strQuery & " ((rd_issues.Description) LIKE ('%"
strQuery = strQuery & searchAR(i)
strQuery = strQuery & "%'))"
next
end if
' Organize data by date
if Request.Form("status")<>"0" or Request.Form("type")<>"0" or Request.Form
("query") <> "" then
strQuery = strQuery & ")"
end if
strQuery = strQuery & " ORDER BY (rd_issues.Date_Reported) DESC;"
' Open Database, Run query and store it in record set objRS
' Response.Write (strQuery)
Set objConn = Server.CreateObject("ADODB.Connection")
ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);"
ConnectionString = ConnectionString & "DBQ=" & Server.MapPath
("rd_issues.mdb")
objConn.Open ConnectionString
set objRS = Server.CreateObject ("ADODB.Recordset")
objRS.CursorLocation = adUseClient
objRS.CursorType = adOpenStatic
objRS.CacheSize = intPageSize
objRS.Open strQuery, objConn, , , adCmdText
objRS.PageSize = intPageSize
If Not(objRS.EOF) then objRS.AbsolutePage = intCurrentPage
intTotalPages = objRS.PageCount
' Print out table with correct number of columns
' ----------------------------------------------
%>
<table border=1 align=center width="100%">
<TR>
<td align=middle width="5%"><font size=-
1><b>ID</th>
<td align=middle width="10%"><font size=-1><b>Date
Submitted</th>
<td align=middle width="10%"><font size=-
1><b>Type</th>
<td align=middle width="40%"><font size=-
1><b>Description</th>
<td align=middle width="10%"><font size=-
1><b>Status</th>
<td align=middle width="10%"><font size=-
1><b>Priority</th>
<td align=middle width="5%"><font size=-
1><b>Completion Date</th>
<td align=middle width="10%"><font size=-
1><b>Assigned To</th>
</tr></font>
<%for intI = 1 to objRS.PageSize
%>
<tr>
<td width="5%" align=center><font size=-
2><%Response.Write objRS("ID")%></font></td>
<td width="10%" align=center> <font size=-
2><%Response.Write objRS("Date_Reported")%> by
<%
Response.Write objRS("Reported_By")%></font></td>
<td width="10%" align=center><font size=-
2><%Response.Write objRS("Category")%> <%Response.Write objRS("Type")%
>
<td width="35%"><font size=-2><%
Response.Write objRS("Description")%></font></td>
<td width="10%" align=center><font size=-
2><%Response.Write objRS("Status")%>
<td width="5%" align=center><font size=-
2><%Response.Write objRS("Priority")%>
<td width="5%" align=center><font size=-
2><%Response.Write objRS("CompDate")%></FONT></td>
<td width="35%" align=center><font size=-
2><%Response.Write objRS("PersonWorking")%> </font></td>
</tr>
<%objRS.MoveNext%>
<%if objRS.EOF then Exit for
next
objRS.Close : objConn.Close
Set objRS = nothing : set objConn = nothing
%>
</table><br>
Page <%= intCurrentPage %> of <%= intTotalPages %><p>
<form action="<%= Request.ServerVariables("SCRIPT_NAME") %>" method="POST">
<input type="Hidden" Name="CurrentPage" value="<%= intCurrentPage%>">
<%
If intCurrentPage > 1 then %>
<input type="Submit" name="Submit" value="Previous">
<% End If
If intCurrentPage <> intTotalPages then %>
<input type="Submit" name="Submit" value="Next">
<%end if%>
Message #2 by "Grant I" <giswim1@a...> on Tue, 26 Jun 2001 20:51:30
|
|
Wouldn't ya know it? Figured this one out on my own too. In case any of
you were stumped and care, I realized that since this form was calling
itself, when it did that, it was looking for input called type, status,
and query. Since it was calling itself, it wasn't sending this input.
So, I just added 3 hidden input fields at the end submitting the input it
got from the previous page (basically it keeps passing the search terms on
each time). Thanks to anyone who took some time to look at this. It took
me quite a while to figure it out. :)
> OK boys and girls, another question :\ Now, I need to be able to page
> through the results returned by the search. I put in the code to do
that
> and it sorta works. The DB will return the first page correctly.
> However, if there is a second page, it looks like somehow the record set
> is going away, not filled, dead, or something. I get this error:
> ----------------------------
> ADODB.Field error '80020009'
>
> Either BOF or EOF is True, or the current record has been deleted.
> Requested operation requires a current record.
>
> ?
> ---------------------------
>
> Now I put in a statement that tests if it is at the end of the record
set
> right before where the fields are accessed and for some reason it is and
> not where it is supposed to be. Anyone have any ideas why? Thanks in
> advance!! My code's below...
>
> ------------
> >-My Code-<
> ------------
>
> <!--#include file="adovbs.inc"-->
> <%
> ' Declare variables and open a connection to the database
> Dim objConn, strConnection, Con
> Dim objRS, strQuery
> Dim ConnectionString
> Dim strReq, searchAR
>
> Const intPageSize = 10
> Dim intCurrentPage, intTotalPages, intI
>
> If Request.Form("Submit") <> "Previous" and Request.Form("Submit")
> <> "Next" then
> intCurrentPage = 1
> Else
> intCurrentPage = CInt(Request.Form("CurrentPage"))
> Select Case Request.Form("Submit")
> case "Previous"
> intCurrentPage = intCurrentPage - 1
> case "Next"
> intCurrentPage = intCurrentPage + 1
> End Select
> End If
>
>
>
> ' /-----------------------------------------\
> ' |Read database information into record set|
> ' \-----------------------------------------/
> strQuery = "SELECT * FROM rd_issues "
>
> ' Determine what conditionals to use in query
> ' -------------------------------------------
>
> ' Put in Where if necessary
> if Request.Form("status")<>"0" or Request.Form("type")<>"0" or
Request.Form
> ("query") <> "" then
> strQuery = strQuery & "WHERE ("
> end if
> if Request.Form("status")<> "0" then
> strQuery = strQuery & "((rd_issues.Status)= '" & Request.Form
> ("status") & "')"
> end if
> ' Add the category to the query if user does not choose all
> if Request.Form("type") <> "0" then
> if Request.Form("status") <> "0" then
> strQuery = strQuery & " AND"
> end if
> strQuery = strQuery & " (rd_issues.Category)=('"
> strQuery = strQuery & Request.Form("type") & "')"
> end if
>
> ' Add user-supplied search terms if search is not empty
> if Request.Form("query") <> "" then
> 'Parse the search string into an array so each term will be
> seached for
> searchAR = SPLIT(Request.Form("query"))
> dim i
> for i=0 to UBOUND(searchAR)
> if Request.Form ("type") <> "0" or Request.Form ("status") <> "0"
> then
> strQuery = strQuery & " AND"
> end if
> strQuery = strQuery & " ((rd_issues.Description) LIKE ('%"
> strQuery = strQuery & searchAR(i)
> strQuery = strQuery & "%'))"
> next
> end if
>
> ' Organize data by date
> if Request.Form("status")<>"0" or Request.Form("type")<>"0" or
Request.Form
> ("query") <> "" then
> strQuery = strQuery & ")"
> end if
> strQuery = strQuery & " ORDER BY (rd_issues.Date_Reported) DESC;"
> ' Open Database, Run query and store it in record set objRS
> ' Response.Write (strQuery)
> Set objConn = Server.CreateObject("ADODB.Connection")
>
> ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);"
> ConnectionString = ConnectionString & "DBQ=" & Server.MapPath
> ("rd_issues.mdb")
> objConn.Open ConnectionString
>
> set objRS = Server.CreateObject ("ADODB.Recordset")
> objRS.CursorLocation = adUseClient
> objRS.CursorType = adOpenStatic
> objRS.CacheSize = intPageSize
>
> objRS.Open strQuery, objConn, , , adCmdText
> objRS.PageSize = intPageSize
> If Not(objRS.EOF) then objRS.AbsolutePage = intCurrentPage
> intTotalPages = objRS.PageCount
>
> ' Print out table with correct number of columns
> ' ----------------------------------------------
>
> %>
> <table border=1 align=center width="100%">
> <TR>
> <td align=middle width="5%"><font size=-
> 1><b>ID</th>
> <td align=middle width="10%"><font size=-1><b>Date
> Submitted</th>
> <td align=middle width="10%"><font size=-
> 1><b>Type</th>
> <td align=middle width="40%"><font size=-
> 1><b>Description</th>
> <td align=middle width="10%"><font size=-
> 1><b>Status</th>
> <td align=middle width="10%"><font size=-
> 1><b>Priority</th>
> <td align=middle width="5%"><font size=-
> 1><b>Completion Date</th>
> <td align=middle width="10%"><font size=-
> 1><b>Assigned To</th>
> </tr></font>
>
> <%for intI = 1 to objRS.PageSize
> %>
> <tr>
> <td width="5%" align=center><font size=-
> 2><%Response.Write objRS("ID")%></font></td>
> <td width="10%" align=center> <font size=-
> 2><%Response.Write objRS("Date_Reported")%> by
>
> <%
> Response.Write objRS("Reported_By")%></font></td>
> <td width="10%" align=center><font size=-
> 2><%Response.Write objRS("Category")%> <%Response.Write objRS
("Type")%
> >
> <td width="35%"><font size=-2><%
> Response.Write objRS("Description")%></font></td>
> <td width="10%" align=center><font size=-
> 2><%Response.Write objRS("Status")%>
> <td width="5%" align=center><font size=-
> 2><%Response.Write objRS("Priority")%>
> <td width="5%" align=center><font size=-
> 2><%Response.Write objRS("CompDate")%></FONT></td>
> <td width="35%" align=center><font size=-
> 2><%Response.Write objRS("PersonWorking")%> </font></td>
> </tr>
> <%objRS.MoveNext%>
> <%if objRS.EOF then Exit for
> next
> objRS.Close : objConn.Close
> Set objRS = nothing : set objConn = nothing
> %>
> </table><br>
> Page <%= intCurrentPage %> of <%= intTotalPages %><p>
> <form action="<%= Request.ServerVariables("SCRIPT_NAME") %>"
method="POST">
> <input type="Hidden" Name="CurrentPage" value="<%= intCurrentPage%>">
> <%
> If intCurrentPage > 1 then %>
> <input type="Submit" name="Submit" value="Previous">
> <% End If
> If intCurrentPage <> intTotalPages then %>
> <input type="Submit" name="Submit" value="Next">
> <%end if%>
Message #3 by "Ken Schaefer" <ken@a...> on Thu, 28 Jun 2001 13:39:00 +1000
|
|
Check to make sure you are passing the current page + search criteria across
from page to page...
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Grant I" <giswim1@a...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, June 26, 2001 5:04 PM
Subject: [asp_databases] Paging through results
: OK boys and girls, another question :\ Now, I need to be able to page
: through the results returned by the search. I put in the code to do that
: and it sorta works. The DB will return the first page correctly.
: However, if there is a second page, it looks like somehow the record set
: is going away, not filled, dead, or something. I get this error:
: ----------------------------
: ADODB.Field error '80020009'
:
: Either BOF or EOF is True, or the current record has been deleted.
: Requested operation requires a current record.
:
: ?
: ---------------------------
<lots of code snipped for your viewing comfort>
|
|
|
View Cart
