ColdFusion & Ajax: How to Get Blank Row in Binded Select Box?

Question

I have two cfselect boxes that use binds and a cfc. One is State. Choose a State, and the second cfselect (counties) is populated on the fly.

Prior to doing this with the bind attribute, I relied on the queryPostion="below" attribute such as the following to basically put a blank row into the option box. I want to do the same thing for both the State and County select boxes now, as I'd like to have "" values (or an "ALL" value as an option in each. But queryPostion no longer works. I'm not sure of a work-around.

//original... leaves a blank option:
     <cfselect enabled="No" name="search_state" multiple="no" query="get_States" value="StateUSAbb" display="StateName" queryPosition="below">
                      <option></option>
    </cfselect>

  //now, w/bind, doesn't work:


    <cfselect bind="cfc:states.getStates()" bindonload="true" name="search_state" 
                  value="StateUSAbb" display="StateName">    
    </cfselect>

    <cfselect bind="cfc:states.getCounties({search_state})" bindonload="true" name="search_county" value="FIPS_County" display="CountyName" >
    </cfselect>

UPDATE Solution for both queries:

     SELECT DISTINCT tblLoc.StateUSAbb, lkuState.StateName
        FROM lkuState INNER JOIN tblLoc ON lkuState.FIPS_State = tblLoc.FIPS_State
        WHERE (lkuState.StateName <> 'New Brunswick')
        UNION
        SELECT '' AS StateUSAbb, '' AS StateName
        FROM lkuState
        ORDER BY StateName

SELECT '' AS FIPS_COUNTY, '' as CountyName
        FROM lkuCnty
        UNION
        SELECT FIPS_County, CountyName
        FROM lkuCnty
        WHERE StateAbb = '#ARGUMENTS.stateabb#'
        ORDER BY CountyName

Answer 1

The easiest way I've figured out is to insert a blank (or placeholder) data row in the query in your cfc. Something like:

select "0" as id, "Choose...." as value
union
select id, value from tableName

Answer 2

This would be handled in the states.cfc getCounties() function. That needs to return the blank record. Post it here if you want more help.

Answer 3

UPDATE: Never mind. Looks like the same was already suggested while I was replying ;-)

AFAIK, there is no built in option for creating empty elements in bound select list. At least not with CF8.

Since binding will replace the list contents, you would need to add the empty options to your query results. One technique is to add the empty option to your query results via a UNION.

Depending on the list values, either

SELECT 0 AS SortOrder, '' AS StateUSAbb, '--- ALL ---' AS StateName
UNION ALL
SELECT 1, StateUSAbb, StateName 
FROM   YourTable
WHERE  (some condition ...)
ORDER BY SortOrder ASC

... OR possibly

SELECT '' AS StateUSAbb, '--- ALL ---' AS StateName
UNION ALL
SELECT StateUSAbb, StateName 
FROM   YourTable
WHERE  (some condition ...)
ORDER BY StateUSAbb

---

UPDATE As a point of interest, the difference between using UNION and UNION ALL is that UNION removes duplicates. UNION ALL does not. Since a UNION query is slightly more expensive, I only use it when needed.

---

Answer 4

FYI bind expression can bind to functions that returns an array of entities in CF9.

In that case, ArrayPrepend() will work the same as QoQ w/ UNION ALL.