Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 30th, 2004, 09:03 PM
Friend of Wrox
Points: 1,107, Level: 13
Points: 1,107, Level: 13 Points: 1,107, Level: 13 Points: 1,107, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2004
Location: , , .
Posts: 240
Thanks: 0
Thanked 1 Time in 1 Post
Default 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:

Code:
<%@ 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">Book </th>
    <th bgcolor="#800000">Spoke </th>
    <th bgcolor="#800000">Book Title </th>
    <th bgcolor="#800000">Chapter </th>
    <th bgcolor="#800000">Verse </th>
<th bgcolor="#800000">Text </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 from your forum. 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.

Code:
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
 
Old July 31st, 2004, 01:17 AM
Friend of Wrox
 
Join Date: Sep 2003
Location: Mentiri, Muara, Brunei.
Posts: 363
Thanks: 0
Thanked 1 Time in 1 Post
Default

Use Recordset object

set rs=server.createObject("adodb.recordset")

and use pagesize, absolutepage,cachesize, to maintain 20 or 50 records per page.



 
Old July 31st, 2004, 10:37 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi there,

Do a search in this forum for keyword "Paging", you will get a lot of such topics, as this was discussed many times in this forum.

You may also take a look at these links.

Recordset Paging with ADO 2.0

DB paging Sample code

ADO Recordset Paging in ASP

Cheers!

_________________________
- Vijay G
Strive for Perfection




Similar Threads
Thread Thread Starter Forum Replies Last Post
Search box on a Database results page meichmann ASP.NET 2.0 Basics 3 April 2nd, 2008 10:25 AM
display count results and image in webpage carswelljr SQL Language 2 April 19th, 2007 09:44 AM
How do I display search results... aspiretech9 Beginning PHP 1 April 25th, 2005 04:59 PM
SQL count query not giving correct results hman SQL Language 2 March 16th, 2005 07:06 AM
SQL count query not giving correct results hman SQL Server 2000 1 March 15th, 2005 01:15 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.