CF will read in bit values from SQL Server and evaluate them as
equivalent to true/false or yes/no. However, SQL Server expects 0 or 1
from ColdFusion when writing to a bit field. There's probably a UDF on
CFLib.org that converts CF boolean values into strict bit values, but
here's one I wrote for this purpose:
<!--- BooltoBit can be used to convert logical boolean values
("on/off","true/false","yes/no", "1/0") to bit values for
insertion into SQL databases
Example:
foo = <cfqueryparam value="#BooltoBit(foo)#"
cfsqltype="cf_sql_bit">
--->
<cffunction name="BooltoBit" returntype="numeric">
<cfargument name="b" type="string" required="yes">
<cfset var result = "">
<cfif LCase(b) eq "on">
<cfset b = True>
<cfelseif LCase(b) eq "off">
<cfset b = False>
</cfif>
<cfif IsBoolean(b)>
<cfif b>
<cfset result = 1>
<cfelse>
<cfset result = 0>
</cfif>
<cfelse>
<cfset result = -1>
</cfif>
<cfreturn result>
</cffunction>
HTH,
Carl
On 2/18/2011 10:38 AM, Nicki Tabb wrote:
> sorry,
> I guess I meant that, when using CF, I have never had issues with No or False
> or 0 being read as the same by the SQL Server
>
>
>
>> No it doesn't.
>>
>> Here is an example on SQL 2005:
>>
>> declare @test table (
>> testVal bit
>> )
>>
>> insert into @test (testval) values (0);
>> insert into @test (testval) values ('Yes'); -- Results in error
>> Conversion failed when converting the varchar value 'Yes' to data type
>> bit.
>> insert into @test (testval) values ('True'); -- Results in error
>> Conversion failed when converting the varchar value 'True' to data type
>> bit.
>> insert into @test (testval) values ('No'); -- Results in error
>> Conversion failed when converting the varchar value 'No' to data type
>> bit.
>> insert into @test (testval) values ('False'); -- Results in error
>> Conversion failed when converting the varchar value 'False' to data type
>> bit.
>>
>> select * from @test
>>
>> Sql expects data types that match. ColdFusion isn't strongly typed so
>> false and no are equivalent to 0.
>>
>> Steve
>>
>>
>>
>> SQL will read a NO or FALSE or 0 as the same
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342440
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
