cfqueryparam causes no records to return some of the time

[godfogged]

I have a query set up as

select  a.coulmn1, a.coulmn2, c.coulmn3, a.coulmn4
from general_view a, second_view c
where a.id = c.id
and a.coumn1 = <cfqueryparam value="#form.selected_from_previous_dropdown#" cfsqltype="cf_sql_varchar" maxlength="20">
and c.some_other_column= ('#query2.other_column#')

if i remove the <cfqueryparam> portion it runs fine. If i add the query param it some times does not. Is the syntax in error or does cfquery param alter input in some way that would sometimes result in 0 matches when in fact the records should match the criteria?

[Adam Cameron.]

So you're saying that this:

<cfquery name="q" datasource="whatever">

select  a.coulmn1, a.coulmn2, c.coulmn3, a.coulmn4
from general_view a, second_view c
where a.id = c.id
and a.coumn1 = <cfqueryparam value="#form.selected_from_previous_dropdown#" cfsqltype="cf_sql_varchar" maxlength="20">
and c.some_other_column= ('#query2.other_column#')

</cfquery>

and this:

<cfquery name="q" datasource="whatever">

select  a.coulmn1, a.coulmn2, c.coulmn3, a.coulmn4
from general_view a, second_view c
where a.id = c.id
and a.coumn1 = '#form.selected_from_previous_dropdown#'

and c.some_other_column= ('#query2.other_column#')

</cfquery>

return different results?

What is the value of #form.selected_from_previous_dropdown# ?

--

Adam

PS: is that query reflective of your actual DB schema?  You spell "column" three different ways...

[godfogged]

they return different results. Sometimes it works properly other times it just returns an empty data set.

The value in the previously selected drop down is text

[Adam Cameron.]

The value in the previously selected drop down is text

Sure, but what is the actual value.  An example of the value that has been selected.  The SQL doesn't care where it came from, but it might care what the actual value is...

--

Adam

[godfogged]

It's a name like 'Smith'.

The database is a varchar.

a seperate query is looped through to populate that dropdown... didn't think that would matter but not sure at this point

[Adam Cameron.]

So you're saying that if you put this code in a template and run it, you get two different result sets:

<cfquery name="q1" datasource="whatever">

select  a.coulmn1, a.coulmn2, c.coulmn3, a.coulmn4
from general_view a, second_view c
where a.id = c.id
and a.coumn1 = <cfqueryparam value="Smith" cfsqltype="cf_sql_varchar" maxlength="20">
and c.some_other_column= ('#query2.other_column#')

</cfquery>

<cfquery name="q2" datasource="whatever">

select  a.coulmn1, a.coulmn2, c.coulmn3, a.coulmn4
from general_view a, second_view c
where a.id = c.id
and a.coumn1 = 'Smith'

and c.some_other_column= ('#query2.other_column#')

</cfquery>

<cfdump var="#q1#">

<cfdump var="#q2#">

?

--

Adam

[-__cfSearching__-]

and c.some_other_column= ('#query2.other_column#')

Not to distract from Adam's point ... but is that actually valid sql ?

[godfogged]

correct. the way this page was set up a user makes a selection from a dropdown. the value of the drop down is put into a hidden field, the page submits to its self and then the value in the hidden field is used in a query that populates the next dropdown. the hidden fields use the following syntax for the value : value="<cfoutput>#htmleditformat(form.first_drop_down)#</cfoutput>"

is it possiblw that the htmleditformat + the cfqueryparam is causing the issue?

[ilssac]

godfogged wrote:

is it possiblw that the htmleditformat + the cfqueryparam is causing the issue?

It would not surprise me.

HTMLEditFormat() will change the text of the string, if you change the text of the string you use in your comparison, you are going to get different results.

[godfogged]

but it always works without the <cfqueryparam> and only occasionally does not work with it. do you or anyone else happen to know why the combination of <cfqueryparam> and htmleditformat would cause a query to return an empty result and not generate an error?

[ilssac]

It is not going to generate an error.  <cfqueryparam...> is expecting a string, With or without htmleditformat() you are giving it a string.  Just, possibly, different strings, but perfectly acceptable strings none the less.

It is only going to change the strings, if the strings contains characters that need to be escaped in HTML, like the ampersand character {&}.  Without  htmleditformat() "Dick & Jane" will be just that.  With htmleditformat(), that string will turn into "DIck & Jane".  Neither of these will cause an error but they sure are going to return different results from the database.

I can only surmise that it 'always' works without that <cfqueryparam...> in that somehow or the other the escaped HTML is being unescaped again before being sent to the database, but with <cfqueryparam...> that escaped HTML is preserved.  But I wouldn't know this without trial and error testing.

[Dan_Bracuk]

The maxlength attribute looks like it might contribute to unexpected results.  When it doesn't work, does it crash or simply return something other than what you anticipated?

[godfogged]

i tried it without the maxlength and got the same result.

[godfogged]

I have to do more testing but i may found a solution. I changed the cfsqltype to char instead of varchar.