cfif to select which query to use.

[brian_williamson1]

I need some help getting syntax on an issue.  I have a listbox with 3 values, True(1), False(0) and Needs Approval(NULL) but I can't seem to get the NULL to show. The SQL column has this as a bit value so a Case adjustment does not work.

What I need this to do is if the Needs Approval value is selected in the cboApproved box then use the 'qRecordsNull" query to get the results, else use the "qRecords" query.

Below is the code that I have that works for true and false.  Any help would be greatly appreciated.

<cfset vApproved = 0>
<cfif isDefined("form.cboApproved")>
<cfif form.cboApproved eq "">
  <cfset vApproved = 0>
<cfelse>
  <cfset vApproved = #form.cboApproved#>
</cfif>
<cfelse>
<cfset vApproved = 0>
</cfif> [/code]

<td>
       <select name="cboApproved" class="smallFont">
        <!---<option value="All">All</option>--->
                 <option value="1"
         <cfif isDefined("vApproved")>
          <cfif vApproved eq 1>
            selected
          </cfif>
         </cfif>
        >True</option>
                 <option value="0"
         <cfif isDefined("vApproved")>
          <cfif vApproved eq 0>
            selected
          </cfif>
         </cfif>
        >False</option>
                 <option value= "">Need Approval</option>
       </select>
      </td>

Basically something that looks like this:

<cfif isDefined("form.cboApproved")>
<cfif #form.cboApproved# eq "Needs Approval">
  <cfquery name="qRecordsNull"
<cfelse>
  <cfquery name="qRecords"
</cfif>

[Dan_Bracuk]

When you have 3 choices, you can either use switch/case logic, or if/else if/else logic.  You don't appear to be using either.

[brian_williamson1]

What would the syntax of that look like in my application?

[Dan_Bracuk]

It would look like the examples in the cfml reference manual for either <cfswitch> or <cfelseif>, depending on what approach you took.

[BKBK · Accepted solution]

1. Replace the following code:

<cfset vApproved = 0>
<cfif isDefined("form.cboApproved")>
<cfif form.cboApproved eq "">
  <cfset vApproved = 0>
<cfelse>
  <cfset vApproved = #form.cboApproved#>
</cfif>
<cfelse>
<cfset vApproved = 0>
</cfif>

with this one:

<cfset vApproved = 0>
<cfif isDefined("form.cboApproved") and form.cboApproved neq "">
  <cfset vApproved = #form.cboApproved#>
</cfif>

2. Replace the following code:

<cfif isDefined("form.cboApproved")>
<cfif #form.cboApproved# eq "Needs Approval">
  <cfquery name="qRecordsNull"
<cfelse>
  <cfquery name="qRecords"
</cfif>
</cfif>

with this one:

<cfif isDefined("form.cboApproved") and form.cboApproved eq "Needs Approval">
    <cfquery name="qRecordsNull" datasource="myDSN">
    <!--- query --->
    </cfquery>
<cfelse>
      <cfquery name="qRecordsNull" datasource="myDSN">
    <!--- query --->
        </cfquery>
</cfif>

[Dan_Bracuk]

Regarding this:

with this one:

<cfif isDefined("form.cboApproved") and form.cboApproved eq "Needs Approval">
    <cfquery name="qRecordsNull" datasource="myDSN">
    <!--- query --->
    </cfquery>
<cfelse>
      <cfquery name="qRecordsNull" datasource="myDSN">
    <!--- query --->
        </cfquery>
</cfif>

Am I missing something or are you specifying the same query for both the if and the else?

[meensi]

<cfif isDefined("form.cboApproved") and form.cboApproved eq "Needs Approval">
    <cfquery name="qRecordsNull" datasource="myDSN">
    <!--- query --->
    </cfquery>
<cfelse>
      <cfquery name="qRecords" datasource="myDSN">
    <!--- query --->
        </cfquery>
</cfif>

[BKBK]

Dan Bracuk wrote:

Regarding this:

with this one:

<cfif isDefined("form.cboApproved") and form.cboApproved eq "Needs Approval">
    <cfquery name="qRecordsNull" datasource="myDSN">
    <!--- query --->
    </cfquery>
<cfelse>
      <cfquery name="qRecordsNull" datasource="myDSN">
    <!--- query --->
        </cfquery>
</cfif>

Am I missing something or are you specifying the same query for both the if and the else?

That was just a typo. My bad. The second name should be qRecords, as Meensi has kindly pointed out.