Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2003
    Posts
    13

    Exclamation Unanswered: SQL - WHERE (via ColdFusion)

    SELECT * FROM TRUCKLIST

    WHERE fld10Make LIKE '%#frmMAKE#%' AND fld10StoredDescription LIKE '%#frmMODEL#%' AND fld10MdlYr LIKE '%#frmYEAR#%'

    This is just a snipped of code from my query. If the variable has no value it makes the query fail.

    Basicailly, I want to search the database based on multiple conditions. If the user doesn't enter a value I want it to search for everything in that column.

    How should my WHERE statement look?

    Thanks!
    - The Soup Nazi
    "Sunned by Soup?"

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    If you are building up a select statment in ColdFusion, then just don't add any test that doesn't have a value specified.

    If you are passing parameters to a stored procedure then something like this would work....
    Code:
    SELECT * 
      FROM TRUCKLIST
     WHERE (fld10Make LIKE '%' + @Make + '%' or @Make is Null)
       AND (fld10StoredDescription LIKE '%' + @Model + '%' or @Model is Null)
       AND (fld10MdlYr LIKE '%' + @Year + '%' or @Year is Null)
    Note that I assume your values to be character based.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is the answer --
    If you are building up a select statment in ColdFusion, then just don't add any test that doesn't have a value specified.
    here's a tip -- start the WHERE clause with 1=1

    then you selectively add additional conditions without having to worry about which one is first and whether it has to be preceded by AND or not
    Code:
    select ...
      from ...
     where 1=1 
    <cfif len(form.frmMAKE)>
      and fld10Make LIKE '%#frmMAKE#%'
    </cfif>
    <cfif len(form.frmMODEL)>
      and fld10StoredDescription LIKE '%#frmMODEL#%'
    </cfif>
    <cfif len(form.frmYEAR)>
      fld10MdlYr LIKE '%#frmYEAR#%'
    </cfif>
    rudy
    http://r937.com/

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    p.s. if none of the form fields are filled in, none of the CFIF tests will evaluate true, so that the entire WHERE clause remains as 1=1, which will result in all rows being returned, which is just what you want if no form fields have been filled in


  5. #5
    Join Date
    Sep 2003
    Posts
    13
    Thank you. I'll give that a shot.
    - The Soup Nazi
    "Sunned by Soup?"

  6. #6
    Join Date
    Sep 2003
    Posts
    13
    [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'AND'.

    The error occurred in D:\WebSites\inventory.atlantafreightliner.com\sear ch.cfm: line 7

    5 : <cfquery name="Search_Results" dataSource="#dbDSN#" username="#dbUSERNAME#" password="#dbPASSWORD#">
    6 : SELECT * from tbl15InventorySQL
    7 : WHERE fld10NeworUsed = #frmSTATUS# AND fld10MdlYr LIKE '%#frmYEAR#%' AND fld10Make = #frmMAKE# AND fld10StoredDescription = #frmMODEL#
    8 : </cfquery>
    9 :


    This is the message I get when running the web page. I tried using r937's method with no luck. When using his suggestion it failed on </CFIF> but does not specify which one.

    Any suggestions?
    - The Soup Nazi
    "Sunned by Soup?"

  7. #7
    Join Date
    Sep 2003
    Posts
    39
    If frmMake or frmModel or frmStatus is empty, you'll get an error.

    You should either build a query using conditional statements or make sure there is always something in there. You could have guessed that by writing the offending statement to the screen before executing it. You would have seen something like

    where mkfldblabla = and mkfblelbal like '%something%'

    It would have been obvious then what your mistake was ...

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    matching closing </CFIF>s to their opening tags, that's your job, i dunno how to help you with that, other than to say "slow down, take it easy, indent your code, and code carefully"



    as far as the syntax error is concerned, make sure all your character strings are enclosed in single quotes

    you have --

    AND fld10Make = #frmMAKE#
    AND fld10StoredDescription = #frmMODEL#

    those are probably character strings


    rudy

  9. #9
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I am NOT a coldfusion guy but it seems to me that this could be easly resolved by passing a fixed parameter list to a stored procedure and letting the SP sort it out.

    Does ColdFusion handle DB queries better than native TSQL?
    Paul Young
    (Knowledge is power! Get some!)

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    coldfusion would simply create TSQL and then pass it to SQL Server

    not sure what you mean by "handle"

    however, as far as the original requirement is concerned ("If the user doesn't enter a value I want it to search for everything in that column") then it's easy to let coldfusion tailor the query as i showed in my example

    on the other hand, if you go to the trouble of putting the conditional logic into a stored proc, then yeah, you could have coldfusion pass markers or switches or empty strings for form fields that were not filled out by the user

    (remember, in the general case, coldfusion will still likely be doing some conditional logic -- checkboxes, for example, that aren't checked aren't submitted from the browser to coldfusion)

    i guess it all depends on whether you want to write that general-purpose stored proc


    rudy

  11. #11
    Join Date
    Sep 2003
    Posts
    13
    Thanks for all of your help to this point. However, nothing is working.

    Using r937's code (copy and paste method) I get the following error. There is no CFIF statements prior to this one.

    [Macromedia][SQLServer JDBC Driver][SQLServer]Line 6: Incorrect syntax near 'fld10MdlYr'.

    The error occurred in D:\WebSites\search.cfm: line 16

    14 : <cfif len(form.frmYEAR)>
    15 : fld10MdlYr LIKE '%#frmYEAR#%'
    16 : </cfif>
    17 : </cfquery>
    18 :



    --------------------------------------------------------------------------------

    SQL SELECT * from tbl15InventorySQL WHERE 1=1 fld10MdlYr LIKE '%2003%'
    DATASOURCE db58885
    VENDORERRORCODE 170
    SQLSTATE HY000
    - The Soup Nazi
    "Sunned by Soup?"

  12. #12
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    My suggestion would be to pass all form parameters to a stored procedure and then use something like I posted above. What you are trying to do is dead simple in TSQL. Also stored procedures give you the ability of divorceing your app from the db schema!
    Paul Young
    (Knowledge is power! Get some!)

  13. #13
    Join Date
    Sep 2003
    Posts
    13
    That thought has crossed my mind, how do I call a stored proc in ColdFusion?
    - The Soup Nazi
    "Sunned by Soup?"

  14. #14
    Join Date
    Sep 2003
    Posts
    13
    That thought has crossed my mind, how do I call a stored proc in ColdFusion?
    - The Soup Nazi
    "Sunned by Soup?"

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oops, i forgot the AND in front of that third example

    no soup for me -- one year!!


    how to call a stored proc from coldfusion? CFSTOREDPROC


    rudy

Posting Permissions

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