?
Solved

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

Posted on 2012-03-22
5
Medium Priority
?
309 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 16

Expert Comment

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

Accepted Solution

by:
_agx_ earned 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Screencast - Getting to Know the Pipeline
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

850 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