Flexible Criteria Search redux

[seasonedweb]

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

[Carl Von Stetten]

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.

[seasonedweb]

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.

[Carl Von Stetten]

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.

[seasonedweb]

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.

[seasonedweb]

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.

[Carl Von Stetten]

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

-Carl V.

[seasonedweb]

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

[Carl Von Stetten]

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.

[seasonedweb]

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

[Carl Von Stetten]

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.