ASP Database Search Engine by Mark Alexander

SEARCH ASP FREE

This came about when designing an Intranet-wide Knowledge Management System for Trocaire . Information was stored in an Access database, which is searched when the user makes a request for information and the results returned to the users' browser in a formatted HTML search results page. You can then click on the returned summary to access the complete record.

This solution makes use of ActiveX Data Objects, therefore a copy of adovbs.inc must be located in same directory as the asp pages. If you are going to be copying and pasting the code, then you will have to locate a copy of the file adovbs.inc. The simplest way is to do a search on your hard disk or browse the Web. The format of the database included is Access 2000. An Access 97 database has been thoroughly tested with this system and works perfectly.

The files are:

searchlibrary.asp
display.asp
adovbs.inc
Library.mdb (MS Access 2000)

When you first use these files there are 3 variables that you must change so the server will know where to look for the database and the correct number of results to display per page.

With searchlibrary.asp:

Go to Line 74 and change the location of the database in the variable strDBLocation.
On Line 80 you can change the value of the constant intPageSize to an integer value, which is the number of results to be displayed per page.

With display.asp:

Go to Line 32 and change the location of the database in the variable strDBLocation.

Below you will find the listings for searchlibrary.asp and display.asp.

Listing 1: searchlibrary.asp

Return

<% @Language = VBScript %>
<%
Option Explicit
Response.Expires = 0
Response.Buffer = True

'********************************************************
'
' searchlibrary.asp: Searches the Library.mdb database
' and displays the results on the same page. If no
' records are matched in the database then a summary
' of your search options is displayed.
'
' Note 1: Go to Line 74 and change the location of the
' database in the variable strDBLocation.
' Note 2: On Line 80 you can change the value of
' intPageSize to an integer value of the number of
' results to be displayed per page.
' Note 3: The database format used is MS Access 2000
'
'********************************************************

'********************************************************
' When the page is requested it loads the following file
' using the file include directive:
'
' adovbs.inc: ADO (ActiveX Data Objects) constants
' loaded
'
' The file adovbs.inc must reside in the same directory
' as this file.
'********************************************************
%>

<%
'********************************************************
' If this is the first time the page has been requested,
' the server-side script re-directs itself to itself by
' using a QUERY_STRING, which contains the default search
' arguments. Search arguments are as follows:
'
' p: The text part of the search
' c: Category. Default is All for an unrestricted
' search. Category parameters include Article,
' Campaign, Country, Fundraising, Overseas,
' Press Release, Publication and File Formats.
' t: Search by field type. Default is search by Title.
' Other search type parameters include Summary,
' Both (Title & Summary), and ID.
' s: The search direction. Initially Search. Can Also
' be Next and Previous
' cp: The current page. If your search yields
' multiple pages of results, this is the page that
' will be displayed. Numbering starts at 0 (zero).
' o: Order/Sort. You can sort your results ascending
' or descending by Title or Date.
'
' The default is to show the first page of ALL records
' sorted by Title Ascending.
'********************************************************

If Request.ServerVariables("QUERY_STRING") = "" then
strURL = Request.ServerVariables("SCRIPT_NAME") & "?p=&c=All&t=Title&s=Search&cp=1"
Response.Redirect(strURL)
End If

Dim intCurrentPage, objConn, objRS, strSQL, strURL, strP, strT, strFile
Dim intTotalPages, intI, strDateOrder, strTitleOrder, blnCatSet, strDBLocation

'********************************************************
' The absolute path of the database on the
' Workstation/Network Server/Web Server
'********************************************************
strDBLocation = "C:\inetpub\wwwroot\temp\db\Library.mdb"

'********************************************************
' Number of records displayed per page. NB intPageSize is
' a property of the ADO Constants.
'********************************************************
Const intPageSize = 20
%>
<html>
<head>
<title>Database Search</title>
<link rel="STYLESHEET" href="trocaire.css" type="text/css">
<script language="javascript">
/*
Validate: Ensures legal search parameters have been entered.
*/
function Validate()
{
var tOption = -1;
for(i=0; i<document.frmSearch.t.length; i++)
{
if(document.frmSearch.t[i].checked)
{
tOption = i
}
}

if(document.frmSearch.p.value.length > 0)
{
if(tOption == 3) // If searching by ID
{
if(isNum(document.frmSearch.p.value))
{
document.frmSearch.submit();
}
else
{
alert("ID can only be a number.");
document.frmSearch.p.focus();
}
}
else
{
document.frmSearch.submit();
}
}
else
{
alert("Enter Search Parameter");
document.frmSearch.p.focus();
}
}

/*
isNum: If searching by ID, checks if entered text is numeric.
*/
function isNum(txt)
{
var validPhone = "0123456789";
var temp;
for (var i=0; i<txt.length; i++)
{
temp = txt.substring(i, i+1);
if (validPhone.indexOf(temp) == "-1")
{
return false;
}
}
return true;
}

</script>
</head>
<body onLoad="javascript:document.frmSearch.p.focus();">
<table border="0" width="100%">
<tr>
<td width="660"><h1><center>Database Search</center></h1></td>
</tr>
<tr>
<td width="660" valign="top">
<form name="frmSearch" action='<%= Request.ServerVariables("SCRIPT_NAME") %>' method="get">


<%
'********************************************************
' The search controls are re-populated with the original
' search parameters.
'********************************************************
%>
<table border="0" width="660">
<tr>
<td width="80" align="right"><b>Search for:</b></td>
<td width="300"><input type="text" name="p" size="50" maxlength="100" value="<%= Server.HTMLEncode(Request.QueryString("p")) %>"></td>
<td width="70" align="right"><b>Category</b>:</td>
<td width="" valign="top">
<select size="1" name="c">
<%
strT = Request.QueryString("t")
%>
<option value="All" <%
If (Request.QueryString("c") = "All") Or (Request.QueryString("c") = "") Or (strT = "ID") Then
Response.Write ("selected")
blnCatSet = True
End If %>>All</option>
<option value="Article" <% If ((Request.QueryString("c") = "Article") And (blnCatSet = False)) Then Response.Write ("selected") %>>Article</option>
<option value="Campaign" <% If ((Request.QueryString("c") = "Campaign") And (blnCatSet = False)) then Response.Write ("selected") %>>Campaign</option>
<option value="Country" <% If ((Request.QueryString("c") = "Country") And (blnCatSet = False)) then Response.Write ("selected") %>>Country</option>
<option value="Fundraising" <% If ((Request.QueryString("c") = "Fundraising") And (blnCatSet = False)) then Response.Write ("selected") %>>Fundraising</option>
<option value="Overseas" <% If ((Request.QueryString("c") = "Overseas") And (blnCatSet = False)) then Response.Write ("selected") %>>Overseas</option>
<option value="PressRelease" <% If ((Request.QueryString("c") = "PressRelease") And (blnCatSet = False)) then Response.Write ("selected") %>>Press Release</option>
<option value="Publication" <% If ((Request.QueryString("c") = "Publication") And (blnCatSet = False)) then Response.Write ("selected") %>>Publication</option>
<optgroup label="Files">
<option value="Acrobat" <% If ((Request.QueryString("c") = "Acrobat") And (blnCatSet = False)) then Response.Write ("selected") %>>Acrobat</option>
<option value="Excel" <% If ((Request.QueryString("c") = "Excel") And (blnCatSet = False)) then Response.Write ("selected") %>>Excel</option>
<option value="Photograph" <% If ((Request.QueryString("c") = "Photograph") And (blnCatSet = False)) then Response.Write ("selected") %>>Photograph</option>
<option value="PowerPoint" <% If ((Request.QueryString("c") = "PowerPoint") And (blnCatSet = False)) then Response.Write ("selected") %>>PowerPoint</option>
<option value="Word" <% If ((Request.QueryString("c") = "Word") And (blnCatSet = False)) then Response.Write ("selected") %>>Word</option>
</optgroup>
</select>
</td>
</tr>
<tr>
<td width="80" height="1">&nbsp;</td>
<td width="300" height="1">
<input type="radio" name="t" value="Title" <% If (Request.QueryString("t") = "Title") Or (Request.ServerVariables("QUERY_STRING") = "") then Response.Write ("checked") %> tabindex="">Title
<input type="radio" name="t" value="Summary" <% If Request.QueryString("t") = "Summary" then Response.Write ("checked") %> tabindex="">Summary
<input type="radio" name="t" value="Both" <% If Request.QueryString("t") = "Both" then Response.Write ("checked") %> tabindex="">Both
<input type="radio" name="t" value="ID" <% If Request.QueryString("t") = "ID" then Response.Write ("checked") %> tabindex="">ID &nbsp;
<input type="button" name="s" value="Search" onClick="Validate();"></td>
<td width="70" height="1">&nbsp;</td>
<td width="*" height="1">&nbsp;</td>
</tr>
</table>


<%
If Request.ServerVariables("QUERY_STRING") <> "" then
intCurrentPage = Cint(Request.QueryString("cp"))
Select Case Request.QueryString("s")
Case "Search"
intCurrentPage = 1
Case "Previous"
intCurrentPage = intCurrentPage - 1
Case "Next"
intCurrentPage = intCurrentPage + 1
Case Else
intCurrentPage = 1
End Select

'********************************************************
' Start of SQL string generation
'********************************************************

strSQL = "SELECT LibraryID, Title, Author, AmendDate, AmendTime, Summary, ThumbnailPath, FilePath, Acrobat, Excel, Photograph, PowerPoint, Word "
strSQL = strSQL & "FROM tblLibrary "
strP = Replace(Request.QueryString("p"), "'", "''")
Select Case Request.QueryString("t")
Case "Title"
strSQL = strSQL & "WHERE Title LIKE '%" & strP & "%' "
Case "Summary"
strSQL = strSQL & "WHERE Summary LIKE '%" & strP & "%' "
Case "Both"
strSQL = strSQL & "WHERE Title LIKE '%" & strP & "%' OR Summary LIKE '%" & strP & "%' "
Case "ID"
strSQL = strSQL & "WHERE LibraryID = " & strP & " "
End Select
If (Request.QueryString("c") <> "All") And (Request.QueryString("t") <> "ID") Then
strSQL = strSQL & "AND " & Request.QueryString("c") & " = TRUE "
End If
If Request.QueryString("o") <> "" Then
Select Case Request.QueryString("o")
Case "da"
strSQL = strSQl & "ORDER BY AmendDate, AmendTime "
Case "dd"
strSQL = strSQl & "ORDER BY AmendDate DESC, AmendTime DESC "
Case "ta"
strSQL = strSQl & "ORDER BY Title "
Case "td"
strSQL = strSQl & "ORDER BY Title DESC "
End Select
Else
strSQL = strSQl & "ORDER BY Title "
End If

'********************************************************
' Open database connection using OLE DB.
'********************************************************
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Provider = "Microsoft.Jet.OLEDB.4.0"
objConn.Open strDBLocation

'********************************************************
' Define and open the Recordset.
' WARNING: DO NOT alter any of these settings.
'********************************************************
set objRS = Server.CreateObject("ADODB.RecordSet")
objRS.CursorLocation = adUseClient
objRS.CursorType = adOpenStatic
objRS.CacheSize = intPageSize
objRS.Open strSQL, objConn, , , adCmdText

If (objRS.EOF = False) And (objRS.BOF = False) Then
objRS.PageSize = intPageSize
If Not(objRS.EOF) Then objRS.AbsolutePage = intCurrentPage
intTotalPages = objRS.PageCount
%>



<table border="0" width="100%">
<tr>
<td colspan="2">
<table border="0" width="100%">
<tr>
<td width="34%" align="left"><%
Response.Write "Number of records found: 20</td><td width='33%' align='center'>"

If intCurrentPage > 1 Then
Response.Write "<a href='" & Request.ServerVariables("SCRIPT_NAME") & "?p=" & Server.URLEncode(Request.QueryString("p")) & "&c=" & Request.QueryString("c") & "&s=Previous" & "&cp=" & intCurrentPage & "&t=" & Request.QueryString("t") & "&o=" & Request.QueryString("o") & "'>Previous</a>"
End If

If (intCurrentPage > 1) And (intCurrentPage < intTotalPages) Then
Response.Write " | "
End If

If intCurrentPage <> intTotalPages Then
Response.Write "<a href='" & Request.ServerVariables("SCRIPT_NAME") & "?p=" & Server.URLEncode(Request.QueryString("p")) & "&c=" & Request.QueryString("c") & "&s=Next" & "&cp=" & intCurrentPage & "&t=" & Request.QueryString("t") & "&o=" & Request.QueryString("o") & "'>Next</a>"
End If

Response.Write "</td><td width='33%' align='right'>"
%>
<select onChange="if(options[selectedIndex].value) window.location.href=(options[selectedIndex].value)">
<%
For intI = 1 To objRS.PageCount
Response.Write "<option value=""" & Request.ServerVariables("SCRIPT_NAME") & "?p=" & Server.URLEncode(Request.QueryString("p")) & "&c=" & Request.QueryString("c") & "&s=Next" & "&cp=" & (intI - 1) & "&t=" & Request.QueryString("t") & "&o=" & Request.QueryString("o") & """"
If intI = intCurrentPage Then Response.Write " selected"
Response.Write ">" & intI & "</option>" & vbCrLf
Next
intI = 0
%>
</select> of <%= intTotalPages %>.
</td>
</tr>
</table>
</td>
</tr>
<tr>
<%
If (Request.QueryString("o") = "") Or (Request.QueryString("o") = "dd") Then
strDateOrder = "da"
Else
strDateOrder = "dd"
End If

If (Request.QueryString("o") = "") Or (Request.QueryString("o") = "ta") Then
strTitleOrder = "td"
Else
strTitleOrder = "ta"
End If
%>
<td>
<% Response.Write "<a href='" & Request.ServerVariables("SCRIPT_NAME") & "?p=" & Server.URLEncode(Request.QueryString("p")) & "&c=" & Request.QueryString("c") & "&s=Next" & "&cp=0&t=" & Request.QueryString("t") & "&o=" & strTitleOrder & "'>Title</a> " %>
</td>
<td align="right">
<% Response.Write "<a href='" & Request.ServerVariables("SCRIPT_NAME") & "?p=" & Server.URLEncode(Request.QueryString("p")) & "&c=" & Request.QueryString("c") & "&s=Next" & "&cp=0&t=" & Request.QueryString("t") & "&o=" & strDateOrder & "'>Date</a> " %>
</td>
</tr>





<%
For intI = 1 To objRS.PageSize
%>
<tr>
<td colspan="2">&nbsp;</td>
</tr>
<tr>
<td colspan="2">
<a href='display.asp?id=<%= objRS("LibraryID") %>'><b><%= objRS("Title") %></b> (ID: <%= objRS("LibraryID") %>)</a>
</td>
</tr>
<tr>
<td width="50%">
<%= objRS("Author") %>
</td>
<td width="50%" align="right">
Date: <%= objRS("AmendDate") %>
</td>
</tr>
<tr>
<td width="80%">
<%= objRS("Summary") %>
</td>
<td width="20%" align="right">
<% if objRS("ThumbnailPath") <> "" Then Response.Write("<img src=""" & objRS("ThumbnailPath") & """>") %>
</td>
</tr>



<%
objRS.MoveNext
If objRS.EOF Then Exit For
Next
%>

</table>
<input type="hidden" name="cp" value='<%= intCurrentPage %>'>
<%
Else
%>
<br>
<br>
<br>
<br>
<div align="center">
<p><b>No Records were found based on the search string off:</b></p>
<p>Search For: <b><%= Request.QueryString("p")%></b></p>
<p>Method: <b><%= Request.QueryString("t") %></b></p>
<p>Category: <b><%= Request.QueryString("c") %></b></p>
</div>
<%
End If
objRS.close
objConn.close
Set objRS = Nothing
Set objConn = Nothing
End If
%>
</form>
</td>
</tr>
</table>
</body>
</html>

Listing 2: display.asp

Return

<% @Language = VBScript %>
<%
Option Explicit
Response.Expires = 0
Response.Buffer = True

'********************************************************
'
' display.asp: Displays the full record that was
' requested from searchlibrary.asp.
'
' Note 1: Go to Line 32 and change the location of the
' database in the variable strDBLocation.
'
'********************************************************

'********************************************************
' If this page has been accessed directly, without going
' via searchlibrary.asp, then this will re-direct you to
' searchlibrary.asp.
'********************************************************
If Len(Request.ServerVariables("QUERY_STRING")) = 0 then
Response.Redirect("searchlibrary.asp")
End if

Dim objConn, objRS, strSQL, strDBLocation

'********************************************************
' The absolute path of the database on the
' Workstation/Network Server/Web Server
'********************************************************
strDBLocation = "C:\inetpub\wwwroot\temp\db\Library.mdb"

'********************************************************
' SQL string generation - pull all fields for the record.
'********************************************************
strSQL = "SELECT * "
strSQL = strSQL & "FROM tblLibrary "
strSQL = strSQL & "WHERE LibraryID = " & Request.QueryString("id")

'********************************************************
' Open database connection using an OLE DB Provider.
'********************************************************
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Provider = "Microsoft.Jet.OLEDB.4.0" ' Access 2000 -- For Access 97 3.51 (I think<:)
objConn.Open strDBLocation

'********************************************************
' Create the Recordset.
'********************************************************
set objRS = objConn.Execute(strSQL)

While Not objRS.EOF

'********************************************************
' Display the full record.
'********************************************************
%>
<html>
<head>
<title>Database Search</title>
</head>
<body>
<table border="0" width="660">
<tr>
<td width="660" class="title"><h1><center>Database Search</center></h1></td>
</tr>
<tr>
<td width="660" valign="top" class="display">
<p>
<span id="title"><%= objRS("Title") %></span>
<span id="ref">(ID: <%= objRS("LibraryID") %>)</span>
</p>
<%= objRS("Author") %>
<br>
<%= objRS("AmendDate") %>
<hr>
<br>
<%
'********************************************************
' If record has a textual article then display.
'********************************************************
If objRS("ArticleText") <> "" Then
Response.Write Replace(objRS("ArticleText"), vbCrLf, "<br>")
End If

'********************************************************
' If the record has a file attachment then generate and
' display the appropriate link.
'********************************************************
If objRS("FilePath") <> "" Then
%>
<br>
<br>
<table border="0" align="center" width="300" bgcolor="lightblue" class="title">
<tr>
<td align="center"><b>Click below to download the file</b></td>
</tr>
<tr>
<td align="center">
<%
Response.Write "<a href=""" & objRS("FilePath") & """>" & objRS("FilePath") & "</a>"
%>
</td>
</tr>
</table>
<%
End If
objRS.MoveNext
Wend
objRS.close
objConn.close
set objRS = Nothing
set objConn = Nothing
%>
</td>
</tr>
</table>
</body>
</html>