[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 541
  • Last Modified:

CFGRID Column Sorting Issue

Hi,
I have problems with using CFGRID and hope you can help. Here is issues
When sorting (using the header)  the 2nd and 3rd columns sort ok, the first causes an error stating an "Error in executing the Query".

Before I thow in a ton of code, I was wondering if anyone had any ideas or how to debug this?

Thanks,
hefterr
0
hefterr
Asked:
hefterr
  • 7
  • 5
2 Solutions
 
_agx_Commented:
Unless you have the same problem sorting any grid, it sounds data related.  I'd check both your ajax log and disk log files to see what the actual query error is. That might give you a better idea of the problem.
0
 
hefterrAuthor Commented:
Funny because the table displays fine.  Most grids work fine.  In the troublesome ones, only a few columns have sorting issues although they display fine.

Q:  ahhhh, how/where do you check these logs?

hefterr
0
 
_agx_Commented:
1) Enable logging in the CF Admin
Debugging & Logging > Debug Output Settings > 
    (check) Enable AJAX Debug Log Window
    (check) Enable Robust Exception Information
     ....

2) To see the ajax log, append ?cfdebug to your grid page's url
      http://localhost/yourGridPage.cfm?cfdebug

3) Regular logs vary by install type. Default locations are c:\ColdFusion9\logs and C:\ColdFusion9\runtime\logs
 
 
 
0
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

 
_agx_Commented:
In the troublesome ones, only a few columns have sorting issues although they display fine.

Do they have anything in common? Like always from the same table, the same data type, nullable  ..?
0
 
hefterrAuthor Commented:
Do they have anything in common?
I think what they have in common is a I populate a column with HTML anchor links.  The links show up fine and work.  Other columns seem randomly affected by the sorting issue.  But this seems to be a common thread.  I do this by using the "querynew" function after the SQL and add this column (a trick someone on this site showed me  - maybe it was you ;).

I then return the result of the querynew result.

To see the ajax log, append ?cfdebug to your grid page's url    http://localhost/yourGridPage.cfm?cfdebug.

I've done this but I'm not sure what to look for.  There is a statement in red in the beginning saying there was a problem but  all the other notices are green and I'm not sure what to look for?
0
 
_agx_Commented:
Errors should be in red. But basically you're looking for more detail about ""Error in executing the Query".  ie What was the query error so we know how to fix it.

I do this by using the "querynew" function after the SQL and add this column (a trick someone on this site showed me  - maybe it was you ;).

Could be ;-) Hmm... I'm not sure why that would cause a db error.  I'd look for the error detail first and see what it says. Don't forget to check the physical logs too. I think db errors should be logged there too.
0
 
hefterrAuthor Commented:
I don't think there is a DB/SQL error. I think the error is a general AJAX one about invoking the cfc only on some column headers.  I am not on my development pc now.  I'll look at the error logs tomorrow.

Is there a way in Firefox todebug what is happening using the developer tools?



0
 
hefterrAuthor Commented:
I think a see a potential problem.  I have added columns that are not in the database call (originally).

I use the typical code in the CFC
<cfquery name="members" datasource="cfartgallery">
      SELECT ARTISTID, FIRSTNAME, LASTNAME, CITY, STATE
      FROM ARTISTS
      <cfif gridsortcolumn neq ''>
      order by #gridsortcolumn# #gridsortdirection#
      </cfif>
  </cfquery>


Now this column does not exist in the db select.  It does exist in my  "querynew" which I return.  I think I have to add a dummy value to the SQL select like :
  Select 'dummy' as columnName.... so the SQL will be able to order by the column name.

What do you think?
0
 
_agx_Commented:
That's some awesome deductive work :)

add a dummy value to the SQL select

Sounds like it should work well. Just cast() it to the correct type if needed.
0
 
_agx_Commented:
I don't think there is a DB/SQL error.

(I know this posted earlier in the thought process but .. ;-)  It *has* to be a db error. A) That's what your original error message reported: "Error in executing the Query". and B) Your subsequent research backs that up ;-)

Select 'dummy' as columnName
Another thought is to validate it against a list of valid columns.  If it's not found, reset it to a default column.  As an added bonus you get some sql injection protection ;-)

ie
<cfif NOT listFindNoCase("artistID,firstName,....", gridsortcolumn)>
      <cfset gridsortcolumn = firstName>
</cfif>
0
 
_agx_Commented:
I would've selected yours as the sole solution, cause hey - it was some awesome 'sleuthing' :)
0
 
hefterrAuthor Commented:
I made sure the SQL matched the column names and we all OK now :)

Thanks for the other idea.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now