Skip to main content
Participant
December 15, 2010
Answered

cfif to select which query to use.

  • December 15, 2010
  • 2 replies
  • 1296 views

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>

    This topic has been closed for replies.
    Correct answer BKBK

    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>

    2 replies

    BKBK
    Community Expert
    BKBKCommunity ExpertCorrect answer
    Community Expert
    December 15, 2010

    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>

    Inspiring
    December 16, 2010

    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?

    Inspiring
    December 16, 2010

    <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>

    Inspiring
    December 15, 2010

    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.

    Participant
    December 15, 2010

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

    Inspiring
    December 15, 2010

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