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

[deanparkr]

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!

[Old Pedant]

Various ways.

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

For example:

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

[Old Pedant]

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