Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
What does the query look like when you try a combination of three inputs (which is where I believe it was failing)?
-Carl V.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.