Dreamweaver ASP Search Problem


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.
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_numRows = 0

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
bains1000Author Commented:
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

What you;ve done there is fine, a shorter way of writing it though would be:
If request.form("allocation1")<>"All" then sqlExtra = " and joballocation = '" + Replace(jobs__MMColParam1, "'", "''") + "'" 
If request.form("location1")<>"All" then sqlExtra = sqlExtra & " and location = '" + Replace(jobs__MMColParam, "'", "''") + "'" 
jobs.Source = "SELECT *  FROM jobs WHERE status <> 'complete' and status <> 'rejected' " & sqlExtra & " ORDER BY Id DESC"

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bains1000Author Commented:
Superb assistance thanks for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.