adding counts to search results

[gilgalbiblewhee]

I found this on the net but it shows that he is using one ASP page for both search and response. I need it for two pages. The first for HTML coding and the second (response page) with ASP coding. What do I need to modify?


<%
' Declare our variables... always good practice!
Dim strURL ' The URL of this page so the form will work
' no matter what this file is named.

Dim cnnSearch ' ADO connection
Dim rstSearch ' ADO recordset
Dim strDBPath ' path to our Access database (*.mdb) file

Dim strSQL ' The SQL Query we build on the fly
Dim strSearch ' The text being looked for

' Retreive the URL of this page from Server Variables
strURL = Request.ServerVariables("URL")

' Retreive the term being searched for. I'm doing it on
' the QS since that allows people to bookmark results.
' You could just as easily have used the form collection.
strSearch = Request.QueryString("search")
'strSearch = Replace(strSearch, "'", "''")

' Since I'm doing this all in one page I need to see if anyone
' has searched for something. If they have we hit the DB.
' O/W I just show the search form and quit.

%>
<p>Search our sample db by first or last name. (% returns all)</p>
<form action="<%= strURL %>" method="get">
<input name="search" value="<%= strSearch %>" />
<input type="submit" />
</form>
<p>[Try 'am' or 'er' for an example]</p>
<%
If strSearch <> "" Then
' MapPath of virtual database file path to a physical path.
' If you want you could hard code a physical path here.
strDBPath = Server.MapPath("database.mdb")


' Create an ADO Connection to connect to the sample database.
' We're using OLE DB but you could just as easily use ODBC or a DSN.
Set cnnSearch = Server.CreateObject("ADODB.Connection")

' This line is for the Access sample database:
'cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"

' We're actually using SQL Server so we use this line instead:
cnnSearch.Open "Provider=SQLOLEDB;Data Source=10.2.1.214;" _
& "Initial Catalog=samples;User Id=samples;Password=password;" _
& "Connect Timeout=15;Network Library=dbmssocn;"

' Build our query based on the input.
strSQL = "SELECT last_name, first_name, sales " _
& "FROM sample " _
& "WHERE last_name LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR first_name LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "ORDER BY last_name;"

' Execute our query using the connection object. It automatically
' creates and returns a recordset which we store in our variable.
Set rstSearch = cnnSearch.Execute(strSQL)

' Display a table of the data in the recordset. We loop through the
' recordset displaying the fields from the table and using MoveNext
' to increment to the next record. We stop when we reach EOF.
' For fun I'm combining some fields and showwing you can do more then
' just spit out the data in the form it is in in the table.
%>
<table border="1">
<tr>
<th>Name</th>
<th>Sales</th>
</tr>
<%
Do While Not rstSearch.EOF
%>
<tr>
<td><%= rstSearch.Fields("first_name").Value %> <%= rstSearch.Fields("last_name").Value %></td>
<td><%= rstSearch.Fields("sales").Value %></td>
</tr>
<%

rstSearch.MoveNext
Loop
%>
</table>
<%
' Close our recordset and connection and dispose of the objects
rstSearch.Close
Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing
End If

' That's all folks! See it's really not all that hard.
%>

[buntine]

You wilol have to remove this section of the code, which is the HTML form for the search page.

<p>Search our sample db by first or last name. (% returns all)</p>
<form action="<%= strURL %>" method="get">
<input name="search" value="<%= strSearch %>" />
<input type="submit" />
</form>

And then get rid of the If Statement that check if StrSearch <> "". Dont forget to remove the end if, also.

Place the form in a new page and alter the <form> tag so it points to the ASP script.

Regards,
Andrew Buntine.

[gilgalbiblewhee]

Ok I found a better coding but when I was merging (because I wanted to include checkbox and radio functions) it stopped working.

<%
Option Explicit

'************************************************************************************

'* Declaration section
'************************************************************************************
' Mode contstants
Const MODE_DEFAULT = 1
Const MODE_RESULTS = 2

Const DB_NAME = "kjv.mdb" ' Name of our database file
Const SCRIPT_NAME = "bible2.asp" ' Name of this script

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
ShowResults

Case Else ' This one is for MODE_DEFAULT or invalid modes all the same
' By default display the search form
ShowSearchForm
End Select

'************************************************************************************
'* End of Main section
'************************************************************************************

'************************************************************************************
'* Functions section
'************************************************************************************

' This function will generate our connection string
' it assumes that Access database is in the same folder as this script
Private Function GetConnectionString()
GetConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath(DB_NAME) & ";" & _
"UID=;PWD=;"
End Function

' Shows HTML page header
Public Function OutputPageHeader()
%>
<HTML>
<HEAD><TITLE>ADO Recordset Paging Sample</TITLE></HEAD>
<BODY>
<H2>ADO Recordset Paging Sample</H2>
<H3><A HREF="<%=SCRIPT_NAME%>">Back Home</A></H3>
<%
End Function

' Shows HTML page footer
Public Function OutputPageFooter()
%>
</BODY>
</HTML>
<%
End Function

' This function will display the search form
Private Function ShowSearchForm()
OutputPageHeader
%>
<!--
This form will direct user to itself with MODE_RESULTS mode
-->
<FORM ACTION="<%=SCRIPT_NAME%>" METHOD="GET">
Item Name: <INPUT TYPE="text" NAME="Keyword" VALUE="Item"> <INPUT TYPE="submit" VALUE=" Search ">
<INPUT TYPE="hidden" NAME="Mode" VALUE="<%=MODE_RESULTS%>">
</FORM>
<%
OutputPageFooter
End Function

' This function will display the results of the search
Private Function ShowResults()
Dim strConn ' Database connection string
Dim SQL ' String that will have our SQL statments
Dim RS ' 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

' 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"))

' 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 text_data LIKE '%" & Replace(Keyword, "'", "''") & "%'"

' Create our connection string
strConn = GetConnectionString()

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

' Start outputing HTML
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>Results:</b><br>" & String(20,"-")

' 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
Do While Not (RS.Eof OR RS.AbsolutePage <> nPage)

' All we do here is just show the records
Response.Write "<br>" & RS("text_data")

' Move on to the next record
RS.MoveNext
Loop
Else
' We did not find anything
Response.Write "Nothing found. Try again.<p><A HREF=""" & SCRIPT_NAME & """>Back</A>"
End If

' Be nice - close the recordset
RS.Close

' Finish this page
OutputPageFooter
End Function
'************************************************************************************
'* End of Functions section
'************************************************************************************
%>

[buntine]

I had a quick look at it and couldnt find anything that was blatently wrong with it. Are you receiving an error? Or is it just not showing anything?

[gilgalbiblewhee]

What I want is the search to go through every field instead of "text_data" which but one field.

This is what I have.

SQL = "SELECT * FROM bible WHERE text_data LIKE '%" & Replace(Keyword, "'", "''") & "%'"

[buntine]

Oh ok. You will have to use the OR operator and check for multiple conditions.

Keyword = Replace(Keyword, "'", "''")
SQL = "SELECT * FROM bible WHERE text_data LIKE '%" & Keyword & "%' " & _
"OR anotherField LIKE '%" & Keyword & "%' OR thirdField LIKE '%" & Keyword & "%'"

Regards.

[gilgalbiblewhee]

I decided to restart working from the sample code. It only has one field to search into. So I replaced that with my database "kjv.mdb", table "bible" and field: "text_data" and here is the code:

<%
'************************************************************************************
'* ADO Recordset Paging Sample Script
'* by Konstantin Vasserman
'* June 2000
'************************************************************************************

Option Explicit

'************************************************************************************
'* Declaration section
'************************************************************************************
' Mode contstants
Const MODE_DEFAULT = 1
Const MODE_RESULTS = 2

Const DB_NAME = "kjv.mdb" ' Name of our database file
Const SCRIPT_NAME = "bible5.asp" ' Name of this script

Const RECORDS_PER_PAGE = 50 ' 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
ShowResults

Case Else ' This one is for MODE_DEFAULT or invalid modes all the same
' By default display the search form
ShowSearchForm
End Select

'************************************************************************************
'* End of Main section
'************************************************************************************

'************************************************************************************
'* Functions section
'************************************************************************************

' This function will generate our connection string
' it assumes that Access database is in the same folder as this script
Private Function GetConnectionString()
GetConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath(DB_NAME) & ";" & _
"UID=;PWD=;"
End Function

' Shows HTML page header
Public Function OutputPageHeader()
%>
<HTML>
<HEAD><TITLE>ADO Recordset Paging Sample</TITLE></HEAD>
<BODY>
<H2>ADO Recordset Paging Sample</H2>
<H3><A HREF="<%=SCRIPT_NAME%>">Back Home</A></H3>
<%
End Function

' Shows HTML page footer
Public Function OutputPageFooter()
%>
</BODY>
</HTML>
<%
End Function

' This function will display the search form
Private Function ShowSearchForm()
OutputPageHeader
%>
<!--
This form will direct user to itself with MODE_RESULTS mode
-->
<FORM ACTION="<%=SCRIPT_NAME%>" METHOD="GET">
Item Name: <INPUT TYPE="text" NAME="Keyword" VALUE="Item"> <INPUT TYPE="submit" VALUE=" Search ">
<INPUT TYPE="hidden" NAME="Mode" VALUE="<%=MODE_RESULTS%>">
</FORM>
<%
OutputPageFooter
End Function

' This function will display the results of the search
Private Function ShowResults()
Dim strConn ' Database connection string
Dim SQL ' String that will have our SQL statments
Dim RS ' 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

' 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"))

' define our SQL statment
' 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 text_data LIKE '%" & Replace(Keyword, "'", "''") & "%'"

' Create our connection string
strConn = GetConnectionString()

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

' Start outputing HTML
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>Results:</b><br>" & String(20,"-")

' 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
Do While Not (RS.Eof OR RS.AbsolutePage <> nPage)

' All we do here is just show the records
Response.Write "<br>" & RS("text_data")

' Move on to the next record
RS.MoveNext
Loop
Else
' We did not find anything
Response.Write "Nothing found. Try again.<p><A HREF=""" & SCRIPT_NAME & """>Back</A>"
End If

' Be nice - close the recordset
RS.Close

' Finish this page
OutputPageFooter
End Function
'************************************************************************************
'* End of Functions section
'************************************************************************************
%>


I need to change this into a multiple field search, where I can enable/disable by using checkboxes. I have the oringinal codes for checkbox functions used in another page format. I just need to adjust to this page format. I will post more later.

[gilgalbiblewhee]

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


So this was part of the previous format, which worked with an htm search page.

the Where statement was

SqlBible = "SELECT * FROM bible WHERE "

I didn't need to add anything else to it. But now that I think of it there I was only able to search one word at a time. Is it possible to enable several fields and search multiple words and get results? Example:
search: beginning and 001 (enable "text_data" and "chapter" fields)and obtain the results.

What I need to know is how to narrow down the search to the records which have these two results (because two fields were checked in checkboxes)

[gilgalbiblewhee]

There's something wrong in this:


<%
'************************************************************************************
'* ADO Recordset Paging Sample Script
'* by Konstantin Vasserman
'* June 2000
'************************************************************************************

Option Explicit

'************************************************************************************
'* Declaration section
'************************************************************************************
' Mode contstants
Const MODE_DEFAULT = 1
Const MODE_RESULTS = 2

Const DB_NAME = "kjv.mdb" ' Name of our database file
Const SCRIPT_NAME = "bible5.asp" ' Name of this script

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
ShowResults

Case Else ' This one is for MODE_DEFAULT or invalid modes all the same
' By default display the search form
ShowSearchForm
End Select

'************************************************************************************
'* End of Main section
'************************************************************************************

'************************************************************************************
'* Functions section
'************************************************************************************

' This function will generate our connection string
' it assumes that Access database is in the same folder as this script
Private Function GetConnectionString()
GetConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath(DB_NAME) & ";" & _
"UID=;PWD=;"
End Function

' Shows HTML page header
Public Function OutputPageHeader()
%>
<HTML>
<HEAD><TITLE>ADO Recordset Paging Sample</TITLE></HEAD>
<BODY>
<H2>Search the bible, the Wheel of God</H2>
<H3><A HREF="<%=SCRIPT_NAME%>">Back Home</A></H3>
<%
End Function

' Shows HTML page footer
Public Function OutputPageFooter()
%>
</BODY>
</HTML>
<%
End Function

' This function will display the search form
Private Function ShowSearchForm()
OutputPageHeader
%>
<!--
This form will direct user to itself with MODE_RESULTS mode
-->
<FORM ACTION="<%=SCRIPT_NAME%>" METHOD="GET">
Look for: <INPUT TYPE="text" NAME="Keyword" VALUE=""> <INPUT TYPE="submit" VALUE=" Search "><input type="reset">
<INPUT TYPE="hidden" NAME="Mode" VALUE="<%=MODE_RESULTS%>"><p>
If you search number write 001 instead of 1 and 022 instead of 22

</td>
<br>
<INPUT TYPE="CheckBox" NAME="book" VALUE="Book">Book<br>
<INPUT TYPE="CheckBox" NAME="book_spoke" VALUE="Book Spoke">Book Spoke<br>
<INPUT TYPE="CheckBox" NAME="book_title" VALUE="Book Title">Book Title<br>
<INPUT TYPE="CheckBox" NAME="chapter" VALUE="Chapter">Chapter<br>
<INPUT TYPE="CheckBox" NAME="chapter_spoke" VALUE="Chapter Spoke">Chapter Spoke<br>
<INPUT TYPE="CheckBox" NAME="verse" VALUE="Verse">Verse<br>
<INPUT TYPE="CheckBox" NAME="verse_spoke" VALUE="Verse Spoke">Verse Spoke<br>
<INPUT TYPE="CheckBox" NAME="text_data" VALUE="Text" CHECKED>Text<br>
</tr>
</FORM>
<%
OutputPageFooter
End Function


TO BE CONTINUED...PART 2

[gilgalbiblewhee]

' This function will display the results of the search
Private Function ShowResults()
Dim strConn ' Database connection string
Dim SQL ' String that will have our SQL statments
Dim RS ' 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 'for the checkboxes

' 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"))

' define our SQL statment
' 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 "
' Create our connection string
strConn = GetConnectionString()

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

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

iCounter = iCounter + 1

end if

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

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

Sql = Sql & "book_spoke LIKE '%" & Keyword & "%'"

iCounter = iCounter + 1

end if

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

If iCounter > 0 Then
Sql = Sql & " AND "
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 & " AND "
End If

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

iCounter = iCounter + 1

end if


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

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

Sql = Sql & "chapter_spoke LIKE '%" & Keyword & "%'"

iCounter = iCounter + 1

end if

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

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

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

iCounter = iCounter + 1

end if

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

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

Sql = Sql & "verse_spoke LIKE '%" & Keyword & "%'"

iCounter = iCounter + 1

end if

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

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

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

iCounter = iCounter + 1

end if

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

' Start outputing HTML
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(20,"-")

' 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


<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">Book 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">Chapter Spoke</font></th>
<th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Verse </font></th>
<th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Verse Spoke</font></th>
<th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Text </font></th>
</tr>

Do While Not (RS.Eof OR RS.AbsolutePage <> nPage)




<tr>
<td><%=rs("book")%>
</td>
<td><%=rs("book_spoke")%>
</td>
<td><%=rs("book_title")%>
</td>
<td><%=rs("chapter")%>
</td>
<td><%=rs("chapter_spoke")%>
</td>
<td><%=rs("verse")%>
</td>
<td><%=rs("verse_spoke")%>
</td>
<td><%=rs("text_data")%>
</td>
</tr>

</table>


' Move on to the next record
RS.MoveNext
Loop
Else
' We did not find anything
Response.Write "Nothing found. Try again.<p><A HREF=""" & SCRIPT_NAME & """>Back</A>"
End If

' Be nice - close the recordset
RS.Close

' Finish this page
OutputPageFooter
End Function
'************************************************************************************
'* End of Functions section
'************************************************************************************
%>


Microsoft VBScript compilation error '800a0400'

Expected statement

/bible5.asp, line 307

<table BORDER="0" width="100%" cellpadding="3">
^

[gilgalbiblewhee]

Microsoft VBScript compilation error '800a0400'

Expected statement

/bible5.asp, line 355

End If
^