coldfusion cfselect bind - error converting 0.1 to int

lantervj
lantervj used Ask the Experts™
on
I have an error when binding a cfselect to a function that creates a query.  The error in the CF application log is;

Conversion failed when converting the varchar value '0,1' to data type int. The specific sequence of files included or processed is: D:\inetpub\wwwroot\global_components\basic.cfc, line: 249

If I remove the union all specs from the query I don't get the error.

Whe I run the query in SQL Management Studio I do not get an error.


The main page has this include;
<cfinclude template="/control/sitewide/cfms/dspNewCompanyTab.cfm">

The dspNewCompanyTab.cfm has the following code;
<cfloop query="QContactTypes">
<cfoutput>
<td>
<cfselect bind="cfc:globalcfc.basic.getcontactList(compid={compGrid.id},specialty=#QContactTypes.contact_type_acronym# )" name="#QContactTypes.contact_type_acronym#" display="contactname" selected="contactname" value="id" bindonload="false" size="10">
</cfselect>
</td>
</cfoutput>
</cfloop>


The cffuntion is;
<cffunction name="getContactList" returntype="query" access="remote">
<cfargument name="compid" required="yes" />
<cfargument name="specialty" required="yes" />
<cfset queryEnd="" />
<cfquery name="team" datasource="#request.dsn#">
SELECT UI.id, UI.LastName , UI.LastName + ', ' + UI.FirstName + space(10) + '^' + cast(ui.id as varchar) as contactname , UI.Year_Started_in_Tax , SP.Title , UI.Current_Consultant , UI.Original_Recruiter , '(' + UI.Work_Area_Code + ') ' + UI.Work_Phone AS workNumber , '(' + UI.Home_Area_Code + ') ' + UI.Home_Phone AS homeNumber , '(' + UI.Cell_Area_Code + ') ' + UI.Cell_Phone AS cellNumber , UI.Country_Phone_Prefix , UI.City , UI.State , UI.Previous_Company_Number , UI.Status_Flag , US.EMAIL_HOME , US.EMAIL_WORK , UI.ID AS theuserid , C1.Name + ' ' + C1.city as CompanyNameCity , UI.Current_Company_Number ,C1.Parent_Company_Number ,ui.firstname + ' ' + ui.lastname as Iname,UI.tax_specialty_ID 
FROM Companies as C1 Inner Join 
Users_Info AS UI on UI.Current_Company_Number = C1.ID INNER JOIN Users AS US ON UI.ID = US.ID INNER JOIN Specialty AS SP ON UI.Specialty = SP.SpecialtyID
WHERE (0 = 0) AND UI.Status_Flag != 'D' and (UI.Current_Company_Number = #val(arguments.compid)# )
union all
select distinct  0 , ' none', ' none selected', '', '', 0, 
   0, '', '', '', '', '', '', 
   0, 'A', '', '', 0, 
   '', #val(arguments.compid)#, 0, 
   '', 0
from companies as c2
WHERE c2.id = #val(arguments.compid)# 
ORDER BY CompanyNameCity, contactname 
</cfquery>
<cfreturn team />
</cffunction>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

The CFC for a SELECT bind is supposed to return a 2-dimension array of values, 1st for the value and 2nd for the label.

You are returning the query many lots of columns

Here's an example using "States"

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_27239291.html?cid=1576

<cffunction name="getstates" access="remote">
        <cfset state = arraynew(2)>
        <cfset xmlData = getXmlData()>
        <cfset numStates = 0>
        <cfset state[1][1] = "0">
        <cfset state[1][2] = "--state--">
        <cfset numStates = ArrayLen(xmlData.states.XmlChildren)>
        <cfloop from="1" to="#numStates#" index="j">
            <cfset state[j+1][1] = 
                ltrim(xmlData.states.state[j].XmlAttributes.abr)>
            <cfset state[j+1][2] = ltrim(xmlData.states.state[j].name.xmlText)>
        </cfloop>
        <cfreturn state>
    </cffunction>

Open in new window

try to ignore the XML parts of that, you're usinga  query, just loop through the query and populate the 2-D array and return the array instead of the query

Author

Commented:
I should have caught that.  Looking too closely.
Most Valuable Expert 2015

Commented:
>> The CFC for a SELECT bind is supposed to return a 2-dimension array of values

I'm curious, what version are you guys using? You shouldn't have to return an array if you're running CF 8.0.1+.  Binding to a query should work fine.
I'm gonna have to check that out, thanks agx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial