1

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

Tomalak
  • 332,285
  • 67
  • 532
  • 628
justacoder
  • 2,684
  • 6
  • 47
  • 78
  • The "id" type is `cf_sql_integer`. So if the error message says `cf_sql_numeric` that points to one of the other fields being the problem, like `form.postmonth` or `form.postyear`. – Leigh Mar 31 '12 at 03:25
  • The cfqueryparam tags for the INSERT are the same as the update statement, and that has no problems. – justacoder Mar 31 '12 at 03:33
  • 1
    I suggest getting rid of the name of you query. Also, get rid of the null="no". Try that. I will be working in the morning and can help you solve this problem. – Evik James Mar 31 '12 at 03:33
  • 1
    Re: *cfqueryparam tags are the same* Yeah, but the data is probably different. The error suggests the problem is with the input, rather than the tags. Specifically one of the `numeric` values. Can you dump the FORM scope and post the actual values when the error occurs? – Leigh Mar 31 '12 at 03:46
  • i would recommend stripping out all the cfquery and cfqueryparam tags and just dumping out the SQL statement with the form values outputted. That will tell you exactly where the error is. Like others have said, the postmonth or postyear column appear to be the issue – Matt Busche Mar 31 '12 at 03:56
  • Just dump the variables before the query and see if they really are what you think they are. – baynezy Mar 31 '12 at 08:47
  • Personally I don't rely on queuryparam for validation. Escaping -- yes. But you still want to validate the form values and display meaningful errors to the user. So if you are using `StructKeyExists(form, "xyz") AND isNumeric(form.xyz)`, it also makes sure you wont push empty string to `cf_sql_numeric` which is most likely the case here. – Sergey Galashyn Mar 31 '12 at 09:23
  • I got rid of null="no" validation, and seems to work again. CFDUMP showed me all values that I expected, so nothing was actually null, or incorrect, while being passed. I'll chalk this one up to "huh?" – justacoder Mar 31 '12 at 13:13
  • 3
    @AngrySpartan `null="no"` is *not* a validation. It is the switch that decides whether a NULL value is passed to the DB. Usually there is a boolean expression here, like `null="#Trim(FORM.foo) eq ''#"`, which sets the parameter to NULL when the form value is empty. Just don't use it with `yes` or `no` values, as this makes no sense at all. – Tomalak Mar 31 '12 at 15:04
  • 1
    @AngrySpartan - Something does not add up ... Are you sure you posted the correct code? Because the error message seems pretty clear: an empty string "" was passed to one of the `cf_sql_numeric` columns instead of a number. Since `null="no"` is the default anyway (and it is not even used on your numeric columns), its presence should not have any effect one way or the other. – Leigh Mar 31 '12 at 20:22
  • My faulty attention to details was the issue. The postmonth variable wasn't being passed with a non-empty value. Thanks for the help, everyone. – justacoder Apr 01 '12 at 01:41
  • 1
    Ah, so my first guess was right after all :P – Leigh Apr 01 '12 at 23:26

2 Answers2

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

Mark A Kruger
  • 7,183
  • 20
  • 21
1

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.

Mike Henke
  • 864
  • 1
  • 8
  • 22