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!
|
Hold your calendar on January 30, 2008 for this free webcast on the new i5/OS. Rational's Enterprise Modernization products will be discussed at this webcast as they help to drive the application development environment for this new System i OS. <br />And learn how i5/OS will take you to the next step of efficient, resilient business processing. You will hear about the new i5/OS capabilities as it will be the most significant i5/OS release in years. If you cannot join the webcast on 1/30/08 you can still use this link to listen to the replay.<br />
FREE! Go There Now!
| | | |
<a href="http://zeus.developershed.com/shonuff.php?blackbird=3853&zoneid=442&source=&dest=http%3A%2F%2Fwww.ibm.com%2Fdeveloperworks%2Fspaces%2Fjazz%3FS_TACT%3D105AGY31%26S_CMP%3DDEVSHED&ismap="><img src="http://images.devshed.com/corp/img/news/jazz01.gif" alt="developerWorks Jazz space" align="left"></a>You've heard the buzz about Jazz... want to know more about it from a developer's perspective? Check out the Jazz space on developerWorks. This space is an up-to-date resource for developers, including technical information about Jazz and products built on Jazz, like Rational Team Concert Express. The Jazz space includes content from a wide variety of sources, including links, feeds, and comments from experts.
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!
| | | |
You probably have thousands of lines of COBOL code loaded with business intelligence and being used to run your business, along with an army of developers maintaining these applications. Learn how to prepare your applications and developers so you can keep that competitive edge and move to a service-oriented architecture with the IBM Rational Enterprise Modernization solutions. Replay is available for 9 months.
FREE! Go There Now!
| | | |
This whitepaper presents the benefits of successfully introducing static analysis into your organization using IBM Rational Software Analyzer. Additionally, it identifies some common pitfalls that can hinder the effective use of static analysis tooling as well as presents 10 simple strategies designed to help you quickly realize the value of static analysis using Rational Software Analyzer.
FREE! Go There Now!
| | | |
Learn how you can extend modern application lifecycle management to IBM System z through the IBM Rational Software Delivery Platform (SDP). The Did you say mainframe? e-kit includes podcasts, webcasts, tutorials, white and red papers, demos, and articles designed to help ease the challenges of modernizing your enterprise. This complimentary kit for mainframe developers is a practical, how-to guide for making the most of an existing development environment, including the skills and infrastructure already in place at an established enterprise.
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!
| | | |
Visit IBM developerWorks to download the latest trial version of IBM Data Studio V1.1 at no cost. IBM Data Studio is a comprehensive data management solution that helps you effectively design, develop, deploy and manage your data, databases, and database applications throughout the data management life cycle utilizing a consistent and integrated user interface. Unlike other client-side data management solutions that focus on only one aspect of the application lifecycle or database administration, Data Studio complements the Rational Software Delivery platform, providing unparalleled flexibility for a heterogeneous data server environment across platforms.
FREE! Go There Now!
| | | |
Join this Rational Talks to You teleconference, to hear how Enterprise Generation Language (EGL) eliminates the need for tedious and error-prone low level coding, so developers can focus on business requirements. EGL extends the Rational software development platform with a simplified programming language that enables developers who have little or no experience with Java, Web technologies or Service Oriented Architecture, to create enterprise-class applications and services quickly and easily. It also allows developers who may have little or no mainframe programming experience to quickly create traditional mainframe components.
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 
