<cfqueryparam with null attribute not working for me>

[me1019]

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.

[Steve Sommers]

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]

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

[Steve Sommers]

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)')#" />,

     ...

[me1019]

INTSTREAM_CAP_MWE is an argument passed to the function call.  I tried-

<cfqueryparam value="#val(arguments.intsteam_cap_mwe)#" cfsqltype="CF_SQL_INTEGER" null="#evaluate('NOT len(arguments.intsteam_cap_mwe)')#" />

It did not work for me either.

[-__cfSearching__-]

me1019 wrote:

INTSTREAM_CAP_MWE is an argument passed to the function call.  I tried-

<cfqueryparam value="#val(arguments.intsteam_cap_mwe)#" cfsqltype="CF_SQL_INTEGER" null="#evaluate('NOT len(arguments.intsteam_cap_mwe)')#" />

It did not work for me either.

As Owain said, the cfqueryparam is not the problem. The error is coming from the function. You are passing in a string when the function expects a number instead. So cffunction aborts the request long before it ever gets to the query.

[Owainnorth]

So do I get a prize or something?

Couldn't help but notice the evaluate() in here is completely unnecessary:

null="#evaluate('NOT len(attributes.tweetDown)')#"

I just tend to do something like this:

null="#len(attributes.tweetDown) EQ 0#"

[Steve Sommers]

I know. I think you and possibly others have mentioned that before. I just have a hard time putting hashes around a non-variable/non-function name. "#len(xxx)#" or "#evaluate'len(xxx) EQ 0')#" I'm fine with, "#len(xxx) EQ 0#" looks wrong.

[Owainnorth]

I know what you mean, doesn't look too neat. If you were that fussed:

<cfset IsNull = len(x) EQ 0 />

null="#IsNull#"

It'll definitely require less processing than adding in an evaluate() though.

[-__cfSearching__-]

So do I get a prize or something?

Haha, I could not figure out what that meant until I logged into the web application.

me1019 - strictly speaking the "correct answer" was from Owain and Steve deduced the empty string problem.

[-__cfSearching__-]

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

Good catch.

[Steve Sommers]

Ah, now I see intsteam_cap_mwe. Good catch.

[Steve Sommers]

The code that is calling your createReference function is probably passing "" as a value for intsteam_cap_mwe -- this is the source of the squawk.

[me1019]

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.

[-__cfSearching__- · Accepted solution]

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.

[me1019]

Tried the code but still giving the same error.

[Steve Sommers]

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".

[me1019]

Thanks for the info!