Solved

enable a Descending Sort Order on SQL Datasource using a CASE statement

Posted on 2009-07-05
4
419 Views
Last Modified: 2012-06-27
I have run out of ideas on how to force a descending sort order  within a declared SQLDatasource on my aspx page.

If I want to have ascending order it is working fine but when I try to append a DESC onto the CASE WHEN THEN statement it all crashes and burns.

If you are confused then check the code it should make sense.

I found this solution on
http://www.velocityreviews.com/forums/t118269-how-use-dropdown-to-fill-order-by-of-sqldata-source.html

It works fine for default order (ASC) but as soon as I try to introduce DESC order (eg
WHEN 'starRating' THEN starRating DESC
)
 I get an error saying syntax around DESC is incorrect.

Any help will be mostl appreciated
<asp:SqlDataSource ID="SqlDataSource_ContactHyperlink" runat="server" ConnectionString="<%$ ConnectionStrings:conn_d1 %>"
                                            SelectCommand="SELECT IndexID, OrderID, 
                                            hyperlink_url, hyperlink_memo, origin_UserName, origin_UserID, origin_Visibility, 
                                            origin_dateTime, starRating FROM tbl_bookmarks
                                            WHERE origin_UserID = @curUserID
                                            ORDER BY CASE @SortBy
                                            WHEN 'IndexID' THEN IndexID 
                                            WHEN 'starRating' THEN starRating
                                            END"
                                            
                                            ProviderName="System.Data.SqlClient">
                                            <SelectParameters>
                                                <asp:SessionParameter Name="curUserID" SessionField="UserGUID" Type="String" />
                                                <asp:ControlParameter Name="SortBy" ControlID="DropDownList_sortOrder" PropertyName="SelectedValue" Type="String" />
                                                
                                            </SelectParameters>
                                        </asp:SqlDataSource>

Open in new window

0
Comment
Question by:JOHNFROG
[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
4 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24779612
Desc should be after END:

SELECT IndexID, OrderID,
hyperlink_url, hyperlink_memo, origin_UserName, origin_UserID, origin_Visibility,
origin_dateTime, starRating FROM tbl_bookmarks
WHERE origin_UserID = @curUserID
ORDER BY CASE @SortBy
WHEN 'IndexID' THEN IndexID
WHEN 'starRating' THEN starRating
END DESC

Hope this helps
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24779658
Above statement applies DESC to both IndexID and starRating.

I assume you have to use a dynamic sql like this:
declare @sql nvarchar(1000);
set @sql = 'SELECT IndexID, OrderID,
hyperlink_url, hyperlink_memo, origin_UserName, origin_UserID, origin_Visibility,
origin_dateTime, starRating FROM tbl_bookmarks
WHERE origin_UserID = ' + @curUserID + (select CASE @SortBy WHEN 'IndexID' THEN 'IndexID' WHEN 'starRating' THEN 'starRating DESC' END ) ;
exec sp_executesql @sql;

Open in new window

0
 

Author Closing Comment

by:JOHNFROG
ID: 31599904
thanks. That makes sense and it works. Feel really dumb but sometimes I cant see the trees for the forest
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24779738
Welcome.. And glad to help you out.
As far your other comments, I would say that's where you begin to earn your experience.
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 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