asked on

Dreamweaver ASP Search Problem

Hi

I have a small web application in ASP talking to a MySQL database. This application has been developed using Macromedia Dreamweaver MX 2004.
I am trying to write a search/results page which gives mulitiple options in the search and need some help please

I would like to search on location, allocation and date range, I am part way through writing the results page currently searching on location and allocation which does work however...
On the search page where the users will be able to click on drop down I have put the first option for each as ALL so if they only want to search on allocation in theory the location will bring back all the results allocated to x in all locations. For both location and allocation i have put "ALL" as the option value. How do I incorporate this into the code?

any help would be greatly apprecited.
thanks.

<%
Dim jobs__MMColParam
jobs__MMColParam = "1"
If (Request.Form("location1")    <> "") Then 
  jobs__MMColParam = Request.Form("location1")   
End If
%>
<%
Dim jobs__MMColParam1
jobs__MMColParam1 = "1"
If (Request.Form("allocation1") <> "") Then 
  jobs__MMColParam1 = Request.Form("allocation1")
End If
%>
<%
Dim jobs
Dim jobs_numRows

Set jobs = Server.CreateObject("ADODB.Recordset")
jobs.ActiveConnection = MM_sgmaintenance_STRING
jobs.Source = "SELECT *  FROM jobs  WHERE location = '" + Replace(jobs__MMColParam, "'", "''") + "' and joballocation ='" + Replace(jobs__MMColParam1, "'", "''") + "' and status <> 'complete' and status <> 'rejected'  ORDER BY Id DESC"
jobs.CursorType = 0
jobs.CursorLocation = 2
jobs.LockType = 1
jobs.Open()

jobs_numRows = 0
%>

Open in new window

tobzzz

You could add a variable that will only write the "and location = etc" in the SQL IF the user didn't search by 'All Locations'
So line 21 would be:
If request.form("location")="All" then sqlExtra="" else sqlExtra = " and location = '" + Replace(jobs__MMColParam, "'", "''") + "'"
jobs.Source = "SELECT * FROM jobs WHERE status <> 'complete' and status <> 'rejected' " & sqlExtra & " ORDER BY Id DESC"

/ Tobzzz

bains1000

ASKER

Tobzzz you are a genius!! Thank you that worked a treat.
I have modified the script to work with the second variable, would this be the best way to do this?

If request.form("allocation1")="All" then sqlExtra1="" else sqlExtra1 = " and joballocation = '" + Replace(jobs__MMColParam1, "'", "''") + "'"
If request.form("location1")="All" then sqlExtra="" else sqlExtra = " and location = '" + Replace(jobs__MMColParam, "'", "''") + "'"
jobs.Source = "SELECT *  FROM jobs WHERE status <> 'complete' and status <> 'rejected' " & sqlExtra & sqlExtra1 & " ORDER BY Id DESC"

Open in new window

ASKER CERTIFIED SOLUTION

tobzzz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bains1000

ASKER

Superb assistance thanks for your help.