?
Solved

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

Posted on 2009-07-05
4
Medium Priority
?
424 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 2000 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

764 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