searching for pageing recordsets

gilgalbiblewheel · August 19th, 2004, 11:30 PM

Do you know any good codes on "pageing"?

happygv · August 20th, 2004, 07:49 AM

Search p2p.wrox for "paging recordset", you will get a lot of such, as it was discussed many times here.

_________________________
- Vijay G
Strive for Perfection

gilgalbiblewheel · August 20th, 2004, 09:23 AM

How about highlighting keyword searches?
Ok. Thanks. How about highlighting keyword searches

happygv · August 20th, 2004, 09:40 AM

Anyways you knew the keyword that was searched for. Just use a replace function on the DB column to replace the keyword with some different color. That is very easy and simple way of achieving it.

Anyways take a look at this link.
Keyword highlighting in ASP

Cheers!

_________________________
- Vijay G
Strive for Perfection

gilgalbiblewheel · August 20th, 2004, 05:05 PM

The checkboxes and every thing are working. Now the paging is an issue because the code that I started and the code within paging seem quite different.

One had

Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")

the other

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

What's the difference?

Anyways the error shows this:

Quote:

quote:Microsoft VBScript compilation error '800a03ea'

Quote:

Syntax error

/amos13.asp, line 88

Call Private Sub ShowResults()

The code is:

Code:

<% 
' This function will display the results of the search 
Private Sub ShowResults() 
    Dim strConn     ' Database connection string 
    Dim SQL         ' String that will have our SQL statments 
    Dim RSGlobalWeb ' Recordset object 
    Dim Keyword     ' Keyword for search 
    Dim nRecCount   ' Number of records found 
    Dim nPageCount  ' Number of pages of records we have 
    Dim nPage       ' Current page number 
    Dim iCounter 
    Dim iLoopCount 
    Dim aRecTypes 
    Dim spoke       ' For dropdown 

    ' Let's see what page are we looking at right now 
    nPage = CLng(Request.QueryString("Page")) 

    ' Let's see what user wants to search for today :) 
    Keyword = Trim(Request.QueryString("Keyword")) 
    spoke = Request.Querystring("spoke") 

    ' define our SQL statement 
    ' we will be looking for all the records in tblItem table 
    ' where ItemName contains our Keyword 
    ' do not forget to fix tick marks (single quotes) in our Keyword 
    SQL = "SELECT * FROM bible WHERE " 
    strConn = GetConnectionString() 



'Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")  
'dbGlobalWeb.Open(GetConnectionString)   taken out because I though it would be irrelevant  


iCounter = 0 

If   request.QueryString("book")="yes" then 

  SQL = SQL & "book LIKE '%" & Keyword & "%'" 

  iCounter = iCounter + 1 

end if 


If   request.QueryString("book_title")="yes" then 

  If iCounter > 0 Then 
    SQL = SQL & " OR " 
  End If 

  SQL = SQL & "book_title LIKE '%" & Keyword & "%'" 

  iCounter = iCounter + 1 

end if 

If   request.QueryString("chapter")="yes" then 

  If iCounter > 0 Then 
    SQL = SQL & " OR " 
  End If 

  SQL = SQL & "chapter LIKE '%" & Keyword & "%'" 

  iCounter = iCounter + 1 

end if 

If   request.QueryString("verse")="yes" then 

  If iCounter > 0 Then 
    SQL = SQL & " OR " 
  End If 

  SQL = SQL & "verse LIKE '%" & Keyword & "%'" 

  iCounter = iCounter + 1 

end if 


If   request.QueryString("text_data")="yes" then 

  If iCounter > 0 Then 
    SQL = SQL & " OR " 
  End If 

  SQL = SQL & "text_data LIKE '%" & Keyword & "%'" 

  iCounter = iCounter + 1 

end if 


If   request.QueryString("book_spoke")="Book_Spoke" then 

  If iCounter > 0 Then 
    SQL = SQL & " AND " 
  End If 

  SQL = SQL & "book_spoke = '" & spoke & "'" 

  iCounter = iCounter + 1 

end if 

If   request.QueryString("chapter_spoke")="Chapter_Spoke" then 

  If iCounter > 0 Then 
    SQL = SQL & " AND " 
  End If 

  SQL = SQL & "chapter_spoke = '" & spoke & "'" 

  iCounter = iCounter + 1 

end if 

If   request.QueryString("verse_spoke")="Verse_Spoke" then 

  If iCounter > 0 Then 
    SQL = SQL & " AND " 
  End If 

  SQL = SQL & "verse_spoke = '" & spoke & "'" 

  iCounter = iCounter + 1 

end if 

If Trim(Request.QueryString("recordType")) <> "" Then 


  aRecTypes = Split(Request.QueryString("recordType"), ",") 

  If IsArray(aRecTypes) Then 'This is a bit redundant, but it can't hurt 
    SQL = SQL & " AND (" 

    For iLoopCount = 0 To UBound(aRecTypes) 
      If iLoopCount <> 0 Then 
        SQL = SQL & " OR " 
      End If 

      SQL = SQL & " recordType = '" & trim(aRecTypes(iLoopCount)) & "'" 
    Next 
  End If 
  SQL = SQL & ")" 
End If 

' 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 & """.<br>" 
        Response.Write nPageCount & " pages of results.<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_NAME & _ 
                "?Keyword=" & Keyword & _ 
                "&Mode=" & MODE_RESULTS & _ 
                "&Page=" & 1 & _ 
                """>First Page</A>" 
        Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;" 

        ' Previous Page 
        ' we link to this page with Page parameter = Current Page - 1 
        Response.Write  "<A HREF=""" & SCRIPT_NAME & _ 
                "?Keyword=" & Keyword & _ 
                "&Mode=" & MODE_RESULTS & _ 
                "&Page=" & nPage - 1 & _ 
                """>Prev. 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_NAME & _ 
                "?Keyword=" & Keyword & _ 
                "&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_NAME & _ 
                "?Keyword=" & Keyword & _ 
                "&Mode=" & MODE_RESULTS & _ 
                "&Page=" & nPageCount & _ 
                """>Last Page</A>" 

        ' Start Results 
        Response.Write "<p><b>These are the results:</b><br>" & String(50,"-") 

        ' 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 

%>

.....End Sub at the end

happygv · August 21st, 2004, 05:19 AM

The difference between those two lines is... one creates a Connection object that is used to connect to the backend. And the other creates a recordset object that is used to populate the rows selected from database for further processing. Both are essential for your page, as you are dealing with displaying records from database.

What is that you are doing on line 88 ???
Call Private Sub ShowResults()
Are you trying to call the sub or trying to define the sub? Cannot do both together.

I would suggest you to read some ASP books for understanding the basics than developing pages from the code available on the net.

Cheers!

_________________________
- Vijay G
Strive for Perfection

gilgalbiblewheel · August 22nd, 2004, 03:18 AM

Calling the sub. Defining is way up:

Code:

    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

I am. But it's one thing to read and another thing to find what you're looking for.

happygv · August 22nd, 2004, 03:51 AM

So what is on line 88?

Microsoft VBScript compilation error '800a03ea'
Syntax error
/amos13.asp, line 88
Call Private Sub ShowResults()

_________________________
- Vijay G
Strive for Perfection

gilgalbiblewheel · August 22nd, 2004, 02:56 PM

It is in bold.

Code:

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

...

Code:

' This function will display the results of the search
Call Sub ShowResults()
    Dim strConn     ' Database connection string
    Dim SQL         ' String that will have our SQL statments
    Dim RSGlobalWeb ' Recordset object
    Dim Keyword     ' Keyword for search
    Dim nRecCount   ' Number of records found
    Dim nPageCount  ' Number of pages of records we have
    Dim nPage       ' Current page number
    Dim iCounter
    Dim iLoopCount
    Dim aRecTypes
    Dim spoke       ' For dropdown

    ' Let's see what page are we looking at right now
    nPage = CLng(Request.QueryString("Page"))

    ' Let's see what user wants to search for today :)
    Keyword = Trim(Request.QueryString("Keyword"))
    spoke = Request.Querystring("spoke")

    ' define our SQL statement
    ' we will be looking for all the records in tblItem table 
    ' where ItemName contains our Keyword
    ' do not forget to fix tick marks (single quotes) in our Keyword
    SQL = "SELECT * FROM bible WHERE "
    strConn = GetConnectionString()



'Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")  
'dbGlobalWeb.Open(GetConnectionString)

I don't know if this is right.

Code:

' 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

happygv · August 23rd, 2004, 05:10 AM

You got to remove the word CALL from line 88. As I believe you are defining the sub routine there, you shouldn't use the word call while doing so. It is optionally used only when you wish to Execute that.

To define
Sub MySub
...My code
...My code
End Sub

To call that
MySub
Or
Call MySub

Hope that explains.
Cheers!

_________________________
- Vijay G
Strive for Perfection