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!
|
Learn field-tested SOA principles, methodology, technology and implementation from the global SOA market leader - in a new e-book by an IBM SOA expert. Written by IBM Certified SOA Solution Designer Bobby Woolf, "Exploring IBM SOA Technology & Practice" is the ultimate insider's guide to SOA - a PDF e-book packed cover to cover with IBM's specific advice on how to make your SOA implementation a success.
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!
| | | |
Visit IBM developerWorks to download a free trial version of Lotus Quickr 8.0, which enables collaboration by transforming the way everyday business content such as documents, rich media, photos, and video can be shared. Lotus Quickr makes it faster and easier to share content of all types (not just documents) within virtual teams. It is designed to make it easier to collaborate across organizational boundaries, while continuing to work within the context of familiar desktop applications.
FREE! Go There Now!
| | | |
Download a free trial version of IBM Rational Developer for System z, software that can help you deliver core development capabilities; the power of Java Platform, Enterprise Edition (Java EE); and rapid application development support to diverse enterprise application development teams. With comprehensive development tools to help create, deploy and maintain traditional enterprise and composite applications, Rational Developer for System z enables developers with different technical backgrounds to easily participate in important technology projects.
FREE! Go There Now!
| | | |
Secure your Web applications with IBM Rational AppScan Standard Edition V7.7, previously known as Watchfire AppScan. This Web application security testing tool automates vulnerability assessments and scans and tests for common Web application vulnerabilities. Visit IBM developerWorks to download a free trial of IBM Rational AppScan Standard Edition V7.7.
FREE! Go There Now!
| | | |
XML has become a common way of storing business data as flat files and many data server vendors including IBM have provided ways to store this data within relational database systems. Increasingly collections of XML files are accessed like databases using an xQuery and other XML standard mechanisms. Businesses find the need to combine the traditional tabular structured data with XML formatted data. In this webcast, you’ll learn about IBM’s WebSphere Federation Server technology, which provides users with the ability to integrate these two data formats.
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!
| | | |
Join this Rational Talks to You teleconference on November 29 at 1:00 pm ET to participate in an interactive discusssion with Grady Booch around architecture and reuse. Get your questions answered!
FREE! Go There Now!
| | | |
This whitepaper provides areas to consider when evaluating any software configuration management solution. It addresses how the IBM solutions (Rational ClearCase and Rational ClearQuest) meet the needs and requirements of both project leaders and developers to provide successful Software Change and Configuration Management.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
/ 29 
