CF_SQL_BIT works on Insert, not on Update

[DennisBelmont]

Aaarrgghhh! For some reason, insert and update Stored Procedures are behaving
differently, and there is not discernable reason for it. Here are the details:

I've got a CFC object that is used to handle DB functions, among other things.
My form's action page passes the #FORM# scope into a "set" method in this
page, then calls either an insert or update method, depending on the value of
the record ID.

Both the Insert and Update methods use stored procedures to process the
record. The code is nearly identical for both (in fact, the update was copied
and pasted from the insert, then the record ID was added as an "in" parameter).
There are no typos in either SP.

Both of the Stored Procedures are nearly identical, and are written correctly,
with no typos.

"Insert" records are processed with no error messages. The data captured in
the database is correct.

"Update" records are throwing an error "Error converting data type varchar to
bit. " Since both are using the same "set" method, this doesn't make sense.

I've even tried adding an IIF to set the "true" "false" values to "1" and "0",
but still got the same error.

Any ideas????

<cfset THIS.intPaymentTypeID = 0>
<cfset THIS.strPaymentTypeName = "">
<cfset THIS.blnIsCC = "false">

<cffunction name="setPaymentTypeAttributes" access="public"
returntype="boolean" output="false">
<cfargument name="intPaymentTypeID" type="numeric" required="no" default="0">
<cfargument name="strPaymentTypeName" type="string" required="no" default="">
<cfargument name="blnIsCC" type="boolean" required="no" default="0">
<cfif #ARGUMENTS.intPaymentTypeID# IS NOT 0><cfset THIS.intPaymentTypeID =
#ARGUMENTS.intPaymentTypeID#></cfif>
<cfif #ARGUMENTS.strPaymentTypeName# IS NOT ""><cfset
THIS.strPaymentTypeName = "#ARGUMENTS.strPaymentTypeName#"></cfif>
<cfif #ARGUMENTS.blnIsCC# IS NOT 0><cfset THIS.blnIsCC =
#ARGUMENTS.blnIsCC#></cfif>
<cfreturn true>
</cffunction>

<cffunction name="actInsertPaymentType" access="public" returntype="boolean"
output="false">
<cftransaction>
<cfstoredproc procedure="ACTspInsertPaymentType"
datasource="#REQUEST.mysql_dsn#" username="#REQUEST.mysql_username#"
password="#REQUEST.mysql_password#">
<cfprocparam type="in" value="#THIS.strPaymentTypeName#"
cfsqltype="cf_sql_varchar">
<cfprocparam type="in" value="#THIS.blnIsCC#" cfsqltype="cf_sql_bit">
<cfprocparam type="out" variable="VARIABLE.intPaymentTypeID"
cfsqltype="cf_sql_integer">
</cfstoredproc>
<cfquery name="qryGetNew" datasource="#REQUEST.mysql_dsn#"
username="#REQUEST.mysql_username#" password="#REQUEST.mysql_password#">
SELECT MAX(PaymentTypeID) AS intNew
FROM ACTtbPaymentType
</cfquery>
<cfset THIS.intPaymentTypeID = #qryGetNew.intNew#>
</cftransaction>
<cfreturn true>
</cffunction>

<cffunction name="actUpdatePaymentType" access="public" returntype="boolean"
output="false">
<cfset VARIABLES.blnIsCC = #IIF(#THIS.blnIsCC# IS "true", DE(1), DE(0))#>
<cfstoredproc procedure="ACTspInsertPaymentType"
datasource="#REQUEST.mysql_dsn#" username="#REQUEST.mysql_username#"
password="#REQUEST.mysql_password#">
<cfprocparam type="in" value="#THIS.intPaymentTypeID#"
cfsqltype="cf_sql_integer">
<cfprocparam type="in" value="#THIS.strPaymentTypeName#"
cfsqltype="cf_sql_varchar">
<cfprocparam type="in" value="#VARIABLES.blnIsCC#" cfsqltype="cf_sql_bit">
</cfstoredproc>
<cfreturn true>
</cffunction>

[paross1]

Could it possibly be a value problem of the parameter (VARIABLES.blnIsCC),
rather than type? Also, have you tried to explicitly CAST the value of the
input parameter to type BIT in the UPDATE .. SET statement within your stored
proc?

Phil

[DennisBelmont]

I've tried a number of different ways to ensure that the value is a 1 or a 0;
nothing's working. CAST doesn't work either, because it's never making it into
the stored procedure - the error is being thrown while it's trying to pass the
parameters in.

[paross1]

Maybe it would help to see the stored procedure, unless it is too much to display here. Also, you might try using cfsqltype="cf_sql_integer" for "#VARIABLES.blnIsCC#" in your update.

Phil