• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

Add blank record to bound cfselect

I'm trying to figure out a way to return the getSaleBuyers4cfselect query with a black record in case the user wants to leave it blank.
<cfselect name="sale_buyer_id" id="sale_buyer_id" size="1" bind="cfc:lots.addWindow.getSaleBuyers4cfselect()" bindonload="true" display="sale_buyer_Name" value="sale_buyer_id" />
<!--- Sale Buyers CFC to fill the cfselect query --->
<cffunction name="getSaleBuyers4cfselect" access="remote" returntype="query" output="false">
<cfset var qGetSaleBuyers="">
<cfquery name="qGetSaleBuyers" datasource="#request.dsn#">
SELECT sale_buyer_id, sale_buyer_name
FROM sale_buyer
ORDER BY sale_buyer_name
<cfreturn qGetSaleBuyers />

Open in new window

1 Solution
use a UNION query in your cfc:

<cfquery name="qGetSaleBuyers" datasource="#request.dsn#">
(SELECT 0 AS sale_buyer_id, '' AS sale_buyer_name, 0 AS sortcolumn
FROM sale_buyer)
(SELECT sale_buyer_id, sale_buyer_name, 1 AS sortcolumn
FROM sale_buyer)
ORDER BY sortcolumn, sale_buyer_name

the sortcolumn column has been added to allow proper sorting of the union'd resultset

MFredinAuthor Commented:
Thanks again Azadi! Perfect!

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now