Skip to main content
B
brian_williamson1
Participant
December 15, 2010
Question

<cfif to select which query to use

  • December 15, 2010
  • 2 replies
  • 1057 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.

2 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
December 19, 2010

Indeed. I did also ask myself whether it was just one query, plus, say, an adjusted where-clause, or two entirely different  queries. Which one is it, Brian?

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
December 18, 2010

This sounds eerily familiar. I must have responded to it some days ago.

You could do it on 2 separate pages.

formpage.cfm

<form action="actionPage.cfm">
<select name="cboApproved">
  <option value="">Select option</option>
  <option value="1">vApproved</option>
  <option value="0">Not vApproved</option>
  <option value="-1">Needs Approval</option>
</select>
<input type="submit">
</form>

actionPage.cfm

<cfif isDefined("form.cboApproved") and form.cboApproved eq -1>
  <cfquery name="qRecordsNull">
<cfelse>
  <cfquery name="qRecords">
</cfif>

-
-__cfSearching__-
Inspiring
December 18, 2010

This sounds eerily familiar.

Yes, it does.

The SQL column has this as a bit value so a Case adjustment does not work.

Could you elaborate? Because it seems a bit convoluted. Just wondering if there is a simpler way to go about it.

Owainnorth
Owainnorth
Inspiring
December 19, 2010

Deja vu?

Well seeing as the previous thread seems to have been wiped off the face of the earth and started again, I'll add a point I added last time.

If you're doing your queries in-line on the page as it would appear, why the need to name them differently? Why not have the switch/cfif *inside* the cfquery tag, so it's just the results of the query that vary? The idea of having them named differently surely just means you're going to have to duplicate the case logic at the display end? I had a need for something similar the other day, and did something like this:

<cfif client.isManager >

  <cfset qWhatever = application.Managers.get('Staff').getAll() />

<cfelse>

  <cfset qWhatever = application.Managers.get('Staff').getById(client.id) />

</cfif>

That way whatever has happened, you end up with a queryset of the same identified. Unless I missed something in the previous elusive thread of course.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices