SQL Array being returned as number, not String in ColdFusion

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">
    &开发者_如何学Clt;/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>

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

append a space to the end of the number, then CF will treat it as a string and no leading 0 will be chopped.

simple workaround: <cfset result[i][1]=data.StateUSAbb[i] & " ">

btw, you know that query object is supported for populating cfselect right? So you don't even need the loop. You can do the same workaround but in SQL inside your cfquery

UPDATE: anyway, the idea is that if u want to preserve the leading 0 and keep using CF's built in serializeJSON() or calling the cfc remote method in JSON style (which will internally invoke serializeJSON(), you can append a space so CF will treat it as a string and leading 0 will be preserved. If your script somehow must need "012345" with no trailing space, then look for another JSON seralizer from riaforge or cflib.

You're sure the data returned from your query is a text string which contains the leading zero, rather than just the integer value? Regardless, I think Zachary's suggestion of NumberFormat(x, "00000") is the way to go.