[butchart]

This query works fine:

SELECT * FROM aTable
WHERE indiv = #form.id#

The form line that creates form.id in the calling form is

<input name="id" type="hidden" id="id" value="102702" />

What doesn't work is:

SELECT * FROM aTable
WHERE indiv = <cfqueryparam value="#form.id#" cfsqltype="cf_sql_integer">

indiv is type int.

I get the error message "Value can not be converted to requested type." The weird thing is this query worked fine until I added a couple of unrelated fields to this table. Where is the issue coming from?

[Craig328]

Hm. That's an interesting problem. I can't tell you exactly what's wrong with it because you could be doing other things or the value that's getting sent isn't the same as the form field default value but there are a few things you could try to validate and prep the form value before you try it in a query.

First and most obvious, put it through an IsNumeric() function test. From the error message you're getting back, the database doesn't see the value of what you're passing through as a valid integer. One thing that disqualifies a value from being accepted as an integer is that it's not a number at all. IsNumeric() should tell you that right away.

You may find that IsNumeric() fails even though the form value looks numeric. Sometimes that's due to the presence of spaces in the form value. For that, you'll want to also perform a Trim() function on the value. Trim() will remove any leading and trailing spaces (but not any within the string) from the form value. You'll want to do a Trim() first actually before evaluating it with IsNumeric().

So, with those in mind, your code should be amended to something like this:

<cfif IsNumeric(Trim(form.id))>
...
SELECT * FROM aTable
WHERE indiv = <cfqueryparam value="#Trim(form.id)#" cfsqltype="cf_sql_integer">

Now, you may find that you STILL get an error back, even when the CF says the value you're passing is is numeric and doesn't contain any leading or trailing spaces. The reason for that then will likely reside with a datatype mismatch OR that the size of the numeric value you're passing in exceeds the maximum number size for the indiv column on aTable in your database. Both of those type errors should return a different error message and not "Value can not be converted to requested type"...but once you've exhausted validating the data you're passing in, the next logical place to look is in the database itself and that means checking the datatype and maximum data size of the aTable.indiv column.

Give all that a spin and see how you do.

Good luck!