#1
  1. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22

    ignore search field if empty?


    I have a search form with three fields that will search a MS SQL database. Two fields are text fields and one is a multiple select box. If no value is ever entered into a text field, I need to ignore it in the SQL query... The way it stands now, the query I am generating looks like this:

    SELECT ItemID, Description, QuantityOnHand, ListPrice, Category FROM Merchandise WHERE ItemID = AND Description LIKE AND category IN(Bird,Cat)

    because it is looking for a value for the two text fields ItemID and Description, which have not been entered...

    Any solutions to this problem? THANKS IN ADVANCE!
    Last edited by mateoc15; April 8th, 2004 at 11:31 AM.
    Discontent is the first necessity of progress. - Edison
  2. #2
  3. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22
    Furthermore... how would I get the search to return ALL rows if no criteria were entered? Just check each one with a CFIF and then do a SELECT * FROM... ?
    Discontent is the first necessity of progress. - Edison
  4. #3
  5. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,242
    Rep Power
    967
    Something like:

    SELECT ItemID, Description, QuantityOnHand, ListPrice, Category
    FROM Merchandise
    WHERE
    category IN(Bird,Cat)
    <cfif len( trim( form.myTextField ) )>
    AND ItemID = #form.myTextField#
    AND Description LIKE '#%form.myTextField%#'
    </cfif>
  6. #4
  7. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22
    great... thanks!!!
    Discontent is the first necessity of progress. - Edison

IMN logo majestic logo threadwatch logo seochat tools logo