[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2012-03-22
5
Medium Priority
?
304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

649 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