Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Posts
    494

    Unanswered: how to count the database search results of record

    Hi,
    I would like to insert a search engine result count (like google) to display 10 per page or 20 or 50...

    the asp code I have is:

    <%@ LANGUAGE="VBSCRIPT" %>
    <html>
    <head>
    <TITLE>amos2.asp</TITLE>
    </head>

    <body>
    <%
    SqlBible = "SELECT * FROM amos WHERE "

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

    dim mySearch, iCounter
    mySearch=Request.QueryString("mySearch")
    iCounter = 0

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

    SqlBible = SqlBible & "book LIKE '%" & mySearch & "%'"

    iCounter = iCounter + 1

    end if

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

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

    SqlBible = SqlBible & "spoke LIKE '%" & mySearch & "%'"

    iCounter = iCounter + 1

    end if

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

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

    SqlBible = SqlBible & "book_title LIKE '%" & mySearch & "%'"

    iCounter = iCounter + 1

    end if

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

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

    SqlBible = SqlBible & "chapter LIKE '%" & mySearch & "%'"

    iCounter = iCounter + 1

    end if

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

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

    SqlBible = SqlBible & "verse LIKE '%" & mySearch & "%'"

    iCounter = iCounter + 1

    end if

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

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

    SqlBible = SqlBible & "text_data LIKE '%" & mySearch & "%'"

    iCounter = iCounter + 1

    end if

    Set rsGlobalWeb = Server.CreateObject("ADODB.Recordset")
    rsGlobalWeb.Open SqlBible, dbGlobalWeb, 3%>

    <%
    If rsGlobalWeb.BOF and rsGlobalWeb.EOF Then%>

    <h2 align="center">We did not find a match!</h2>
    <%Else%>


    <%If Not rsGlobalWeb.BOF Then%>

    <h2>These are the results:</h2>

    <table BORDER="0" width="100%" cellpadding="3">
    <tr>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Book </font></th>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Spoke </font></th>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Book Title </font></th>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Chapter </font></th>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Verse </font></th>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Text </font></th>
    </tr>
    <%
    Do While Not rsGlobalWeb.EOF
    %>
    <tr>
    <td><%=rsGlobalWeb("book")%>#32
    </td>
    <td><%=rsGlobalWeb("spoke")%>
    </td>
    <td><%=rsGlobalWeb("book_title")%>
    </td>
    <td><%=rsGlobalWeb("chapter")%>
    </td>
    <td><%=rsGlobalWeb("verse")%>
    </td>
    <td><%=rsGlobalWeb("text_data")%>
    </td>
    </tr>
    <% rsGlobalWeb.MoveNext
    Loop
    %>
    </table>
    <%End If%>
    <%End If%>
    <%
    rsGlobalWeb.Close
    dbGlobalWeb.Close
    %>
    </body>
    </html>


    I had found a code. Is this the code I'm looking for? If it is then I've already tried it but couldn't figure out where to insert it, and how to make the proper connections. I'm a newbie.



    set objCN = Server.CreateObject("adodb.connection")
    objCN.Open "bible"

    set objCommand = Server.CreateObject("adodb.command")
    with objCommand
    .CommandType = adCmdStoredProc
    .CommandText = "AllTasksByType"
    ' add your parameters here
    end with

    objCommand.ActiveConnection = objCN

    Set objRS = objCommand.Execute
    ' Here you have your recordset
    ' Method 2:
    ' Move recordset data to 2 dimensional array using GetRows()
    'If not objRS.EOF then
    ' arrResults = objRS.GetRows
    ' response.write("RecordCount=" & Ubound(arrResults,2)+1)
    'End If

    objrs.close

  2. #2
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Here are some paging links and I don't think I'd go with the stored procedure way of doing it unless you were using something like SQL Server in which case you probably wouldn't be a newbie...

    Database Paging
    http://www.asp101.com/samples/db_paging.asp
    Uses PageSize method of recordset.

    Paging Records with GetRows by Mukul Sabharwal - 7/5/2000
    http://www.4guysfromrolla.com/webtech/070500-1.shtml
    Uses GetRows.

    Displaying a certain # of records on each webpage Paging
    http://www.aspfree.com/asp/startpage.asp?id=11
    Uses PageSize method of recordset.
    Shorter.

    Recordset Paging with ADO 2.0 by Michael Qualls
    http://www.asp101.com/articles/recor...ging/index.asp
    Uses PageSize method of recordset.
    Longer with more comments.

    Paging Demo provides Count and Navigation at the top of the Page!
    http://www.aspfree.com/asp/startpage.asp?id=65

    How do I page through a recordset? - 11/19/2001
    http://www.aspfaq.com/show.asp?id=2120
    Starts at particular record in record and another method uses a stored procedure.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

Posting Permissions

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