View Full Version : Creating a search function! help please.

07-09-2004, 07:41 PM
hi, i have this courselist script located at http://www.startbar.co.uk/coursemanager/courses.asp which uses info from a ms access database etc..

i would like to know how i can create a search function for users to search by fields like course title, venue, date etc..

help appriciated (im a newbie)


07-09-2004, 09:35 PM
Well perhaps before that page you could have a search page with the fields you want the user to be able to search on.

And then on the main page that shows multiple records you could revise your SQL statement accordingly.

Example on a page receiving a post of a SQL statement based on two listboxes (Rep and Customer) that might each have a value of "" (i.e. blank) to show all with no criteria restrictions.

' Get posted form vars.
Rep = Request.Form("Rep")
Customer = Request.Form("Customer")

' Set strSQL.
strSQL = "SELECT * FROM MyTable WHERE (1=1)"
If Rep <> "" Then
strSQL = strSQL & " AND (Rep='" & Rep & "')"
End If
If Customer <> "" Then
strSQL = strSQL & " AND (Customer='" & Customer & "')"
End If

Response.Write "strSQLWhere: " & strSQLWhere

And the (1=1) above is there as a placeholder (which doesn't affect the results since it is always true) as the SQL statement possibly may use AND with criteria after it or possibly the SQL statement may not have anything after it. For example a SQL statement without the (1=1) like this would not work: SELECT * FROM MyTable WHERE AND Customer='API'

And to see something like this in action you can do the following:
- Go to http://www.bullschmidt.com/login.asp (the ASP Web database demo's login page)
- Click the Continue button
- On the Main Menu click the Invoices Edit button
- On the Invoices Edit Search Dialog's Customer listbox notice that you can choose a rep and/or customer and then click the Edit button to see all the invoices for that rep and/or customer

07-09-2004, 09:53 PM
thanks for your help.. unfortunalty im not very experienced with this..

i just need a simple text box and a search button that can be used to search keywords in the course title fields or whatever. but also if someone search for "duchy college" (one of the venues) that would show all courses with that venue...

so what complete code would i need? sorry but im a newbie!!

help very much appriciated

07-10-2004, 12:14 AM
Let's say your SQL statement already is something like this:

' Set strSQL.
strSQL = "SELECT * FROM MyTable ORDER BY CourseDate DESC"

Well now you could have the page post back to itself by adjusting the form tag accordingly:

<form id="frmMain" name="frmMain" action="<%= Request.ServerVariables("SCRIPT_NAME") %>" method="post">

And then change the SQL statement to perhaps be more like this:

' Get posted form vars.
If Request.Form("btnSearch") <> "" Then
CourseName = Request.Form("CourseName")
CourseName = ""
End If

' Set strSQL.
strSQL = "SELECT * FROM MyTable WHERE (1=1)"
If CourseName <> "" Then
strSQL = strSQL & " AND (CourseName='" & CourseName & "')"
End If
strSQL = strSQL & " ORDER BY CourseDate DESC"

07-10-2004, 07:11 PM
im after a little search box below the list. my code at the moment is

<%if pageView="" THEN

dim ordervariable, sortorder
ordervariable = "CourseStartDate" 'default column to sort on
sortorder = "ASC" 'default sortorder

if (len(request.querystring("sortby")) >= 1) then
ordervariable = request.querystring("sortby")
end if
if (len(request.querystring("sortorder")) >= 1) then
sortorder = request.querystring("sortorder")
end if

strSQL="SELECT * FROM Courses WHERE CourseStatus=1 ORDER BY " & ordervariable & " " & sortorder

' Open the Database
objRS.Open strSQL, objConn, adOpenKeyset, adLockPessimistic, adCmdText

' the table with the finished data in it!

<tr><td><b><img src="coursename.gif"> <a href="courses.asp?sortby=CourseTitle">Course Name</b></a> <a href="courses.asp?sortby=CourseTitle&sortorder=ASC"><img src="up.gif" border="0"></a> <a href="courses.asp?sortby=CourseTitle&sortorder=DESC"><img src="down.gif" border="0"></a></td>
<td><b><a href="courses.asp?sortby=CourseStartDate">Date</b></a> <a href="courses.asp?sortby=CourseStartDate&sortorder=ASC"><img src="up.gif" border="0"></a> <a href="courses.asp?sortby=CourseStartDate&sortorder=DESC"><img src="down.gif" border="0"></a></td>
<td><b><a href="courses.asp?sortby=CourseVenue">Venue</b></a> <a href="courses.asp?sortby=CourseVenue&sortorder=ASC"><img src="up.gif" border="0"></a> <a href="courses.asp?sortby=CourseVenue&sortorder=DESC"><img src="down.gif" border="0"></a></td>
<td><b><a href="courses.asp?sortby=CourseAvailability&sortorder=DESC">Available</b></a></td> etc....

so do you modify this code or add new code for the search function? im confused? can you show me please? thanks