Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

636 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