Skip to main content
M
me1019
Participant
March 27, 2012
Answered

<cfqueryparam with null attribute not working for me>

  • March 27, 2012
  • 2 replies
  • 5020 views

Using CF801 and SQL Server 2005.  I have a field in my table defined as (int, null).  In my CF .cfc function -

<cffunction name="createReference" access="remote" returntype="string" hint="Add Reference unit.  On success return ReferenceID On failure return error message.">

<cfargument name="intsteam_cap_mwe" type="numeric" required="no" default="0">

    

<cfquery name="insertReferenceData" datasource="#application.DSN#">

      INSERT INTO Units

       (

            SteamCapacity_MWe)

      Values

     (

           <cfqueryparam value="#arguments.intsteam_cap_mwe#" cfsqltype="cf_sql_integer" null="#YesNoFormat(NOT len(trim(arguments.intsteam_cap_mwe)))#">

     )

</cfquery>

</cffunction>

However, it gives an error "The INTSTEAM_CAP_MWE argument passed to the createReference function is not of type numeric. "

In my old code, I used <cfif to test if the value is not numeric then I assign a value 0.  This works.  However, I have so many fields and many<cfif's so I'd like to use the NULL atribute of <cfqueryparam>. In the code above, this is just one of the many fields inside my cffunction.

Please help!

Thanks.

This topic has been closed for replies.
Correct answer -__cfSearching__-

In my form, I'm not populating this field as a test for the <cfqueryparam null attribute>. I thought this will handle that situation.  Otherwise, I need to check using <cfif not numeric then assign value=0>.  As i've said earlier this works but I have so many fields in the form and have to add <cfif> statements to all these fields.


me1019 wrote:

In my form, I'm not populating this field as a test for the <cfqueryparam null attribute>. I thought this will handle that situation. 

Nope. Even if you leave the text field blank, its still defined. So you are passing in an empty string as Steve said.  An empty string is not a number. So your function complains because you told it to expect a number:

        <cfargument name="intsteam_cap_mwe" type="numeric" required="no" default="0">

You either need to force the value to be numeric, such as using val(), or change the argument type.

2 replies

S
Steve Sommers
Legend
March 27, 2012

FYI, for optional parameters like this, I almost always avoid "numeric" and instead use "string" and do any validity check myself within the function (like using val() or len()). If the paramerter is not optional, then I might use "numeric".

M
me1019Author
Participant
March 27, 2012

Thanks for the info!

S
Steve Sommers
Legend
March 27, 2012

I believe cfqueryparam validates all the attributes first, when provided, before it logically decides whether or not it requires the parameters. For this, simply add a val() withing the value parameter:

<cfqueryparam value="#val(arguments.intsteam_cap_mwe)#" cfsqltype="cf_sql_integer" null="#YesNoFormat(NOT len(trim(arguments.intsteam_cap_mwe)))#">

Owainnorth
Owainnorth
Inspiring
March 27, 2012

It's not cfqueryparam complaining, it's the function arguments itself.

S
Steve Sommers
Legend
March 27, 2012

I have no idea what INTSTREAM_CAP_MWE is, as I have never seen it. But I do use very similar logic to what you're attempting all the time and it works great:

     ...

     <cfqueryparam value="#attributes.tweetDown#" cfsqltype="CF_SQL_VARCHAR" null="#evaluate('NOT len(attributes.tweetDown)')#" />,

     <cfqueryparam value="#val(attributes.severity)#" cfsqltype="CF_SQL_INTEGER" null="#evaluate('NOT len(attributes.severity)')#" />,

     ...

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices