searching for pageing recordsets

gilgalbiblewheel · August 23rd, 2004, 01:24 PM

Is this right the way of including my checkboxes and stuff in the page links?

Code:

  ' first page
        ' we link to this page with Page parameter = 1
        Response.Write  "<A HREF=""" & SCRIPT_NAMES & _
                "?Keyword=" & Keyword & _
        "&Keywordb=" & Keywordb & _
        "&text_data=yes" & _
        "&optAction=" & _
        "&spoke=" & spoke & _
        "&book_spoke=" & _
        "&chapter_spoke=" & _
        "&verse_spoke=" & _
        "&book=" & _
        "&book_title=" & _
        "&chapter=" & _
        "&verse=" & _
        "&recordType=" & recordType & _
                "&Mode=" & MODE_RESULTS & _
                "&Page=" & 1 & _
                """>First Page</A>"
        Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;"

happygv · August 24th, 2004, 04:56 AM

Syntactically, I dont find any problem with that. But, looks like some of the querystring variables are not passed with its data(book_spoke, verse_spoke, chapter, book, book_title, chapter_spoke, verse, etc). Were those values been deliberately left out? Otherwise it looks fine.

_________________________
- Vijay G
Strive for Perfection

gilgalbiblewheel · August 24th, 2004, 01:31 PM

One of the problems is that I 've asked to give 20 records per page but the records DO start with an increment of 20 but then go all the way to the end. In other words the first is from #1 all the way to the end. The 2nd is from #21 all the way to the end...

Code:

Const RECORDS_PER_PAGE  = 20            ' Number of records per page

Dim nMode   ' Current Mode
'************************************************************************************
'* End of Declaration section
'************************************************************************************

'************************************************************************************
'* Main section
'************************************************************************************

' Find out what mode we are in
nMode = CLng(Request.QueryString("Mode"))

' Depending on our mode we will do different things
Select Case nMode

    Case MODE_RESULTS
        ' This is where all the results will show       
        call ShowResults()

    Case Else   ' This one is for MODE_DEFAULT or invalid modes all the same
        ' By default display the search form
        call ShowSearchForm()
End Select
    Dim nRecCount   ' Number of records found
    Dim nPageCount  ' Number of pages of records we have
    Dim nPage       ' Current page number

' Time to create and open recordset
    Set RS = Server.CreateObject("ADODB.Recordset")
    RS.CursorLocation = 3 ' adUseClient
    Response.Write sql
    RS.Open SQL, strConn ' adOpenKeyset CursorType

    ' Start outputing HTML
    call OutputPageHeader()

    ' Did we find anything?
    If Not RS.Eof Then
        ' Let's deal with our findings

        ' Get records count
        nRecCount = RS.RecordCount

        ' Tell recordset to split records in the pages of our size
        RS.PageSize = RECORDS_PER_PAGE

        ' How many pages we've got
        nPageCount = RS.PageCount

        ' Make sure that the Page parameter passed to us is within the range
        If nPage < 1 Or nPage > nPageCount Then
            ' Ops - bad page number
            ' let's fix it
            nPage = 1           
        End If

        ' Time to tell user what we've got so far
        Response.Write nRecCount & " records found matching """ & Keyword & """ """ & Keywordb & """ """ & Keywordc & """.<br>"
        Response.Write nPageCount & " page(s) of result(s).<br>"
        Response.Write "Current page is " & nPage & ".<p>"

        ' Give user some navigation
    ' first page
        ' we link to this page with Page parameter = 1
        Response.Write  "<A HREF=""" & SCRIPT_NAMES & _
                "?Keyword=" & Keyword & _
        "&Keywordb=" & Keywordb & _
        "&Keywordc=" & Keywordc & _
        "&text_data=yes" & _
        "&optAction=" & _
        "&spoke=" & spoke & _
        "&book_spoke=Book_Spoke" & _
        "&chapter_spoke=Chapter_Spoke" & _
        "&verse_spoke=Verse_Spoke" & _
        "&book=yes" & _
        "&book_title=yes" & _
        "&chapter=yes" & _
        "&verse=yes" & _
        "&recordType=" & recordType & _
                "&Mode=" & MODE_RESULTS & _
                "&Page=" & 1 & _
                """>First Page</A>"
        Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;"
    'http://127.0.0.1/amos2.asp?Keyword=judah&Keywordb=&text'_data=yes&optAction=o'n&spoke=&book_spoke=Book_Spoke&ch'apter_spoke=Chapter_Spoke&verse_spoke=Verse'_Spoke&book'=yes&book_title=yes&chapter=yes&verse=yes&recordType=&M'ode=2&Page'=2
        ' Previous Page
        ' we link to this page with Page parameter = Current Page - 1
        Response.Write  "<A HREF=""" & SCRIPT_NAMES & _
                "?Keyword=" & Keyword & _
        "&Keywordb=" & Keywordb & _
        "&Keywordc=" & Keywordc & _
        "&text_data=yes" & _
        "&optAction=" & _
        "&spoke=" & spoke & _
        "&book_spoke=Book_Spoke" & _
        "&chapter_spoke=Chapter_Spoke" & _
        "&verse_spoke=Verse_Spoke" & _
        "&book=yes" & _
        "&book_title=yes" & _
        "&chapter=yes" & _
        "&verse=yes" & _
        "&recordType=" & recordType & _
                "&Mode=" & MODE_RESULTS & _
                "&Page=" & nPage - 1 & _
                """>Previous Page</A>"
        Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;"

        ' Next Page
        ' we link to this page with Page parameter Current Page + 1
        Response.Write  "<A HREF=""" & SCRIPT_NAMES & _
                "?Keyword=" & Keyword & _
        "&Keywordb=" & Keywordb & _
        "&Keywordc=" & Keywordc & _
        "&text_data=yes" & _
        "&optAction=" & _
        "&spoke=" & spoke & _
        "&book_spoke=Book_Spoke" & _
        "&chapter_spoke=Chapter_Spoke" & _
        "&verse_spoke=Verse_Spoke" & _
        "&book=yes" & _
        "&book_title=yes" & _
        "&chapter=yes" & _
        "&verse=yes" & _
        "&recordType=" & recordType & _
                "&Mode=" & MODE_RESULTS & _
                "&Page=" & nPage + 1 & _
                """>Next Page</A>"
        Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;"

        ' Last Page
        ' we link to this page with Page parameter = nPageCount
        Response.Write  "<A HREF=""" & SCRIPT_NAMES & _
                "?Keyword=" & Keyword & _
        "&Keywordb=" & Keywordb & _
        "&Keywordc=" & Keywordc & _
        "&text_data=yes" & _
        "&optAction=" & _
        "&spoke=" & spoke & _
        "&book_spoke=Book_Spoke" & _
                "&chapter_spoke=Chapter_Spoke" & _
        "&verse_spoke=Verse_Spoke" & _
        "&book=yes" & _
        "&book_title=yes" & _
        "&chapter=yes" & _
        "&verse=yes" & _
        "&recordType=" & recordType & _
                "&Mode=" & MODE_RESULTS & _
                "&Page=" & nPageCount & _
                """>Last Page</A>"

        ' Start Results


        ' Position recordset to the page we want to see
        RS.AbsolutePage = nPage

        ' Let's output our records              
        ' Loop through records until it's a next page or End of Records

%>

gilgalbiblewheel · August 24th, 2004, 01:37 PM

I'm getting:

Quote:

quote:SELECT * FROM bible WHERE text_data LIKE '%lord%' AND text_data LIKE '%jesus%' AND text_data LIKE '%christ%' AND book LIKE '' AND book_title LIKE '%%' AND chapter LIKE '%%' AND verse LIKE '%%' AND book_spoke = '' AND chapter_spoke = '' AND verse_spoke = ''

As you see there are places where I haven't entered any data, and it's coming up in the query. I only want to get the result of what I've entered only.

Is it because of the AND? But then again if I change it to OR then I would have as results a little bit of this and a little bit of that. I want to narrow down result and not expand it!

gilgalbiblewheel · August 24th, 2004, 01:44 PM

my 3rd reply:
Now that I've added to the pages, when I press "search" the result is there.

Quote:

quote:SELECT * FROM bible WHERE text_data LIKE '%lord%' AND text_data LIKE '%jesus%' AND text_data LIKE '%christ%'

But when I press "next page" I get no results and this:

Quote:

quote:SELECT * FROM bible WHERE text_data LIKE '%lord%' AND text_data LIKE '%jesus%' AND text_data LIKE '%christ%' AND book LIKE '' AND book_title LIKE '%%' AND chapter LIKE '%%' AND verse LIKE '%%' AND book_spoke = '' AND chapter_spoke = '' AND verse_spoke = ''

happygv · September 3rd, 2004, 06:57 PM

You should be missing to pass the search criteria as querystring to the page when pressed "next"

_________________________
- Vijay G
Strive for Perfection

gilgalbiblewheel · September 3rd, 2004, 07:30 PM

what do you mean?

happygv · September 4th, 2004, 11:56 AM

Quote:

quote:But when I press "next page" I get no results and this:

SELECT * FROM bible WHERE text_data LIKE '%lord%' AND text_data LIKE '%jesus%' AND text_data LIKE '%christ%' AND book LIKE '' AND book_title LIKE '%%' AND chapter LIKE '%%' AND verse LIKE '%%' AND book_spoke = '' AND chapter_spoke = '' AND verse_spoke = ''

I meant this.

As there is nothing in the following where conditions.
book LIKE '' AND book_title LIKE '%%' AND chapter LIKE '%%' AND verse LIKE '%%' AND book_spoke = '' AND chapter_spoke = '' AND verse_spoke = ''

And why is the next page query different from the search query? Is that by design? check if that is right.

_________________________
- Vijay G
Strive for Perfection

kumiko · August 27th, 2006, 01:24 AM

dear happygv,
can u teach me how to pass the search criteria to the next page?thanx..