SQL Array being returned as number, not String in ColdFusion

Question

In Coldfusion, I'm using a cfc that binds one select box to another (basically, choose a State from one box, the second box is populated with County names.) The value for the County box is a 5-digit number WHICH IS FORMATTED AS TEXT (ie. the value comes from a text field.)

The problem is that I'm finding that if the value of the selected county id starts with a '0', it's been cut off.

So I get stuff like: ID County 11223 A 2300 B (should be 02300)

Can someone help make sure that leading 0s are not cut off?

Here's the select boxes on the page:

 <!--- State Name options --->
    <b>State:</b><br />
    <cfselect bind="cfc:states.getStates()" bindonload="true" name="search_state" id="search_state" value="StateUSAbb" display="StateName">
    </cfselect><br />

  <!--- County Name options --->
    <b>County:</b><br />
    <cfselect bind="cfc:states.getCounties({search_state})" name="search_county" id="search_county" value="FIPS_County" display="CountyName">
    </cfselect>

I hate pasting the whole .cfc but pay attention to the latter part, particularly the cfloop which uses a cfset to populate array RESULT:

<cfcomponent output="false">

    <!--- Get array of media types --->
    <cffunction name="getStates" access="remote" returnType="array">
        <!--- Define variables --->
        <cfset var data="">
        <cfset var result=ArrayNew(2)>
        <cfset var i=0>

        <!--- Get data --->
        <cfquery name="data" datasource="bridges">
       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, ' ALL' AS StateName
        FROM lkuState
        ORDER BY StateName
        </cfquery>

        <!--- Convert results to array --->
        <cfloop index="i" from="1" to="#data.RecordCount#">
            <cfset result[i][1]=data.StateUSAbb[i]>
            <cfset result[i][2]=data.StateName[i]>
        </cfloop>

        <!--- And return it --->
        <cfreturn result>
    </cffunction>

    <!--- Get counties by state --->
    <cffunction name="getCounties" access="remote" returnType="array">
        <cfargument name="stateabb" type="string" required="true">

        <!--- Define variables --->
        <cfset var data="">
        <cfset var result=ArrayNew(2)>
        <cfset var i=0>

        <!--- Get data --->
        <cfquery name="data" datasource="bridges">
        SELECT '' AS FIPS_COUNTY, ' ALL' as CountyName
        FROM lkuCnty
        UNION
        SELECT FIPS_County, CountyName
        FROM lkuCnty
        WHERE StateAbb = '#ARGUMENTS.stateabb#'
        ORDER BY CountyName
        </cfquery>

        <!--- Convert results to array --->
        <cfloop index="i" from="1" to="#data.RecordCount#">
            <cfset result[i][1]=data.FIPS_County[i]>
            <cfset result[i][2]=data.CountyName[i]>
        </cfloop>

        <!--- And return it --->
        <cfreturn result>
    </cffunction>

</cfcomponent>

Zachary Zachary · Accepted Answer · 2010-11-16T23:26:06

If the data is a fixed length you can use NumberFormat to force leading zero's. In general, CF is typeless so there must be some underlying conversion happening that is causing the data to get corrupt. You might try forcing the value toString(), or to debug add something like a single quote as the first character in the column value (eg. SELECT '''' + FIPS_County, '''' + CountyName FROM lkuCnty) to see if they keep all their characters.

[Update] Based on your comments about how SQL is not returning 5 char, use this updated query to go from INT to VARCHAR with leading zeros.

  SELECT DISTINCT 
    RIGHT('00000' + CONVERT(VARCHAR(5),StateUSAbb),5), 
    lkuState.StateName
         FROM lkuState INNER JOIN tblLoc ON lkuState.FIPS_State = tblLoc.FIPS_State
         WHERE (lkuState.StateName <> 'New Brunswick')
  UNION
     SELECT '' AS StateUSAbb,
     ' ALL' AS StateName
          FROM lkuState
          ORDER BY StateName

SQL Array being returned as number, not String in ColdFusion

3 Answers