CodingForums.com - How to return all data when no variable specified in SQL WHERE?

CodingForums.com (http://www.codingforums.com/index.php)

- ASP (http://www.codingforums.com/forumdisplay.php?f=8)

- - How to return all data when no variable specified in SQL WHERE? (http://www.codingforums.com/showthread.php?t=274701)

How to return all data when no variable specified in SQL WHERE?

Hi all

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! :D

Various ways.

The best way is to not even include the condition in the SQL if it's not specified.

For example:

Code:

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 If

What is this "H#" bit? That's not a valid SQL Server field name, with the # in it.