Dynamic Sort

Posted on 2007-10-16
Last Modified: 2013-12-24
I am trying to create a sortable query, but get this error message.
ODBC Error Code = 37000 (Syntax error or access violation)

<cfset sortby ="CASE WHEN (photo1 <> '0' AND Photo1Auth <> 1) OR (photo2 <> '0' AND Photo2Auth <> 1) OR (photo3 <> '0' AND Photo3Auth <> 1) OR (photo4 <> '0' AND Photo4Auth <> 1) OR (photo5 <> '0' AND Photo5Auth <> 1) THEN 1 ELSE 0 END DESC">

FROM table
ORDER BY #sortby#

If I hardcode it, it works fine

FROM table
ORDER BY CASE WHEN (photo1 <> '0' AND Photo1Auth <> 1) OR (photo2 <> '0' AND Photo2Auth <> 1) OR (photo3 <> '0' AND Photo3Auth <> 1) OR (photo4 <> '0' AND Photo4Auth <> 1) OR (photo5 <> '0' AND Photo5Auth <> 1) THEN 1 ELSE 0 END DESC

Any ideas??
Question by:JRockFL
    LVL 51

    Accepted Solution

    CF may be automatically escaping the single quotes.  ie replacing '  with ''.   What happens if you use ?

    ORDER BY #PreserveSingleQuotes(sortby)#

    Though on a personal note, I try and avoid this type of dynamic clause for a few reasons.  Using <cfqueryparam> can boost performance and helps avoid sql injection risks.
    LVL 51

    Expert Comment

    > Using <cfqueryparam>  
    ..    _instead_
    LVL 8

    Author Comment


    Great thinking with the cfqueryparam, I forgot all about that. It didn't work though, same results.
    #PreserveSingleQuotes(sortby)# worked though.

    Thank you!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    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

    In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
    One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    745 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

    15 Experts available now in Live!

    Get 1:1 Help Now