Link to home
Start Free TrialLog in
Avatar of earwig75
earwig75

asked on

One field crashes my grid when trying to sort. Problem with my query?

I have a CFGRID displaying 5 fields. One field is populated from a drop-down choice/reference table. This one field doesn't allow me to sort, and when I try I get the error: Error Invoking CFC / Error Executing Database Query.

In my master table I am storing the ID (numeric) for "OfferType" (OfferTypeID) but displaying the word/name value that goes with that (OfferType).

Below is a sample of my code - can someone help me figure out why I can't sort by this column?

Thank you.

CFC

<cffunction name="getrecords" 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 records="">

      <cfquery name="getrecords" datasource="#ds#">

            SELECT dbo.Tbl_Record_Master.ReferenceNumber, dbo.Rtbl_Record_OfferType.OfferType, dbo.Tbl_Record_Master.SomeField,
               dbo.Tbl_Record_Master.AnotherField, dbo.Tbl_Record_Master.PhoneNumberA,
               dbo.Tbl_Record_Master.Inactive
            FROM   dbo.Tbl_Record_Master LEFT OUTER JOIN
               dbo.Rtbl_Record_OfferType ON dbo.Tbl_Record_Master.OfferType = dbo.Rtbl_Record_OfferType.OfferTypeID

            WHERE dbo.Tbl_Record_Master.Inactive is Null
            <cfif len(arguments.getSearchString)>
            and lower(dbo.Tbl_Record_Master.PhoneNumberA) like <cfqueryparam value="%#lcase(arguments.getSearchString)#%" cfsqltype="cf_sql_varchar">

            or lower(dbo.Rtbl_Record_OfferType.OfferType) like <cfqueryparam value="%#lcase(arguments.getSearchString )#%" cfsqltype="cf_sql_varchar">
            </cfif>
            <cfif len(arguments.gridSortColumn) and len(arguments.gridSortDirection)>
            order by #arguments.gridsortcolumn# #arguments.gridsortdirection#
            </cfif>

            </cfquery>

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

</cffunction>      

GRID

<div id="Grid">
<cfform name="Grid">
<cfinput name="searchString" />
<cfinput type="button" name="searchBtn" value="Search" onclick="ColdFusion.Grid.refresh('Grid', false);" />
<cfinput type="reset" name="Reset" value="Reset" onClick="setTimeout(function() {ColdFusion.Grid.refresh('Grid', false);},500);" />
<br /><br />
<cfgrid  
    format="html" name="Grid" pagesize="20" width="455"
      bind="cfc:CopyRecord.getrecords({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection}, getSearchString())">
    <cfgridcolumn name="ReferenceNumber" width="4" header="ID" href="record_edit.cfm?" hrefkey="ReferenceNumber" />
      <cfgridcolumn name="PhoneNumberA" width="100" header="Phone A" />
      <cfgridcolumn name="AnotherField" width="100" header="Another Field" />
      <cfgridcolumn name="SomeField" width="100" header="Some Field" />
      <cfgridcolumn name="OfferType" width="100" header="Offer Type" />
</cfgrid>
</cfform>
</div>
Avatar of Coast Line
Coast Line
Flag of Canada image

what this is returning to u
getSearchString()
ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Side note, if you're doing any ajax work, enabling the ajax debugger in the CF Admin and using a plugin like firebug are essentially tools for troubleshooting.
Avatar of earwig75
earwig75

ASKER

You rule, that was it.
Welcome :) .. and thanks for posting exactly what we needed to answer the question. It was perfect.