Passed value being treated as empty in CFQUERYPARAM

Question

This is baffling me greatly as I have done this a hundred times, but now it fails me.

I am doing a basic query update using CF params, and the primary key for the table is passing an empty value in, resulting in a critical failure.

Even if I enter a hardcoded number into the #form.id placeholder#, I get this:

Invalid data '' for CFSQLTYPE CF_SQL_NUMERIC.

Here is my code base

<cfquery name="updateIdea" datasource="#request.db#">
            UPDATE freshideas
            SET subject = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.subject#" null="no" />,
                content = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.content_text#" />,
                postmonth = <cfqueryparam cfsqltype="cf_sql_numeric" value="#form.postmonth#" />,
                postyear = <cfqueryparam cfsqltype="cf_sql_numeric" value="#form.postyear#" />,
                imglink = <cfqueryparam cfsqltype="cf_sql_varchar" value="#image#" />,
                oindex = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.oindex#" null="no" maxlength="3" />
            WHERE id = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.id#" null="no" />
        </cfquery>

The insert works fine, and the column ID is auto_increment int(5).

Answer 1

It's a long shot but if you made a recent change to your table you might have a problem with caching the schema. Try restarting your CF server or even just adding some spaces to your query. Here's a post with more info.

http://www.coldfusionmuse.com/index.cfm/2005/4/29/dbschemaChange

Answer 2

I get this alot in my current code base. When adding the cfqueryparams the developer assumed the values and sometimes the expected integer is an empty string.

If empty string/null is appropriate use this http://www.carehart.org/blog/client/index.cfm/2007/3/5/cfqueryparam_null

In my case, the integer should have been an integer so it is usually a problem with how that variable is getting set like a query returning 0 records.