Solved

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

Posted on 2012-03-22
5
271 Views
Last Modified: 2012-03-22
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>
0
Comment
Question by:earwig75
  • 3
5 Comments
 
LVL 15

Expert Comment

by:myselfrandhawa
ID: 37753610
what this is returning to u
getSearchString()
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 37753742
order by #arguments.gridsortcolumn# #arguments.gridsortdirection#


Just a guess .. but IIRC both tables contain a column named "OfferType". So if you try and sort by that column the db gets confused and throws an "ambiguous column" error because it doesn't know which column to use.

You either have to indicate the source table manually
<cfif len(arguments.gridSortColumn) and len(arguments.gridSortDirection)>
      <cfif arguments.gridSortColumn eq "OfferType">
          order by dbo.Rtbl_Record_OfferType.OfferType #arguments.gridsortdirection#
      <cfelse>
            order by #arguments.gridsortcolumn# #arguments.gridsortdirection#
      </cfif>
</cfif>
         
          ... OR ...

Create a completely unique alias for that column in your SELECT. Then use the Alias in the rest of your code (like for the grid column name)  That's the better option, but it'll require changing some of your existing code.

SELECT  dbo.Tbl_Record_Master.ReferenceNumber
            , dbo.Rtbl_Record_OfferType.OfferType   AS SomeUniqueName
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37753754
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.
0
 

Author Closing Comment

by:earwig75
ID: 37753884
You rule, that was it.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37754027
Welcome :) .. and thanks for posting exactly what we needed to answer the question. It was perfect.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now