• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

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

0
bains1000
Asked:
bains1000
  • 2
  • 2
1 Solution
 
tobzzzCommented:
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
0
 
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

0
 
tobzzzCommented:
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

0
 
bains1000Author Commented:
Superb assistance thanks for your help.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now