null="#Len(Trim(ARGUMENTS.SourceRef1))#" that will evaluate to null="true" when ARGUMENTS.SourceRef1 IS an empty string. as Leigh said, you should have used null="#NOT Len(Trim(ARGUMENTS.SourceRef1))#"
Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ On 13/11/2009 01:58, Brett Davis wrote: > Ok in the quest for looking for better ways to write my code I ran into a > small gotcha today that had me banging my head against the wall. Here is the > original code: > > <cftry> > <cfquery name="createField" datasource="#DSN#"> > INSERT INTO t_field( > structure_id, > source_ref_1, > field_fmt_type_id, > last_chg_user_id, > last_chg_dt > > ) > VALUES( > <cfqueryparam cfsqltype="cf_sql_integer" > value="#ARGUMENTS.StructureID#"/>, > <cfqueryparam cfsqltype="cf_sql_varchar" > value="#ARGUMENTS.SourceRef1#" null="#Len(Trim(ARGUMENTS.SourceRef1))#"/>, > <cfqueryparam cfsqltype="cf_sql_integer" > value="#ARGUMENTS.FieldFmtTypeID#" > null="#Len(Trim(ARGUMENTS.FieldFmtTypeID))#"/>, > <cfqueryparam cfsqltype="cf_sql_integer" > value="#ARGUMENTS.LastChangeUserID#"/>, > #CreateODBCDate(Now())# > ); > SELECT SCOPE_IDENTITY() AS newid; > </cfquery> > <cfcatch type="database"> > <cfreturn false/> > </cfcatch> > </cftry> > > I am sure most of you know what I am attempting to do, but for those that > don't I'll explain. Instead of writing a bunch of CFIFs to determine if the > value passed to the query is an empty string and omitting parts of the SQL > query and allowing MSSQL to insert NULLs, I've opted to use the NULL > attribute of the CFQueryparam tag to do that work for me. I've been using > null="#Len(Trim(Arguments.Value))#" with much success and happiness until > today. When ColdFusion attempted to run this code I got a nice fat error: > Invalid data '' for CFSQLTYPE CF_SQL_INTEGER > > I was like WTF? Ummm hello McFly if I passed you an empty string that's what > the whole null="#Len(Trim(value))#" was there for... hello. So I did some > digging around and it turns out I was in fact as Fred G Sanford would say > "You Big Dummy!". After reading a blog post by Ben Nadel > http://www.bennadel.com/blog/1092-ColdFusion-CFQueryParam-Binding-vs-SQL-Execution.htm > turns out that if you are using CF_SQL_INTEGER type it's looking for an > integer value period. Pass it an empty string if you wish and it will fail. > So a quick re-write to the code below gave me the results I was expecting in > the first place. > > <cftry> > <cfquery name="createField" datasource="#DSN#"> > INSERT INTO t_field( > structure_id, > source_ref_1, > field_fmt_type_id, > last_chg_user_id, > last_chg_dt > > ) > VALUES( > <cfqueryparam cfsqltype="cf_sql_integer" > value="#ARGUMENTS.StructureID#"/>, > <cfqueryparam cfsqltype="cf_sql_varchar" > value="#ARGUMENTS.SourceRef1#" null="#Len(Trim(ARGUMENTS.SourceRef1))#"/>, > <cfqueryparam cfsqltype="cf_sql_integer" > value="#ARGUMENTS.FieldFmtTypeID#" null="#NOT > isNumeric(ARGUMENTS.FieldFmtTypeID)#"/>, > <cfqueryparam cfsqltype="cf_sql_integer" > value="#ARGUMENTS.LastChangeUserID#"/>, > #CreateODBCDate(Now())# > ); > SELECT SCOPE_IDENTITY() AS newid; > </cfquery> > <cfcatch type="database"> > <cfreturn false/> > </cfcatch> > </cftry> > > Using the null="#NOT isNumeric(ARGUMENTS.value)# allows for the proper insert > of a NULL when an empty string is passed to the query. > > Cheers > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328334 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
