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.Refe renceNumbe r, dbo.Rtbl_Record_OfferType. OfferType, dbo.Tbl_Record_Master.Some Field,
dbo.Tbl_Record_Master.Anot herField, dbo.Tbl_Record_Master.Phon eNumberA,
dbo.Tbl_Record_Master.Inac tive
FROM dbo.Tbl_Record_Master LEFT OUTER JOIN
dbo.Rtbl_Record_OfferType ON dbo.Tbl_Record_Master.Offe rType = dbo.Rtbl_Record_OfferType. OfferTypeI D
WHERE dbo.Tbl_Record_Master.Inac tive is Null
<cfif len(arguments.getSearchStr ing)>
and lower(dbo.Tbl_Record_Maste r.PhoneNum berA) like <cfqueryparam value="%#lcase(arguments.g etSearchSt ring)#%" cfsqltype="cf_sql_varchar" >
or lower(dbo.Rtbl_Record_Offe rType.Offe rType) like <cfqueryparam value="%#lcase(arguments.g etSearchSt ring )#%" cfsqltype="cf_sql_varchar" >
</cfif>
<cfif len(arguments.gridSortColu mn) and len(arguments.gridSortDire ction)>
order by #arguments.gridsortcolumn# #arguments.gridsortdirecti on#
</cfif>
</cfquery>
<cfreturn queryConvertForGrid(getrec ords, 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.r efresh('Gr id', false);" />
<cfinput type="reset" name="Reset" value="Reset" onClick="setTimeout(functi on() {ColdFusion.Grid.refresh(' Grid', false);},500);" />
<br /><br />
<cfgrid
format="html" name="Grid" pagesize="20" width="455"
bind="cfc:CopyRecord.getre cords({cfg ridpage},{ cfgridpage size},{cfg ridsortcol umn},{cfgr idsortdire ction}, 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>
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.Refe
dbo.Tbl_Record_Master.Anot
dbo.Tbl_Record_Master.Inac
FROM dbo.Tbl_Record_Master LEFT OUTER JOIN
dbo.Rtbl_Record_OfferType ON dbo.Tbl_Record_Master.Offe
WHERE dbo.Tbl_Record_Master.Inac
<cfif len(arguments.getSearchStr
and lower(dbo.Tbl_Record_Maste
or lower(dbo.Rtbl_Record_Offe
</cfif>
<cfif len(arguments.gridSortColu
order by #arguments.gridsortcolumn#
</cfif>
</cfquery>
<cfreturn queryConvertForGrid(getrec
</cffunction>
GRID
<div id="Grid">
<cfform name="Grid">
<cfinput name="searchString" />
<cfinput type="button" name="searchBtn" value="Search" onclick="ColdFusion.Grid.r
<cfinput type="reset" name="Reset" value="Reset" onClick="setTimeout(functi
<br /><br />
<cfgrid
format="html" name="Grid" pagesize="20" width="455"
bind="cfc:CopyRecord.getre
<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>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
You rule, that was it.
Welcome :) .. and thanks for posting exactly what we needed to answer the question. It was perfect.
getSearchString()