Thread: CFC SQL problem

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    12
    Rep Power
    0

    CFC SQL problem


    Hello. I am using a CFGRID and I have a search box that filters the results. One database field is called "inactive". The grid should only populate with records where "inactive" is NULL but it shows all records anyway. It does filter out the inactive records but only when a search is run Below is what I am using in my CFC. Could someone help and tell me what could be wrong? Thank you.


    <cffunction name="myfunction" access="remote" returntype="struct">
    <cfargument name="page" required="true" />
    <cfargument name="pageSize" required="true" />
    <cfargument name="gridsortcolumn" required="true" />
    <cfargument name="gridsortdirection" required="true" />
    <cfargument name="getSearchString" required="true" />
    <cfset var data="">

    <cfquery name="getdata" datasource="#this.source#">
    SELECT IDNumber, FirstField, SecondField, ThirdField, ForthField, Inactive
    FROM masterTable

    WHERE Inactive is Null

    and lower(SecondField) like <cfqueryparam value="%#lcase(arguments.getSearchString)#%" cfsqltype="cf_sql_varchar">


    or lower(ThirdField) like <cfqueryparam value="%#lcase(arguments.getSearchString )#%" cfsqltype="cf_sql_varchar">


    <cfif len(arguments.gridSortColumn) and len(arguments.gridSortDirection)>
    order by #arguments.gridsortcolumn# #arguments.gridsortdirection#
    </cfif>

    </cfquery>

    <cfreturn queryConvertForGrid(getdata, arguments.page, arguments.pageSize)>

    </cffunction>
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,278
    Rep Power
    968
    I'd say something is wrong with your query. Try running it directly against the database and see what you get.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    131
    Rep Power
    7
    As mentioned in your other thread when mixing AND/OR operators you need to use parenthesis.

    Comments on this post

    • kiteless agrees
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,278
    Rep Power
    968
    Good catch!

    Originally Posted by cfSearching
    As mentioned in your other thread when mixing AND/OR operators you need to use parenthesis.

IMN logo majestic logo threadwatch logo seochat tools logo