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: 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. <% @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.
'********************************************************
%>
<!--#include file="adovbs.inc"-->
<%
'********************************************************
' 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">
<!--------- Display the search controls: Start ------------->
<%
'********************************************************
' 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"> </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
<input type="button" name="s" value="Search" onClick="Validate();"></td>
<td width="70" height="1"> </td>
<td width="*" height="1"> </td>
</tr>
</table>
<!---------- Display the search controls: End -------------->
<%
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
%>
<!------- Display the navagation controls: Start ----------->
<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>
<!-------- Display the navagation controls: End ------------>
<!--------- Display the search results: Start -------------->
<%
For intI = 1 To objRS.PageSize
%>
<tr>
<td colspan="2"> </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>
<!---------- Display the search results: End --------------->
<%
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>
|
<% @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>
|
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More Database Code Articles
More By aspfree  developerWorks - FREE Tools!
|
The IBM DB2 Deep Compression ROI tool is designed for DBA’s and IT management personnel to perform a clinical analysis of the cost savings gained from the Storage Optimization feature of DB2 9 for Linux, UNIX and Windows. The feature, also known as Deep Compression, compresses data that lies within a database by up to 80% at times.
FREE! Go There Now!
| | | |
Join this Rational Talks to You teleconference, featuring Paul Boustany and Mark Krasovich, to speak to the experts about becoming a Rational ClearCase power user. Get a chance to ask your questions and learn tips and tricks for using Rational ClearCase in Agile development
FREE! Go There Now!
| | | |
Download the Rational Application Developer (RAD) v7.5 open beta code and start developing applications for the JEE5 standard which features EJB3.0, JPA, JSF 1.2, JSP 2.1 and Servlet 2.5 standards. When you use this beta you will see how you can increase developer productivity for already existing applications with improved support for refactoring, as well as adding new features to existing applications. In addition, the beta provides tooling for JD Edwards, Oracle, SAP, Siebel and PeopleSoft to improve the developer productivity with these enterprise systems.
FREE! Go There Now!
| | | |
Asset Reuse is a key strategy for companies looking to create innovative solutions to solve complex software development problems. Searching for, identifying, updating, using and deploying software assets can be a difficult challenge. Listen to this webcast, to learn about strategies and tools that you can leverage for a successful project, including Rational Asset Manager, Rational Software Architect and WebSphere Service Registry and Repository.
FREE! Go There Now!
| | | |
This webcast outlines the best practices that must be instituted to gain the maximum benefit from SOA while maintaining high quality of service. Whether you are deploying new applications or managing and monitoring your existing infrastructure, learn how you can ensure high quality of services with SOA based solutions from IBM. All registrants who attend this live Web Seminar will receive complimentary access to a white paper titled “Maintaining QoS in an SOA Environment”.
FREE! Go There Now!
| | | |
Portfolio Management is about effectively managing portfolio value by aligning portfolio investments with business goals. This complimentary e-kit provides a collection of materials that can help you understand how IBM Rational enables and automates best practices for improved governance and clear visibility into portfolio and project performance across the entire IT project lifecycle.
FREE! Go There Now!
| | | |
This Fall, IBM Rational talks to you directly through a special teleconference series giving you access to the best minds in IBM Rational - product experts and market thought leaders who will answer your questions during these pre-scheduled telephone conference calls. Register today!
FREE! Go There Now!
| | | |
Rational Build Forge Express Edition is an automation framework that packages the latest enterprise-grade technologies into a reliable, flexible and robust configuration designed and priced specifically for small to midsize businesses. The new Rational Build Forge Express eKit provides you with valuable resources – including a case study, podcast, demo, and articles – to help you increase staff productivity, compress development cycles and deliver better software, fast.
FREE! Go There Now!
| | | |
Attend this launch webcast with Scott Hebner, Vice President of IBM Rational Marketing and Strategy, where he will overview Rational’s new offerings and programs to help customers accelerate software innovation on System z. He will discuss how these solutions help organizations extend their core business processes toward modern architectures such as SOA and web technologies to deliver business improvements that stand the test of time.
FREE! Go There Now!
| | | |
Viper 2 brings a great value to developer communities including SQL, XML, PHP, Ruby, .NET and Java. You probably already know that DB2 Express-C is free for developers to develop, deploy and distribute. Viper 2 provides a variety of means that help move your application from the development stage to deployment more rapidly. This webcast shows how to best utilize the latest tools available for developing DB2 applications.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
/ 29 
