10 Replies Latest reply on Dec 10, 2013 2:56 PM by Carl Von Stetten

    Flexible Criteria Search redux

    seasonedweb Level 1

      This action page to query mutiple drop-down selection only works with 2 choices- when a third is introduced, it ignores it:

       

      <cfquery name="search_flexible_criteria" datasource="#Request.BaseDSN#">

      SELECT

      mo.order_ID,

      mo.order_number,

      mo.order_property_type_ID,

      mo.order_order_type_ID,

      mo.order_report_type_ID,

      mo.order_client_ID,

      mo.order_appraiser_ID,

      mo.order_status_ID,

      mo.order_rush_status_ID,

      mo.order_property_street,

      mo.order_property_city,

      mo.order_create_date,

       

      ls.order_status_ID,

      ls.order_status,

       

       

      lc.client_ID,

      lc.client_company,

       

      la.appraiser_ID,

      la.appraiser_fname,

      la.appraiser_lname,

       

      lo.order_type_ID,

      lo.order_type,

       

      lp.property_type_ID,

      lp.property_type,

       

      lr.report_type_ID,

      lr.report_type,

       

      lrs.rush_status_ID,

      lrs.rush_status

       

       

      FROM  main_orders mo

       

       

             

              LEFT JOIN lookup_order_status ls

              ON mo.order_status_ID = ls.order_status_ID

             

              LEFT JOIN lookup_clients lc

              ON mo.order_client_ID = lc.client_ID

             

             

              LEFT JOIN lookup_appraisers la

              ON mo.order_appraiser_ID = la.appraiser_ID

             

              LEFT JOIN lookup_order_type lo

              ON mo.order_order_type_ID = lo.order_type_ID

             

              LEFT JOIN lookup_property_type lp

              ON mo.order_property_type_ID = lp.property_type_ID

             

              LEFT JOIN lookup_report_type lr

              ON mo.order_report_type_ID = lr.report_type_ID

             

             

              LEFT JOIN lookup_rush_status lrs

              ON mo.order_rush_status_ID = lrs.rush_status_ID

             

              WHERE 1 = 1

       

       

       

       

      <cfif StructKeyExists(Form, "StartDate") AND StructKeyExists(Form, "EndDate")  AND form.StartDate IS NOT  "" AND form.EndDate IS NOT  "">

       

      AND (order_create_date BETWEEN #CreateODBCDate(FORM.StartDate)# AND #CreateODBCDate(FORM.EndDate)#)</cfif>

       

      <cfif StructKeyExists(Form, "order_client_ID") AND Form.order_client_ID NEQ 0>

       

       

      AND client_ID  = #Form.order_client_ID# </cfif>

       

       

      <cfif StructKeyExists(Form, "order_appraiser_ID") AND Form.order_appraiser_ID NEQ 0>

       

       

      AND  appraiser_ID  = #Form.order_appraiser_ID#</cfif>

       

       

      <cfif StructKeyExists(Form, "order_status_ID") AND Form.order_status_ID NEQ 0>

       

      AND mo.order_status_ID = #Form.order_status_ID#</cfif>

       

      <cfif StructKeyExists(Form, "order_rush_status_ID") AND Form.order_rush_status_ID NEQ 0>

       

      AND order_rush_status_ID = #Form.order_rush_status_ID#</cfif>

       

      ORDER BY order_create_date DESC

       

      </cfquery>  

       

      Thanks to Carl for getting it this far- hair-pulling, hopefully, will stop soon.

       

      Thank you for taking the time to read this.

       

      Norman

        • 1. Re: Flexible Criteria Search redux
          Carl Von Stetten Adobe Community Professional & MVP

          Norman,


          Did you do what I suggested in the other thread and test the query in SSMS? Would help to isolate whether there is a problem with the SQL being generated or with the CF logic.

           

          -Carl V.

          • 2. Re: Flexible Criteria Search redux
            seasonedweb Level 1

            Carl:

             

            Thanks for picking this up again. Running the query directly in SQLServer:

             

            Msg 208, Level 16, State 1, Line 1

            Invalid object name 'main_orders'.

             

            which makes no sense, since I  use that table in dozens of queries that do work.

            • 3. Re: Flexible Criteria Search redux
              Carl Von Stetten Adobe Community Professional & MVP

              Have you set SSMS to use the correct database before running the query?  SSMS defaults to the "master" database for new queries.


              If that isn't the issue, can you post the actual query you are trying to run in SSMS?

               

              -Carl V.

              • 4. Re: Flexible Criteria Search redux
                seasonedweb Level 1

                Carl:

                 

                At this point, I would gladly give you the credentials necessary to get in there and take a peek- of course, I would need a private email. (one of) mine is info@sakonnetweb.com

                 

                Thanks again for your help with this

                 

                Norman B.

                • 5. Re: Flexible Criteria Search redux
                  seasonedweb Level 1

                  Carl:

                   

                  I did:

                   

                   

                  SELECT

                  mo.order_ID,

                  mo.order_number,

                  mo.order_property_type_ID,

                  mo.order_order_type_ID,

                  mo.order_report_type_ID,

                  mo.order_client_ID,

                  mo.order_appraiser_ID,

                  mo.order_status_ID,

                  mo.order_rush_status_ID,

                  mo.order_property_street,

                  mo.order_property_city,

                  mo.order_create_date,

                   

                  ls.order_status_ID,

                  ls.order_status,

                   

                   

                  lc.client_ID,

                  lc.client_company,

                   

                  la.appraiser_ID,

                  la.appraiser_fname,

                  la.appraiser_lname,

                   

                  lo.order_type_ID,

                  lo.order_type,

                   

                  lp.property_type_ID,

                  lp.property_type,

                   

                  lr.report_type_ID,

                  lr.report_type,

                   

                  lrs.rush_status_ID,

                  lrs.rush_status

                   

                   

                  FROM  main_orders mo

                   

                   

                         

                          LEFT JOIN lookup_order_status ls

                          ON mo.order_status_ID = ls.order_status_ID

                         

                          LEFT JOIN lookup_clients lc

                          ON mo.order_client_ID = lc.client_ID

                         

                         

                          LEFT JOIN lookup_appraisers la

                          ON mo.order_appraiser_ID = la.appraiser_ID

                         

                          LEFT JOIN lookup_order_type lo

                          ON mo.order_order_type_ID = lo.order_type_ID

                         

                          LEFT JOIN lookup_property_type lp

                          ON mo.order_property_type_ID = lp.property_type_ID

                         

                          LEFT JOIN lookup_report_type lr

                          ON mo.order_report_type_ID = lr.report_type_ID

                         

                         

                          LEFT JOIN lookup_rush_status lrs

                          ON mo.order_rush_status_ID = lrs.rush_status_ID

                         

                          WHERE 1 = 1

                   

                  The query was sucessful. 3200 records were returned.

                  • 6. Re: Flexible Criteria Search redux
                    Carl Von Stetten Adobe Community Professional & MVP

                    What does the query look like when you try a combination of three inputs (which is where I believe it was failing)?

                    -Carl V.

                    • 7. Re: Flexible Criteria Search redux
                      seasonedweb Level 1

                      Carl:

                       

                      These are the form inputs:

                       

                      <input name="StartDate" size="17">    <input type="button" value="select" onClick="displayDatePicker('StartDate');">

                       

                      <input name="EndDate" size="17">    <input type="button" value="select" onClick="displayDatePicker('EndDate');">

                       

                      <select name="order_appraiser_ID" class="forminputfield">

                      <option value="0">-- Select an Appraiser --</option>

                      <cfoutput query="get_all_appraiser"><option value="#appraiser_ID#">#appraiser_lname#, #appraiser_fname#</option></cfoutput></select>

                       

                      <select name="order_report_type_ID" class="forminputfield">

                      <option value="0">-- Select a Report Type --</option>

                      <cfoutput query="get_all_report_type"><option value="#report_type_ID#">#report_type#</option></cfoutput></select>

                       

                      <select name="order_order_type_ID" class="forminputfield">

                      <option value="0">-- Select an Order Type --</option>

                      <cfoutput query="get_all_order_type"><option value="#order_type_ID#">#order_type#</option></cfoutput></select>

                       

                      <select name="order_status_ID" class="forminputfield">

                      <option value="0">-- Select a Status --</option>

                      <cfoutput query="get_all_status"><option value="#order_status_ID#">#order_status#</option></cfoutput></select>

                       

                      <select name="order_client_ID" class="forminputfield">

                      <option value="0">-- Select a Client --</option>

                      <cfoutput query="get_all_client"><option value="#client_ID#">#client_company#</option></cfoutput></select>

                       

                      <select name="order_property_type_ID" class="forminputfield">

                      <option value="0">-- Select a Property Type --</option>

                      <cfoutput query="get_all_property_type"><option value="#property_type_ID#">#property_type#</option></cfoutput></select>

                       

                      <select name="order_rush_status_ID" class="forminputfield">

                      <option value="0">-- Select a Priority --</option>

                      <cfoutput query="get_all_rush_status"><option value="#rush_status_ID#">#rush_status#</option></cfoutput></select>

                       

                      As I said, it sucessfully finds up to 2 criteria but when a third is introduced, it returns 0 records.

                       

                      Norman

                      • 8. Re: Flexible Criteria Search redux
                        Carl Von Stetten Adobe Community Professional & MVP

                        Norman,

                         

                        I really wanted to see what the query looked like.  I'm wondering if you've verified that running the raw query with the same input values in SSMS actually returns records.  I want to rule out a ColdFusion issue.  Is it possible that the input values have no matching records in the database?  Try running various combinations of inputs in SSMS first (finding some that actually return records), then use the exact same input values in your ColdFusion form page and see if you get the same results.

                         

                        -Carl V.

                        • 9. Re: Flexible Criteria Search redux
                          seasonedweb Level 1

                          Carl:

                           

                          All is good in CF land- I had omitted 3 of the IF variables on the action page.

                           

                          I really appreciate all the time you took helping with this- I will be debugging in SSMS from now on.

                           

                          Have a great holliday :>)

                           

                          Norman

                          • 10. Re: Flexible Criteria Search redux
                            Carl Von Stetten Adobe Community Professional & MVP

                            Norman,

                             

                            Glad you got it sorted.  Sorry for the intermittent replies over the past few days; we were moving back into our normal office space over the weekend and setting everything back up.

                             

                            -Carl V.