Link to home
Start Free TrialLog in
Avatar of lantervj
lantervjFlag for United States of America

asked on

coldfusion cfselect bind - error converting 0.1 to int

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

ASKER CERTIFIED SOLUTION
Avatar of gdemaria
gdemaria
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of lantervj

ASKER

I should have caught that.  Looking too closely.
>> 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