Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Sep 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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!
    Last edited by deanparkr; 09-29-2012 at 04:11 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    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
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    What is this "H#" bit? That's not a valid SQL Server field name, with the # in it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •