Source Code for Nevada State & Local Publications Database Search
To adapt this code for use with your database, copy everything below the
horizontal line and paste it into your text editor or Source code/HTML view in a
WYSIWYG editor. Be sure to remove the <plaintext> tag if you copied and
pasted it with the code!
Obviously, this code was written to work with a particular database, so if
you want to try it, you'll need to adapt the field names to what exists in your
database. A complete description of the database and ASP application will be
published in the next issue of The Journal of Government Information.
To make this code work, you would need a database with two tables, one for
Agency and one for Titles. You can get the field names from the code, along with
the fields that join the two tables.
The code block belongs within the <body> tag.
Remember to put the following line ABOVE the <html> tag:
<%@language="VBScript"%>
You can use stylesheets or styles, and add additional HTML coding above and
below the code block to make the page look the way you want.
Save the page with a .asp extension.
The search form page can have a .html extension, but the form must specify
the ASP page in it's ACTION parameter. In our case, the HTML form is on the
page: http://www.library.unr.edu/depts/bgic/guides/government/nevada/nevada.html,
and the ASP page is nvlookup.asp, which is in the same directory. The opening
form tag looks like this: <form method="get"
action="nvlookup.asp">
For security reasons, the name of the data source file has been changed.
<![CDATA[
<%
'************************************************************************
'ASP Code for searching Nevada State & Local Publications Database
'University of Nevada, Reno, University Library, Business & Government
'Information Center.
'Terms of Use:
'This code may be copied or modified for non-commercial uses only,
'provided that this copyright notice remains intact. The code may not
'be sold or distributed with commercial applications.
'Author: Araby Greene, Getchell Library/322, University of Nevada, Reno
'?2000
'************************************************************************
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "yourDSN","",""
dim p_title
dim p_agency
dim p_cn
dim p_boolean1
dim p_boolean2
dim p_title2
dim p_agency2
dim strSQL
'In the following function, we replace chr(34) (quotation marks) with "
'because we have replaced quotation marks in the database with the entity.
'Search results can then be output without using Server.HTMLencode(rs("fieldname"))
'and the page is valid xhtml.
Function newstr(str)
newstr = Replace(Replace(str, "'", "''"), CHR(34), """)
End Function
p_title = newstr(Request.QueryString("p_title"))
p_title2 = newstr(Request.QueryString("p_title2"))
p_agency = Request.QueryString("p_agency")
p_cn = Request.QueryString("p_cn")
p_boolean1 = Request.QueryString("p_boolean1")
p_boolean2 = Request.QueryString("p_boolean2")
p_agency2 = Request.QueryString("p_agency2")
strSQL = "SELECT title.title, title.agencyid, title.cutter, title.titlenote, title.titlelink, " &_
"agency.agency, agency.agencylink, agency.agencynote " &_
"FROM agency INNER JOIN title ON agency.id = title.agencyid "
'construct sql string from if statements depending on user input
'first see if any search terms have been entered
If p_title = "" AND p_agency ="" AND p_cn = "" then
Response.Write "<h4>Please enter at least one search term!</h4>"
else
'start getting user entered search terms. Have to start with a WHERE clause so blank title will get ANY/ALL titles
If p_title2 = "" THEN
strSQL = strSQL & " WHERE ((title.title) Like '%" & p_title & "%') "
else
strSQL = strSQL & " WHERE (((title.title) Like '%" & p_title & "%')" & p_boolean1 & "((title.title) Like '%" & p_title2 & "%')) "
End If
'get agency term
If (p_agency2 = "" AND p_agency <> "") Then
strSQL = strSQL & " AND ((agency.agency) Like '%" & p_agency & "%') "
End If
If p_agency2 <> "" AND p_agency <> "" THEN
strSQL = strSQL & " AND (((agency.agency) Like '%" & p_agency & "%')" & p_boolean2 & "((agency.agency) Like '%" & p_agency2 & "%')) "
End If
'get callnumber term
If p_cn <> "" Then
strSQL = strSQL & " AND ((Trim(title.agencyid) & ' ' & Trim(title.cutter)) Like '" & p_cn & "%') "
End If
'finish the sql query string with sort order
strSQL = strSQL & " ORDER by title.title,title.agencyid,title.cutter "
'create the recordset
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL, conn, 0, 1
On Error Resume Next
rs.MoveFirst
'create a friendly message if no records found
if rs.EOF then
Response.Write "<h4>Sorry, no records matched your search.</h4>"
else
%>
<h2>Nevada State Publications Bibliography<br>
Search Results</h2>
<table border="0" bgcolor="#ffffff" cellspacing="0">
<font face="Arial" color="#000000">
<tbody>
<%
End If
do while Not rs.eof
%>
<tr valign="TOP">
<td><%=rs("title"))%> <%=rs("titlelink")%><br>
<b><%=rs("agencyid") & " "%><%=rs("cutter")%></b>
<%=rs("titlenote"))%><br>
<%=rs("agency") & ". "%><%=rs("agencylink")%><br>
<%=rs("agencynote")%>
<p> </p>
</td>
</tr>
<%
rs.MoveNext
loop
conn.close
set conn = nothing
rs.close
set rs = nothing
End If
%>
</tbody>
</font>
</table>
</body>
</html>]]></plaintext></body></div>