<% Dim rsCustSearch__testvar rsCustSearch__testvar = "%" If (Request.form("search") <> "") Then rsCustSearch__testvar = Request.form("search") End If %> <% Dim rsCustSearch Dim rsCustSearch_numRows Const NumPerPage = 10 If Request.QueryString("page") = "" then CurrentPage = 1 'We're on the first page Else CurrentPage = CInt(Request.QueryString("page")) End If Set rsCustSearch = Server.CreateObject("ADODB.Recordset") rsCustSearch.CursorLocation = 3 rsCustSearch.CursorType = 3 'adOpenStatic rsCustSearch.ActiveConnection = MM_connTS_STRING rsCustSearch.Source = "SELECT ProductID, ProductName, Keywords, ProductPict FROM Products WHERE Keywords LIKE '%" + Replace(rsCustSearch__testvar, "'", "''") + "%' OR ProductName LIKE '%" + Replace(rsCustSearch__testvar, "'", "''") + "%'" rsCustSearch.LockType = 1 rsCustSearch.Open() If Not rsCustSearch.EOF Then rsCustSearch.MoveFirst rsCustSearch.PageSize = NumPerPage TotalPages = rsCustSearch.PageCount maxrecs = rsCustSearch.Pagesize 'Set the absolute (current) page rsCustSearch.AbsolutePage = CurrentPage End If Dim Count %> <% Dim Repeat1__numRows Dim Repeat1__index Repeat1__numRows = -1 Repeat1__index = 0 rsCustSearch_numRows = rsCustSearch_numRows + Repeat1__numRows %> <% ' *** Recordset Stats, Move To Record, and Go To Record: declare stats variables Dim rsCustSearch_total 'Dim rsCustSearch_first 'Dim rsCustSearch_last ' set the record count rsCustSearch_total = rsCustSearch.RecordCount ' set the number of rows displayed on this page 'If (rsCustSearch_numRows < 0) Then ' rsCustSearch_numRows = rsCustSearch_total 'Elseif (rsCustSearch_numRows = 0) Then ' rsCustSearch_numRows = 1 'End If ' set the first and last displayed record 'rsCustSearch_first = 1 'rsCustSearch_last = rsCustSearch_first + rsCustSearch_numRows - 1 ' if we have the correct record count, check the other stats 'If (rsCustSearch_total <> -1) Then ' If (rsCustSearch_first > rsCustSearch_total) Then ' rsCustSearch_first = rsCustSearch_total ' End If 'If (rsCustSearch_last > rsCustSearch_total) Then ' rsCustSearch_last = rsCustSearch_total ' End If 'If (rsCustSearch_numRows > rsCustSearch_total) Then ' rsCustSearch_numRows = rsCustSearch_total 'End If 'End If %> <% ' *** Recordset Stats: if we don't know the record count, manually count them If (rsCustSearch_total = -1) Then ' count the total records by iterating through the recordset rsCustSearch_total=0 While (Not rsCustSearch.EOF) rsCustSearch_total = rsCustSearch_total + 1 rsCustSearch.MoveNext Wend ' reset the cursor to the beginning 'If (rsCustSearch.CursorType > 0) Then ' rsCustSearch.MoveFirst 'Else ' rsCustSearch.Requery 'End If ' set the number of rows displayed on this page If (rsCustSearch_numRows < 0 Or rsCustSearch_numRows > rsCustSearch_total) Then rsCustSearch_numRows = rsCustSearch_total End If ' set the first and last displayed record rsCustSearch_first = 1 rsCustSearch_last = rsCustSearch_first + rsCustSearch_numRows - 1 If (rsCustSearch_first > rsCustSearch_total) Then rsCustSearch_first = rsCustSearch_total End If If (rsCustSearch_last > rsCustSearch_total) Then rsCustSearch_last = rsCustSearch_total End If End If %> <% Dim MM_paramName %> <% ' *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters Dim MM_keepNone Dim MM_keepURL Dim MM_keepForm Dim MM_keepBoth Dim MM_removeList Dim MM_item Dim MM_nextItem ' add the URL parameters to the MM_keepURL string For Each MM_item In Request.QueryString MM_nextItem = "&" & MM_item & "=" If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then MM_keepURL = MM_keepURL & MM_nextItem & Server.URLencode(Request.QueryString(MM_item)) End If Next ' add the Form variables to the MM_keepForm string For Each MM_item In Request.Form MM_nextItem = "&" & MM_item & "=" If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then MM_keepForm = MM_keepForm & MM_nextItem & Server.URLencode(Request.Form(MM_item)) End If Next ' create the Form + URL string and remove the intial '&' from each of the strings MM_keepBoth = MM_keepURL & MM_keepForm If (MM_keepBoth <> "") Then MM_keepBoth = Right(MM_keepBoth, Len(MM_keepBoth) - 1) End If If (MM_keepURL <> "") Then MM_keepURL = Right(MM_keepURL, Len(MM_keepURL) - 1) End If If (MM_keepForm <> "") Then MM_keepForm = Right(MM_keepForm, Len(MM_keepForm) - 1) End If ' a utility function used for adding additional parameters to these strings Function MM_joinChar(firstItem) If (firstItem <> "") Then MM_joinChar = "&" Else MM_joinChar = "" End If End Function %> Search Results
form>
<% 'Loop to display data on current page. Do While Not rsCustSearch.EOF and Count < rsCustSearch.PageSize OR howmanyrecs>=maxrecs %> <% rsCustSearch.MoveNext Count = Count + 1 Loop %>

Your Search returned <%=(rsCustSearch_total)%> results
Displaying page <%=CurrentPage%> of <%=TotalPages%>:   <% 'Display PREV page link, if appropriate If Not CurrentPage = 1 Then Response.Write "Prev | " Else Response.Write "Prev | " End If 'Display NEXT page link, if appropriate If Not CurrentPage = TotalPages Then Response.Write "Next" Else Response.Write "Next" End If %>
<%=(rsCustSearch.Fields.Item("ProductName").Value)%>
">View Item Details
">" border="0" width="100" height="100">

<% rsCustSearch.Close() Set rsCustSearch = Nothing %> =================================================== It is basicly losing the search recordset details when I click 'next' or 'prev' & I need someone to show me how to get round this! THANKYOU in advance for your help....... Joseph CharitiesOnline [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => CharitiesOnline [ip] => charitiesonline [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) --> problems paging a recordset search result - ASP Database

problems paging a recordset search result - ASP Database

Hello, I have set this script up to add paging to a search results page. Which on the first page works fine. I calculates how many pages there should be depending on the number of results returned from my search and how many records I have set it to display per page. All great so far, HOWEVER..... When you click the next link to see the next 10 results on the next page, it just dumps the search details and pulls up all the records in the table! So it finds 320 matching results, and then happily pages through them ...

  1. #1

    Default problems paging a recordset search result

    Hello,
    I have set this script up to add paging to a search results page. Which on
    the first page works fine. I calculates how many pages there should be
    depending on the number of results returned from my search and how many
    records I have set it to display per page. All great so far, HOWEVER.....

    When you click the next link to see the next 10 results on the next page, it
    just dumps the search details and pulls up all the records in the table! So
    it finds 320 matching results, and then happily pages through them 10
    records at a time, with both the next and prev links working!

    I need to find a way of using this paging system in conjunction with the
    search results, so if for example I search for "yellow" and I get 18
    matching results with the first 10 being displayed on the first page, & I
    then click next, I would like to see the last 8 results on the next page,
    with the option to click 'prev' and see the previous 10 again. What actually
    happens is that it shows me the first 10 matching results and says
    displaying page 1 of 2, I click the 'next' link and it suddenly says showing
    page 2 of 32 and starts listing all the records from the database starting
    at record number 11 !

    Please can anyone help me fix this?


    Here is my current code:

    ================================================== =
    <%LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
    <!--#include file="Connections/connTS.asp" -->
    <%
    Dim rsCustSearch__testvar
    rsCustSearch__testvar = "%"
    If (Request.form("search") <> "") Then
    rsCustSearch__testvar = Request.form("search")
    End If
    %>
    <%
    Dim rsCustSearch
    Dim rsCustSearch_numRows
    Const NumPerPage = 10

    If Request.QueryString("page") = "" then
    CurrentPage = 1 'We're on the first page
    Else
    CurrentPage = CInt(Request.QueryString("page"))
    End If

    Set rsCustSearch = Server.CreateObject("ADODB.Recordset")
    rsCustSearch.CursorLocation = 3
    rsCustSearch.CursorType = 3 'adOpenStatic
    rsCustSearch.ActiveConnection = MM_connTS_STRING
    rsCustSearch.Source = "SELECT ProductID, ProductName, Keywords, ProductPict
    FROM Products WHERE Keywords LIKE '%" + Replace(rsCustSearch__testvar, "'",
    "''") + "%' OR ProductName LIKE '%" + Replace(rsCustSearch__testvar, "'",
    "''") + "%'"

    rsCustSearch.LockType = 1
    rsCustSearch.Open()

    If Not rsCustSearch.EOF Then
    rsCustSearch.MoveFirst
    rsCustSearch.PageSize = NumPerPage
    TotalPages = rsCustSearch.PageCount
    maxrecs = rsCustSearch.Pagesize

    'Set the absolute (current) page
    rsCustSearch.AbsolutePage = CurrentPage
    End If

    Dim Count
    %>
    <%
    Dim Repeat1__numRows
    Dim Repeat1__index

    Repeat1__numRows = -1
    Repeat1__index = 0
    rsCustSearch_numRows = rsCustSearch_numRows + Repeat1__numRows
    %>
    <%
    ' *** Recordset Stats, Move To Record, and Go To Record: declare stats
    variables

    Dim rsCustSearch_total
    'Dim rsCustSearch_first
    'Dim rsCustSearch_last

    ' set the record count
    rsCustSearch_total = rsCustSearch.RecordCount

    ' set the number of rows displayed on this page
    'If (rsCustSearch_numRows < 0) Then
    ' rsCustSearch_numRows = rsCustSearch_total
    'Elseif (rsCustSearch_numRows = 0) Then
    ' rsCustSearch_numRows = 1
    'End If

    ' set the first and last displayed record
    'rsCustSearch_first = 1
    'rsCustSearch_last = rsCustSearch_first + rsCustSearch_numRows - 1

    ' if we have the correct record count, check the other stats
    'If (rsCustSearch_total <> -1) Then
    ' If (rsCustSearch_first > rsCustSearch_total) Then
    ' rsCustSearch_first = rsCustSearch_total
    ' End If
    'If (rsCustSearch_last > rsCustSearch_total) Then
    ' rsCustSearch_last = rsCustSearch_total
    ' End If
    'If (rsCustSearch_numRows > rsCustSearch_total) Then
    ' rsCustSearch_numRows = rsCustSearch_total
    'End If
    'End If
    %>

    <%
    ' *** Recordset Stats: if we don't know the record count, manually count
    them

    If (rsCustSearch_total = -1) Then

    ' count the total records by iterating through the recordset
    rsCustSearch_total=0
    While (Not rsCustSearch.EOF)
    rsCustSearch_total = rsCustSearch_total + 1
    rsCustSearch.MoveNext
    Wend

    ' reset the cursor to the beginning
    'If (rsCustSearch.CursorType > 0) Then
    ' rsCustSearch.MoveFirst
    'Else
    ' rsCustSearch.Requery
    'End If

    ' set the number of rows displayed on this page
    If (rsCustSearch_numRows < 0 Or rsCustSearch_numRows > rsCustSearch_total)
    Then
    rsCustSearch_numRows = rsCustSearch_total
    End If

    ' set the first and last displayed record
    rsCustSearch_first = 1
    rsCustSearch_last = rsCustSearch_first + rsCustSearch_numRows - 1

    If (rsCustSearch_first > rsCustSearch_total) Then
    rsCustSearch_first = rsCustSearch_total
    End If
    If (rsCustSearch_last > rsCustSearch_total) Then
    rsCustSearch_last = rsCustSearch_total
    End If

    End If
    %>
    <%
    Dim MM_paramName
    %>
    <%
    ' *** Go To Record and Move To Record: create strings for maintaining URL
    and Form parameters

    Dim MM_keepNone
    Dim MM_keepURL
    Dim MM_keepForm
    Dim MM_keepBoth

    Dim MM_removeList
    Dim MM_item
    Dim MM_nextItem



    ' add the URL parameters to the MM_keepURL string
    For Each MM_item In Request.QueryString
    MM_nextItem = "&" & MM_item & "="
    If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then
    MM_keepURL = MM_keepURL & MM_nextItem &
    Server.URLencode(Request.QueryString(MM_item))
    End If
    Next

    ' add the Form variables to the MM_keepForm string
    For Each MM_item In Request.Form
    MM_nextItem = "&" & MM_item & "="
    If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then
    MM_keepForm = MM_keepForm & MM_nextItem &
    Server.URLencode(Request.Form(MM_item))
    End If
    Next

    ' create the Form + URL string and remove the intial '&' from each of the
    strings
    MM_keepBoth = MM_keepURL & MM_keepForm
    If (MM_keepBoth <> "") Then
    MM_keepBoth = Right(MM_keepBoth, Len(MM_keepBoth) - 1)
    End If
    If (MM_keepURL <> "") Then
    MM_keepURL = Right(MM_keepURL, Len(MM_keepURL) - 1)
    End If
    If (MM_keepForm <> "") Then
    MM_keepForm = Right(MM_keepForm, Len(MM_keepForm) - 1)
    End If

    ' a utility function used for adding additional parameters to these strings
    Function MM_joinChar(firstItem)
    If (firstItem <> "") Then
    MM_joinChar = "&"
    Else
    MM_joinChar = ""
    End If
    End Function
    %>
    <html><!-- InstanceBegin template="/Templates/home.dwt"
    codeOutsideHTMLIsLocked="false" -->
    <head>
    <!-- InstanceBeginEditable name="doctitle" -->
    <title>Search Results</title>
    </head>

    <body bgcolor="#FFFFFF" text="#000000" leftmargin="0" topmargin="0"
    marginwidth="0" marginheight="0">
    <form action="SearchResults.asp" method="post" name="frmsearch"
    id="frmsearch">

    <input name="search" type="text" class="formelement"
    id="search">
    <input name="Submit" type="submit" class="formelement"
    value="Submit">form><div align="left"><!-- InstanceBeginEditable
    name="body" -->


    <table width="100%" border="0" cellspacing="0" cellpadding="0">
    <tr>
    <td colspan="2"><div align="center"><img src="images/search.jpg"><br>
    <font face="Arial, Helvetica, sans-serif"><strong>Your
    Search </strong><font size="3">returned <%=(rsCustSearch_total)%>
    results</font></font></div>
    <font size="2" face="Verdana">Displaying page <%=CurrentPage%>
    of <%=TotalPages%>:</font></b> &nbsp;
    <%
    'Display PREV page link, if appropriate
    If Not CurrentPage = 1 Then
    Response.Write "<a href='" & ScriptName & "?page=" & CurrentPage - 1 &
    "'>Prev</a> | "
    Else
    Response.Write "Prev | "
    End If

    'Display NEXT page link, if appropriate
    If Not CurrentPage = TotalPages Then
    Response.Write "<a href='" & ScriptName & "?page=" & CurrentPage + 1 &
    "'>Next</a>"
    Else
    Response.Write "Next"
    End If
    %></td>
    </tr>
    <%
    'Loop to display data on current page.
    Do While Not rsCustSearch.EOF and Count < rsCustSearch.PageSize OR
    howmanyrecs>=maxrecs
    %>
    <tr>
    <td width="49%"><div
    align="right"><b><%=(rsCustSearch.Fields.Item("Pro ductName").Value)%><br><A
    href="ProductDetails.asp?<%= MM_keepURL & MM_joinChar(MM_keepURL) &
    "ProductID=" & rsCustSearch.Fields.Item("ProductID").Value %>">View Item
    Details</a></div></td>
    <td width="51%"><A href="ProductDetails.asp?<%= MM_keepURL &
    MM_joinChar(MM_keepURL) & "ProductID=" &
    rsCustSearch.Fields.Item("ProductID").Value %>"><img
    src="http://www.bathchain.co.uk/images/<%=(rsCustSearch.Fields.Item("Product
    Pict").Value)%>" border="0" width="100" height="100"></A></td>
    </tr>
    <%

    rsCustSearch.MoveNext
    Count = Count + 1
    Loop
    %>
    </table>
    <!-- InstanceEndEditable --><br>
    </div>
    </body>
    <!-- InstanceEnd --></html>
    <%
    rsCustSearch.Close()
    Set rsCustSearch = Nothing
    %>
    ================================================== =

    It is basicly losing the search recordset details when I click 'next' or
    'prev' & I need someone to show me how to get round this!

    THANKYOU in advance for your help.......
    Joseph
    CharitiesOnline


    CharitiesOnline Guest

  2. #2

    Default Re: problems paging a recordset search result

    Hi i'm having the same problem and have search for many topics related to pagination but couldn't find any good solution for this. Do you manage to get an answer, can you share with me?! Appreciate that as i'm really frus with that problem!
    Unregistered Guest

Similar Threads

  1. Help Recordset paging
    By Miss Jo in forum Coldfusion - Getting Started
    Replies: 3
    Last Post: June 10th, 10:57 AM
  2. Recordset Paging
    By Atmosfera in forum Dreamweaver AppDev
    Replies: 0
    Last Post: May 16th, 03:46 PM
  3. recordset paging on search/results pages
    By markusallen in forum Dreamweaver AppDev
    Replies: 4
    Last Post: March 17th, 04:53 PM
  4. paging through recordset
    By Tom B in forum ASP Database
    Replies: 6
    Last Post: May 26th, 08:12 AM
  5. Replies: 0
    Last Post: November 7th, 12:56 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •