how to count the database search results of record

gilgalbiblewhee · #1 (**permalink**) 07-30-04, 22:20

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:

Quote:

<%@ 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.

Quote:

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

Quote:

' 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

Bullschmidt · #2 (**permalink**) 08-04-04, 02:07

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech