Database search form in ASP

Hi there, I'm very new to ASP but due to a deadline given to me at work I've had to try and learn fast with the help of sample code etc.

I have a knowledge base database with three tables, two of which are used for lookup purposes on the main "Problems" table. I have designed a form on a web page which can be used to add records to this database. I also have an asp page which displays these records in pages. However, I am struggling with the search form. The SQL statement to display all the records in the database is:

SELECT Problems.id, Problems.error, Categories.Category, Programs.Program, Problems.description, Problems.resolution FROM Problems, Categories, Programs WHERE Categories.CategoryRef = Problems.categoryref AND Programs.ProgramRef = Problems.programref

I have designed a search form where the user specifies the error, category, program and some keywords. I want to pass these details into a SQL statement similar to the below.

SELECT Problems.id, Problems.error, Categories.Category, Programs.Program, Problems.description, Problems.resolution FROM Problems, Categories, Programs WHERE Categories.CategoryRef = Problems.categoryref AND Programs.ProgramRef = Problems.programref AND Problems.error LIKE FormErrorField AND Categories.categoryref = FormCategoryField AND Programs.programref = FormProgramField AND Problems.description LIKE FormKeywordsField AND Problems.resolution LIKE FormKeywordsField

I'd then like it to either display on th same page or feed the resulting SQL statement into another asp page similar to the one I use to display all records.

I've seen various ways of doing this but have been unable to customize the examples I have found to my needs. I need to get this done within a week and am struggling a bit. I can provide the files for the site I've already done if necessary.

Hope I've made some sense.

Many Thanks

Steve

You could try something like this, but it depends on the contents of the KeywordField.

And here's a related link I put together that hopefully might give you an idea or two...

ASP Design Tips - Search For an Expression on Multiple Fields
http://www.bullschmidt.com/devtip-s...tiplefields.asp

Thanks guys, I'll give them a go.

I've adapted some code I found and it seems to be ok. However, when I submit the query it just refreshes the screen instead of displaying the table as specified. Any ideas? Here's the code.


<%
' 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 strSearchErr ' The text being looked for
Dim strSearchCat ' The text being looked for
Dim strSearchPro ' The text being looked for
Dim strSearchKey ' 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 the database</p>
<form action="<%= strURL %>" method="get">

<table class=problems>
<tr class=forms>
<td><font size=2 face=verdana><b>Error: </b></font></td>
<td><input name="error" value="<%= strSearchErr %>" /></td>
</tr>
<tr><tr>
<tr class=forms>
<td><font size=2 face=verdana><b>Category: </b></font></td>
<td>
<select name="category" value="<%= strSearchCat %>" />
<option selected>---Please choose one of the below---</option>
<option value="1">Citrix/Terminal Server</option>
<option value="2">Client Transfer</option>
<option value="3">Database Corruption</option>
<option value="4">Hardware/Network Issues</option>
<option value="5">Installation/Upgrade</option>
<option value="6">Office Integration/Mailmerge</option>
<option value="7">Price Feeds/E-Business</option>
<option value="8">Reporting</option>
<option value="9">SQL Server</option>
</select>
</td>
</tr>
<tr><tr>
<tr class=forms>
<td><font size=2 face=verdana><b>Program: </b></font></td>
<td>
<select name="program" value="<%= strSearchPro %>" />
<option selected>---Please choose one of the below---</option>
<option value="1">AO - Client Management</option>
<option value="2">AO - Client Transfer</option>
<option value="3">AO - Citrix/Terminal Server</option>
<option value="4">AO - Installation</option>
<option value="5">AO - Office Integration - Excel</option>
<option value="6">AO - Office Integration - Outlook</option>
<option value="7">AO - Office Integration - Word</option>
<option value="8">AO - Letter Writer</option>
<option value="9">AO - Outside Services/Links</option>
<option value="10">AO - Reports</option>
<option value="11">AO - SQL Server</option>
<option value="14">AO - Taskbar/Logging In</option>
</select>
</td>
</tr>
<tr><tr>
<tr class=forms>
<td valign=top><font size=2 face=verdana><b>Keywords: </b></font></td>
<td><textarea cols="38" rows="5" name="keywords" value="<%= strSearchKey %>" /></textarea></td>
</tr>
</table>
<p>&nbsp;</p>
<input type="submit" />
</form>


<p>Please work!!</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("techservices.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=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"

' We're actually using SQL Server so we use this line instead:
cnnSearch.Open "Provider=SQLOLEDB;Data Source=10.2.1.214;" _
& "Initial Catalog=samples;User Id=samples;Password=password;" _
& "Connect Timeout=15;Network Library=dbmssocn;"


' Build our query based on the input.
strSQL = "SELECT A.id, A.error, B.Category, C.Program, A.description, A.resolution " _
& "FROM Problems As A " _
& "INNER JOIN Catgory As B ON (A.CategoryRef = B.CategoryRef) " _
& "INNER JOIN Programs As C ON (A.ProgramRef = C.ProgramRef) " _
& "WHERE A.error LIKE '%" & Request.Form("error") & "%' " _
& "AND (A.CategoryRef = '" & Request.Form("category") & "') " _
& "AND (A.ProgramRef = '" & Request.Form("program") & "') " _
& "AND (A.description LIKE '%" & Request.Form("keywords") & "%') " _
& "AND (A.resolution LIKE '%" & Request.Form("keywords") & "%');"

' Execute the 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>ID</th>
<th>Error</th>
<th>Category</th>
<th>Program</th>
<th>Description</th>
<th>Resolution</th>
</tr>
<%
Do While Not rstSearch.EOF
%>
<tr>
<td><%= rstSearch.Fields("id").Value %></td>
<td><%= rstSearch.Fields("error").Value %></td>
<td><%= rstSearch.Fields("category").Value %></td>
<td><%= rstSearch.Fields("program").Value %></td>
<td><%= rstSearch.Fields("description").Value %></td>
<td><%= rstSearch.Fields("resolution").Value %></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

%>

Seems like your form sends a querystring (<form action="<%= strURL %>" method="get">) but that you are initially checking for a querystring item based on a field that doesn't exist (strSearch = Request.QueryString("search")) and then later are checking for posted items (Request.Form("error")...).

Personally I like search dialogs go post back to themselves:

ASP Design Tips - Post Back Page
http://www.bullschmidt.com/devtip-postbackpage.asp

I've just noticed that

If strSearch <> "" Then

is invalid as I haven't declared strSearch. Is there a way I can declare this and set it as something like below?

strSearch = strSearchErr & strSearchCat & strSearchPro & strSearchKey

Got this sorted now. Thanks for your help guys