Solved

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

Posted on 2009-07-05
4
417 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
  • 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
vb.net - How to check if current user is an administrator? 6 34
SQL Login 17 40
SQL Exceptions 3 39
Variable Event ? 3 23
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…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

895 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

13 Experts available now in Live!

Get 1:1 Help Now