[cfaussie] Re: SQL search in CFMX 7

Tue, 18 Apr 2006 21:23:43 -0700


Is it possible that you are trying to search a numeric field? E.g. WHERE req_id LIKE '%9%' ? That doesn't sound like it should work. Otherwise, cfparam works fine as Steve noted. To my knowledge it does something like this
 
WHERE [field] LIKE <cfparam value="%#myvar#%" type="cf_sql_varchar">
 
=
 
DECLARE @var1 varchar
SET @var1 = '%#myvar#%' 
 
...... WHERE [field] LIKE @var1 ---> this works
 
If you try and think objectively about what you are doing you might find a solution
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED]On Behalf Of George
Sent: Wednesday, 19 April 2006 2:11 PM
To: [email protected]
Subject: [cfaussie] Re: SQL search in CFMX 7

It works on this ('=') but not for 'like %'.

On 19/04/06, Steve Onnis <[EMAIL PROTECTED]> wrote:

then strip out the cfparam tags
 
   <CFIF LEN(ARGUMENTS.SearchField) AND LEN(ARGUMENTS.searchstring)>
    WHERE
     <CFSWITCH _expression_="#ARGUMENTS.SearchField#">
      <CFCASE value="Requester">
       Requester = '#ARGUMENTS.searchstring#'
      </CFCASE>
      <CFCASE value="Description">
       Description = '#ARGUMENTS.searchstring#'
      </CFCASE>
      <CFCASE value="jobStatus">
       jobStatus = '#ARGUMENTS.searchstring#'
      </CFCASE>
      <CFDEFAULTCASE>
       Req_id = '#ARGUMENTS.searchstring#'
      </CFDEFAULTCASE>
     </CFSWITCH>
   </CFIF>
 
populate and run the query and see what it ends up looking like.  run it against the database and see if you get anything
-----Original Message-----
From: [email protected] [mailto:[email protected]]On Behalf Of George
Sent: Wednesday, April 19, 2006 1:55 PM
To: [email protected]
Subject: [cfaussie] Re: SQL search in CFMX 7

Hi Steve,
 
I think it's a bit hard to do it without CF. I have to pass parameters (e.g. search strings, criterias). See more codes below.
 
   <CFIF LEN(ARGUMENTS.SearchField) AND LEN(ARGUMENTS.searchstring)>
    WHERE
     <CFSWITCH _expression_="#ARGUMENTS.SearchField#">
      <CFCASE value="Requester">
       Requester = <cfqueryparam value="#ARGUMENTS.searchstring#" cfsqltype="cf_sql_varchar"/>
      </CFCASE>
      <CFCASE value="Description">
       Description = <cfqueryparam value="#ARGUMENTS.searchstring#" cfsqltype="cf_sql_varchar"/>
      </CFCASE>
      <CFCASE value="jobStatus">
       jobStatus = <cfqueryparam value="#ARGUMENTS.searchstring#" cfsqltype="cf_sql_varchar"/>
      </CFCASE>
      <CFDEFAULTCASE>
       Req_id = <cfqueryparam value="#ARGUMENTS.searchstring#" cfsqltype="cf_sql_integer"/>
      </CFDEFAULTCASE>
     </CFSWITCH>
   </CFIF>


George
 
On 19/04/06, Steve Onnis <[EMAIL PROTECTED] > wrote:

how about trying it without the queryparam and see if it works?  and then actually output whatt he query is and run it outside of cf directly on the database.
 
Maybe its not CF at all....maybe it the query or the data itself
 
Steve
-----Original Message-----
From: [email protected] [mailto:[email protected]]On Behalf Of George
Sent: Wednesday, April 19, 2006 12:44 PM
To: [email protected]
Subject: [cfaussie] Re: SQL search in CFMX 7

Hi Dale,
 
Thank you for the suggestion. However, it doesn't work :( (it returns nothing).
 
Regards,
George

 
On 19/04/06, Dale Fraser <[EMAIL PROTECTED]> wrote:

Try this

 

Description LIKE <cfqueryparam value="%#ARGUMENTS.searchstring#%" cfsqltype="cf_sql_varchar"/>

Regards
Dale Fraser

From: [email protected] [mailto: [email protected]] On Behalf Of George
Sent: Wednesday, 19 April 2006 12:17 PM
To: [email protected]
Subject: [cfaussie] SQL search in CFMX 7

 

Hi all,

 

I try to search a database using 'like' operator but it's not working in CFMX 7. Can anyone see what's wrong?

..............

Where

    Description LIKE '%<cfqueryparam value="#ARGUMENTS.searchstring#" cfsqltype="cf_sql_varchar"/>%'

 

Thanks,

George










--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "cfaussie" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/cfaussie
-~----------~----~----~----~------~----~------~--~---

Reply via email to