Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jul 2004
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating a search function! help please.

    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)

    thanks

  • #2
    Regular Coder
    Join Date
    Aug 2002
    Location
    USA
    Posts
    478
    Thanks
    0
    Thanked 2 Times in 2 Posts
    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
    J. Paul Schmidt
    www.Bullschmidt.com - Freelance Web and Database Developer
    www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips

  • #3
    Regular Coder
    Join Date
    Jul 2004
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #4
    Regular Coder
    Join Date
    Aug 2002
    Location
    USA
    Posts
    478
    Thanks
    0
    Thanked 2 Times in 2 Posts
    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")
    Else
    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"
    J. Paul Schmidt
    www.Bullschmidt.com - Freelance Web and Database Developer
    www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips

  • #5
    Regular Coder
    Join Date
    Jul 2004
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    IF NOT objRS.EOF THEN
    'objRS.Filter=objRS("CourseStatus")=1
    %>

    ' 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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •