Skip navigation
paulferree
Currently Being Moderated

CFQUERYPARAM and text datatype

Sep 2, 2008 1:34 PM

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
 
Replies
  • Currently Being Moderated
    Sep 2, 2008 1:41 PM   in reply to paulferree
    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#%">


     
    |
    Mark as:
  • Currently Being Moderated
    Sep 2, 2008 2:27 PM   in reply to paulferree
    > 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
     
    |
    Mark as:
  • Currently Being Moderated
    Sep 2, 2008 2:32 PM   in reply to paulferree
    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
     
    |
    Mark as:
  • Currently Being Moderated
    Sep 2, 2008 2:43 PM   in reply to paulferree
    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Sep 2, 2008 2:46 PM   in reply to paulferree
    > <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
     
    |
    Mark as:
  • Currently Being Moderated
    Sep 2, 2008 2:47 PM   in reply to paulferree
    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.


     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points