Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
09-29-2012, 04:07 PM #1
- Join Date
- Sep 2012
- Thanked 0 Times in 0 Posts
How to return all data when no variable specified in SQL WHERE?
This is my first time programming in ASP . Just hit a bit of a road block and wondered if someone could steer me in the right direction!
I have a table of data which is populated from an SQL DB. At the top I have a number of drop downs which allow users to filter the table results. I have my SQL statement laidout like this:
mySQL = "SELECT * FROM INCIDENTS WHERE H#DEPT1 ='" & department & "' AND WHERE H#USERNAME1 ='" & username & "' "
This works great as I have a submit button which passes the variable into the SQL WHERE clause when the page loads. If I fill out all the drop down boxes it works perfectly.
However.. what can I do if I wanted to leave one of the drop downs blank? In this case I would like to return all the data for that filter. Is there anyway I can manipluate the WHERE clause so that it works like this:
WHERE H#DEPT1 ='" & department & " OR IF NOT SET & department & SHOW ALL RESULTS ?
Any assitance would be most appreciated!
Last edited by deanparkr; 09-29-2012 at 04:11 PM.
10-04-2012, 11:49 PM #2
The best way is to not even include the condition in the SQL if it's not specified.
SQL = "SELECT * FROM INCIDENTS WHERE 1=1 " If department <> "" Then SQL = SQL & " AND H#DEPT1 = '" & Replace(department,"'","''") & "' " End If If username <> "" Then SQL = SQL & " AND H#USERNAME1 = '" & Replace(username,"'","''") & "' " End IfBe yourself. No one else is as qualified.
10-05-2012, 12:38 AM #3
What is this "H#" bit? That's not a valid SQL Server field name, with the # in it.Be yourself. No one else is as qualified.