Apparently something strange happens to INT fields when you try to pass them
to a custom tag
as NULL values. The stored proc snippet below was originally just a query in
a simple custom tag, and I kept
getting the error "cannot convert datatype to int... use convert function"
when this particular INT value was NULL. I had to use "convert(int,
'#attributes.lid#')" and that worked fine. Then I converted the query into a
stored proc.
When the stored proc started failing ([Microsoft][ODBC SQL Server
Driver]Invalid character value for cast
specification), I suspected this might be the problem again, so I added the
IF statement
below, which seems to have fixed the problem. The table does accept NULL
values for that field, and if I run the stored proc in iSQL the NULL values
go through just fine. If I use the stored proc and that INT field has a
value, it also runs fine, it only fails when that INT is NULL. So, now I
know what the problem is and how to work around it, but I need a better
solution than passing a 0 (zero). Has anyone else had this very confusing
problem?
Original param that failed when passed a NULL:
<cfprocparam type="In" dbvarname="lid" value=#attributes.lid#
cfsqltype="CF_SQL_INTEGER">
This is how I fixed it:
<cfif #attributes.lid# IS NOT ''>
<cfprocparam type="In" dbvarname=lid value=#attributes.lid#
cfsqltype="CF_SQL_INTEGER">
<cfelse>
<cfprocparam type="In" dbvarname="lid" value=0 cfsqltype="CF_SQL_INTEGER">
</cfif>
Gina Shillitani
[EMAIL PROTECTED]
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
