Solved

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

Posted on 2012-03-22
5
278 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 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Build PDF in coldfusion 2 71
coldfusion, jQuery help 25 96
ColdFusion Need assistance updating my legacy zip function 13 40
coldfusion upload spreadsheet into the databse 2 44
Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

772 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