|
san
Date:: Jan 12, 2009
Time:: 18:51
|
Search.asp
<%
' Declare our variables... always good practice!
Dim strURL ' The URL of this page so the form will work
' no matter what this file is named.
Dim cnnSearch ' ADO connection
Dim rstSearch ' ADO recordset
Dim strDBPath ' path to our Access database (*.mdb) file
Dim strSQL ' The SQL Query we build on the fly
Dim strSearch ' The text being looked for
' Retreive the URL of this page from Server Variables
strURL = Request.ServerVariables("URL")
' Retreive the term being searched for. I'm doing it on
' the QS since that allows people to bookmark results.
' You could just as easily have used the form collection.
strSearch = Request.QueryString("search")
'strSearch = Replace(strSearch, "'", "''")
' Since I'm doing this all in one page I need to see if anyone
' has searched for something. If they have we hit the DB.
' O/W I just show the search form and quit.
%>
<p>Search for any details regarding SOST contact form. (% returns all)</p>
<form action="<%= strURL %>" method="get">
<input name="search" value="<%= strSearch %>" />
<input type="submit" />
</form>
<p>[Try 'am' or 'er' for an example]</p>
<%
If strSearch <> "" Then
' MapPath of virtual database file path to a physical path.
' If you want you could hard code a physical path here.
strDBPath = Server.MapPath("database.mdb")
' Create an ADO Connection to connect to the sample database.
' We're using OLE DB but you could just as easily use ODBC or a DSN.
set cnnSearch = Server.CreateObject("ADODB.Connection")
' This line is for the Access sample database:
cnnSearch.Open("Provider=SQLOLEDB.1;Password=helpdesk;Persist Security Info=True;User ID=HelpDesk;Initial Catalog=HelpDesk;Data Source=s9DCA3E")
' Build our query based on the input.
strSQL = "SELECT a.ProblemStatusId, a.ProblemStatus, e.DepartmentName, c.ProblemTitle, c.ProblemDescription " _
& "FROM (((ProblemsUsersDeptsReps d Inner Join Departments e on d.DepartmentId = e.DepartmentId) Inner Join Problems c on d.ProblemID = c.ProblemID) Inner Join ProblemStatus a on d.ProblemsUsersDeptsRepsId = a.ProblemsUsersDeptsRepsId) " _
& "WHERE a.ProblemStatus = 'CLOSED' " _
& "OR a.ProblemStatusId LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR e.DepartmentName LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR c.ProblemTitle LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR c.ProblemDescription LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR a.ProblemStatus LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "ORDER BY a.ProblemStatusId;"
' Execute our query using the connection object. It automatically
' creates and returns a recordset which we store in our variable.
Set rstSearch = cnnSearch.Execute(strSQL)
' Display a table of the data in the recordset. We loop through the
' recordset displaying the fields from the table and using MoveNext
' to increment to the next record. We stop when we reach EOF.
' For fun I'm combining some fields and showwing you can do more then
' just spit out the data in the form it is in in the table.
%>
<table border="1">
<tr>
<th>ProblemID</th>
<th>Department</th>
<th>Title</th>
<th>Description</th>
<th>Status</th>
<th>View</th>
</tr>
<%
Do While Not rstSearch.EOF
%>
<tr>
<td><%= rstSearch.Fields("ProblemStatusId").Value %></td>
<td><%= rstSearch.Fields("DepartmentName").Value %></td>
<td><%= rstSearch.Fields("ProblemTitle").Value %></td>
<td><%= rstSearch.Fields("ProblemDescription").Value %></td>
<td><%= rstSearch.Fields("ProblemStatus").Value %></td>
<%
Dim ProblemStatusID
ProblemStatusID=Request.QueryString("ProblemStatusId") %>
<td><a href="problemdetails.asp?ProblemStatusID=<%= rstSearch.Fields("ProblemStatusId").Value %> ">View</a></td>
</tr>
<%
rstSearch.MoveNext
Loop
%>
</table>
<%
' Close our recordset and connection and dispose of the objects
rstSearch.Close
Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing
End If
%>
|