Copy link to clipboard
Copied
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.
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,
...Copy link to clipboard
Copied
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)))#">
Copy link to clipboard
Copied
It's not cfqueryparam complaining, it's the function arguments itself.
Copy link to clipboard
Copied
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)')#" />,
...
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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#"
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
It's not cfqueryparam complaining, it's the function arguments itself.
Good catch.
Copy link to clipboard
Copied
Ah, now I see intsteam_cap_mwe. Good catch.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
Tried the code but still giving the same error.
Copy link to clipboard
Copied
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".
Copy link to clipboard
Copied
Thanks for the info!