CFQUERYPARAM and text datatype

[paulferree]

What is the proper cfsqltype for a text/ntext datatype?? I've used char, varchar and longvarchar and all of them give me invalid parameter bindings.

The query code:

<cfquery name="querynam" datasource="#DSN#">
SELECT v_id FROM tblV
WHERE v_keywords LIKE '%<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.search#">%'
</cfquery>

Thanks!
Paul Ferree

[Newsgroup_User]

paulferree wrote:
> What is the proper cfsqltype for a text/ntext datatype?? I've used char,
> varchar and longvarchar and all of them give me invalid parameter bindings.
>
> The query code:
>
> <cfquery name="querynam" datasource="#DSN#">
> SELECT v_id FROM tblV
> WHERE v_keywords LIKE '%<cfqueryparam cfsqltype="cf_sql_longvarchar"
> value="#form.search#">%'
> </cfquery>
>
> Thanks!
> Paul Ferree
>

I don't think your problem is a inproper datatype, but just plain bad
sysntax. You can not bind part of a value like you are. The entire
value, quotes and percents must be part of the bound value.

I.E.

LIKE <cfqueryparam cfsqltype="{your choice here}" value="%#form.search#%">

[paulferree]

Well...I tried that, but when I put the entire value in there the returned rows are 0.

If I do the exact same query removing the cfqueryparam, it pulls up 45.

Here is the cf debugging info:

SELECT vendor_id FROM tblVendors
WHERE vendor_keywords LIKE ?
ORDER BY vendor_package_id DESC, vendor_company ASC

Query Parameter Value(s) -
Parameter #1(cf_sql_char) = '%hotel%'

Thanks

[Newsgroup_User]

> Well...I tried that, but when I put the entire value in there the returned rows
> are 0.

What is the EXACT syntax you used for the <cfqueryparam>, using Ian's
suggestion (which is correct)?

--
Adam

[Newsgroup_User]

paulferree wrote:

> SELECT vendor_id FROM tblVendors
> WHERE vendor_keywords LIKE ?
> ORDER BY vendor_package_id DESC, vendor_company ASC
>
> Query Parameter Value(s) -
> Parameter #1(cf_sql_char) = '%hotel%'
>
> Thanks
>

Just to be clear here, you are saying that the following query returns
45 records, but the above one does not?

SELECT vendor_id FROM tblVendors
WHERE vendor_keywords LIKE '%hotel%'
ORDER BY vendor_package_id DESC, vendor_company ASC

[paulferree]

This is the exact query:

<cfquery name="getvendors" datasource="#DSN#">
SELECT vendor_id FROM tblVendors
WHERE vendor_keywords LIKE <cfqueryparam cfsqltype="cf_sql_char" value="'%#form.search#%'">
ORDER BY vendor_package_id DESC, vendor_company ASC
</cfquery>

Paul

[Newsgroup_User · Accepted solution]

paulferree wrote:

> Query Parameter Value(s) -
> Parameter #1(cf_sql_char) = '%hotel%'
>
> Thanks
>

I just did a test, I think you have two many quotes in your query.
SELECT * FROM art WHERE ARTNAME LIKE ?

Query Parameter Value(s) -
Parameter #1(cf_sql_varchar) = %Do%


Notice that the Parameter does not have quotes around it. I think you
had this.

LIKE <cfqueryparam cfsqltype="{your choice here}"
value="'%#form.search#%'">

Which would search for the string '%hotel%' not the desired %hotel%.
Get rid if the extra quotes if you have them.

[Newsgroup_User]

> <cfqueryparam cfsqltype="cf_sql_char"
> value="'%#form.search#%'">

You have an extra set of single quotes in your value there. When one is
using <cfqueryparam> one does not need the quotes around string values.

It's probably good to familiarise yourself with the documentation for any
tag / function you use, before using it:

http://livedocs.adobe.com/coldfusion/8/Tags_p-q_18.html

--
Adam

[Newsgroup_User]

paulferree wrote:

> WHERE vendor_keywords LIKE <cfqueryparam cfsqltype="cf_sql_char"
> value="'%#form.search#%'">

As I just said too many quotes in there. You just need one set. Either
will do: value="%#form.search#%" OR value='%#form.search#%' otherwise
the inner quotation marks become part of the string the database is
trying to match.

[paulferree]

Bingo! That was the problem...thank you very much.

Inching ever so slightly towards never making stupid mistakes...haha...right.

Thanks again guys,
Paul