CODEWITHSUNDEEP
oracle-databasecoldfusion
Jeff Fabiny
Jeff Fabiny

Reputation: 325

cfqueryparam is acting weird

I'm having a problem running an update query in coldfusion. I get an error saying Invalid data '' for CFSQLTYPE CF_SQL_NUMERIC. I know the data is numeric because I output it before the error and it's right. I set the variable like so:

<cfset itemID = FORM.itemID * 1> <!--- The * 1 is to force a numeric operation, and
therefore a numeric datatype, and like I said, it outputs right. --->

<!--- This is the line (45) the error says is bad --->
WHERE itemid = <cfqueryparam value="#itemID#" cfsqltype="CF_SQL_NUMERIC">

One weird thing that happens is if I change the cfqueryparam to varchar, I still get the same error!!

WHERE itemid = <cfqueryparam value="#itemID#" cfsqltype="CF_SQL_VARCHAR">

And even weirder, when I completely take out the cfqueryparam and use a number, a new error comes up saying the line BEFORE (44) is bad now:

WHERE itemid = 20072 <!--- Changing to this produces a new error on the line before --->

<!--- Line 44, which is also using a cfqueryparam numeric. This new error is the same
as before, but on line 44 now instead of 45. --->
min_qty = <cfqueryparam value="#FORM.itemMinQuantity#" cfsqltype="CF_SQL_NUMERIC">

Any idea what's going on?

EDIT more code...

<cfquery name="qUpdateItem" datasource="#thedb#">
UPDATE items_something
SET
    itemdesc = <cfqueryparam value="#FORM.itemDescription#" cfsqltype="CF_SQL_VARCHAR">,
    room = <cfqueryparam value="#FORM.itemRoom#" cfsqltype="CF_SQL_VARCHAR">,
    value = <cfqueryparam value="#FORM.itemValue#" cfsqltype="CF_SQL_NUMERIC">,
    categoryid = <cfqueryparam value="#FORM.categoryID#" cfsqltype="CF_SQL_NUMERIC">,
    keywords = <cfqueryparam value="#FORM.itemKeywords#" cfsqltype="CF_SQL_VARCHAR">,
    manufact = <cfqueryparam value="#FORM.itemManufacturer#" cfsqltype="CF_SQL_VARCHAR">,
    partno = <cfqueryparam value="#FORM.itemPartNumber#" cfsqltype="CF_SQL_VARCHAR">,
    itemtitle = <cfqueryparam value="#FORM.itemTitle#" cfsqltype="CF_SQL_VARCHAR">,
    qty = <cfqueryparam value="#FORM.itemQty#" cfsqltype="CF_SQL_NUMERIC">,
    assembly_draw_no = <cfqueryparam value="#FORM.itemAssemblyDrawingNo#" cfsqltype="CF_SQL_NUMERIC">,
    bin = <cfqueryparam value="#FORM.itemBin#" cfsqltype="CF_SQL_VARCHAR">,
    shelf = <cfqueryparam value="#FORM.itemShelf#" cfsqltype="CF_SQL_VARCHAR">,
    min_qty = <cfqueryparam value="#FORM.itemMinQuantity#" cfsqltype="CF_SQL_NUMERIC">
WHERE itemid = <cfqueryparam value="#itemID#" cfsqltype="CF_SQL_NUMERIC">

Upvotes: 2

Views: 1190

Answers (1)

Milner
Milner

Reputation: 638

The issue may not have anything to do with the queryparam. Oracle errors on the last line of the executing statement (usually a where, group by or order by clause). If you supply the ORA- error and your table defs that would help.

But the error probably isn't related in any way to the queryparam. There's something else (identical columns in a join (e.g., the ID column in two tables in the query), missing comma, etc.).

EDIT:

Based on the text from the cfqueryparam help page you may want to try CF_SQL_FLOAT or CF_SQL_DECIMAL since CF_SQL_NUMERIC doesn't appear to have a direct correlation to Oracle. You can check your other columns too. I don't know if this would explain it but I do seem to remember seeing this in the past.

Upvotes: 2

Related Questions