9 Replies Latest reply: Sep 2, 2008 3:07 PM by paulferree RSS

    CFQUERYPARAM and text datatype

    paulferree Community Member
      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
        • 1. Re: CFQUERYPARAM and text datatype
          Newsgroup_User Community Member
          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#%">


          • 2. Re: CFQUERYPARAM and text datatype
            paulferree Community Member
            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
            • 3. Re: CFQUERYPARAM and text datatype
              Newsgroup_User Community Member
              > 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
              • 4. Re: CFQUERYPARAM and text datatype
                Newsgroup_User Community Member
                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
                • 5. Re: CFQUERYPARAM and text datatype
                  paulferree Community Member
                  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
                  • 6. Re: CFQUERYPARAM and text datatype
                    Newsgroup_User Community Member
                    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.

                    • 7. Re: CFQUERYPARAM and text datatype
                      Newsgroup_User Community Member
                      > <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
                      • 8. Re: CFQUERYPARAM and text datatype
                        Newsgroup_User Community Member
                        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.


                        • 9. Re: CFQUERYPARAM and text datatype
                          paulferree Community Member
                          Bingo! That was the problem...thank you very much.

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

                          Thanks again guys,
                          Paul