2 Replies Latest reply on Jan 19, 2013 8:28 AM by raupie

    cfqueryparam wildcards in tsql contains search

    raupie

      I would like to implement cfqueryparam for my autocomplete function for a project I'm working on.

       

      Currently the code I have is below and works by passing a argument variable into a tsql query.

       

      SELECT

                      users.user_id,

                      users.first_name,

                      users.last_name

      FROM

                       users

      WHERE

                      CONTAINS(*, '"*#arguments.term#*"')

       

      I would like to protect this by using cfqueryparam such as CONTAINS(*, <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.term#">) however the results are not as expected.

        • 1. Re: cfqueryparam wildcards in tsql contains search
          Dan Bracuk Level 5

          This has asterisks around the variable.

          CONTAINS(*, '"*#arguments.term#*"')

           

          This does not

          value="#arguments.term#"

          • 2. Re: cfqueryparam wildcards in tsql contains search
            raupie Level 1

            I've tried to include the * in the cfqueryparam but it does not return the results I'm looking for with a autocomplete function.

             

            CONTAINS(*, <cfqueryparam cfsqltype="cf_sql_varchar" value="*#arguments.term#*" />)

             

            No errors are returned but now no results return. I have a list of names, if I pass just 'bo' into the query it should return records for bob, bobby, etc. Without cfqueryparam this was working great, but when I include this, no results are returned until I type out the full name 'bob' or 'bobby'. This function is a autocomplete search that checks several columns for partial strings.