<% 'Check that the user should be allowed to access this page. 'If not, send them to another page. 'If Session("ReadWrite") = "Read" Then 'the user is not validated to be here. Redirect to the Guest Page 'Response.Redirect "../../guest_main.asp" 'End If %>

home | email  
 

Enter your search criteria below, then click the corresponding 'Search' button. The 'Use All Criteria' button will search the database using all selected criteria.

 * Title and Key word searches work only for an exact word or phrase. For example, entering 'wild trout' in the title will search for an exact match such as 'Wild Trout in Montana Rivers'. It will not find titles such as 'Trout in Wild Rivers'.
<% 'Create the list boxes from the database Dim DataConnection2, RS2 Set DataConnection2 = Server.CreateObject("ADODB.Connection") DataConnection2.Open "DSN=MRCDC" Set cmdDC = Server.CreateObject("ADODB.Command") cmdDC.ActiveConnection = DataConnection2 'Document Types SQL = "SELECT doc_Type FROM Documents GROUP BY doc_Type" cmdDC.CommandText = SQL Set RS2 = Server.CreateObject("ADODB.Recordset") RS2.Open cmdDC, , 0, 2 'Check to see that doc types were selected. If so, add them to the Select List if RS2.eof then Response.Write "Error: No doc_Types found." else Response.Write "" Response.Write "" Response.Write "" Response.Write "" end if 'Location SQL = "SELECT spatial_general_extent FROM qry_Documents GROUP BY spatial_general_extent" cmdDC.CommandText = SQL Set RS2 = Server.CreateObject("ADODB.Recordset") RS2.Open cmdDC, , 0, 2 'Check to see that spatial extents were selected. If so, add them to the Select List if RS2.eof then Response.Write "Error: No Publishers found." else Response.Write "" Response.Write "" Response.Write "" Response.Write "" end if 'Title Words Response.Write "" Response.Write "" Response.Write "" 'Key Words Response.Write "" Response.Write "" 'All Criteria Response.Write "" %>
Document Types" Response.Write "
General Location" Response.Write "
Title Words*
Key Words*
" Response.Write "

<% Dim DataConnection1, cmdDC1, RecordSet1 Dim RecordToEdit, Updated, strUserName, strPassword '-- Create object and open database Set DataConnection1 = Server.CreateObject("ADODB.Connection") DataConnection1.Open "DSN=MRCDC" Set cmdDC1 = Server.CreateObject("ADODB.Command") cmdDC1.ActiveConnection = DataConnection1 SQL = "SELECT * FROM Documents" cmdDC1.CommandText = SQL Set RS1 = Server.CreateObject("ADODB.Recordset") RS1.Open cmdDC1, , 0, 2 display = "no" If Request.Form("btnSearch") = "Search Only Selected Type" Then '-- default SQL SQL = "SELECT * FROM qry_Documents WHERE doc_Type = '" & Request.Form("TypeList") & "'" cmdDC1.CommandText = SQL Set RS1 = Server.CreateObject("ADODB.Recordset") RS1.Open cmdDC1, , 0, 2 display = "yes" elseif Request.Form("btnSearch") = "Search Only General Location" Then SQL = "SELECT * FROM qry_Documents WHERE spatial_general_extent = '" & Request.Form("GeneralExtentList") & "'" cmdDC1.CommandText = SQL Set RS1 = Server.CreateObject("ADODB.Recordset") RS1.Open cmdDC1, , 0, 2 display = "yes" elseif Request.Form("btnSearch") = "Search Title Only" Then SQL = "SELECT * FROM qry_Documents WHERE doc_Title Like '%" & Request.Form("txtTitleWords") & "%'" cmdDC1.CommandText = SQL Set RS1 = Server.CreateObject("ADODB.Recordset") RS1.Open cmdDC1, , 0, 2 display = "yes" elseif Request.Form("btnSearch") = "Search Only by Keyword" Then SQL = "SELECT * FROM qry_Documents WHERE doc_KeyWords Like '%" & Request.Form("txtKeyWords") & "%'" cmdDC1.CommandText = SQL Set RS1 = Server.CreateObject("ADODB.Recordset") RS1.Open cmdDC1, , 0, 2 display = "yes" elseif Request.Form("btnSearch") = "Use All Criteria" Then 'Build the WHERE clause and query the database. whereClause = "WHERE " includeAND = "no" 'Check the document Type if Request.Form("TypeList") = "" Then whereClause = whereClause Else whereClause = whereClause & "doc_Type = '" & Request.Form("TypeList") & "' " includeAND = "yes" End if 'Check the spatial extent if Request.Form("GeneralExtentList") = "" Then whereClause = whereClause Else if includeAND = "yes" Then whereClause = whereClause & " AND spatial_general_extent = '" & Request.Form("GeneralExtentList") & "' " else whereClause = whereClause & " spatial_general_extent = '" & Request.Form("GeneralExtentList") & "' " end if includeAND = "yes" End if 'Check the title words. if Request.Form("txtTitleWords") = "" Then whereClause = whereClause Else if includeAND = "yes" Then whereClause = whereClause & " AND doc_Title Like '%" & Request.Form("txtTitleWords") & "%' " else whereClause = whereClause & " doc_Title Like '%" & Request.Form("txtTitleWords") & "%' " end if includeAND = "yes" End if 'Check the key words. if Request.Form("txtKeyWords") = "" Then whereClause = whereClause Else if includeAND = "yes" Then whereClause = whereClause & " AND doc_KeyWords Like '%" & Request.Form("txtKeyWords") & "%' " else whereClause = whereClause & " doc_KeyWords Like '%" & Request.Form("txtKeyWords") & "%' " end if includeAND = "yes" End if if whereClause = "WHERE " Then whereClause = "" End if SQL = "SELECT * FROM qry_Documents " & whereClause cmdDC1.CommandText = SQL Set RS1 = Server.CreateObject("ADODB.Recordset") RS1.Open cmdDC1, , 0, 2 display = "yes" end if myCount = 0 while not rs1.eof myCount = myCount + 1 rs1.movenext wend if myCount <> 0 Then rs1.movefirst End if response.write "Your search returned " & myCount & " documents.
" 'response.write sql if display = "yes" Then While Not RS1.EOF myRecID = RS1.Fields("Document_ID") Response.Write "Document Type: " & RS1.Fields("doc_Type") & "
" Response.Write "Title: " & RS1.Fields("doc_Title") & "
" Response.Write "Author(s): " & RS1.Fields("doc_Authors") & "
" Response.Write "Date: " & RS1.Fields("doc_Date") & "
" 'Response.Write " Show Full Record" Response.Write "Record Details" Response.Write "
" RS1.MoveNext Wend end if %>

Problems? Questions?
If you are having problems with any of the database functions or have suggestions and/or improvements, please contact tony@dtmgis.com.

Copyright 2004 - DTM Consulting, Inc.. - All Rights Reserved