We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Medium Priority
495 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

Comment
Watch Question

SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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

Author

Commented:
thanks. That makes sense and it works. Feel really dumb but sometimes I cant see the trees for the forest
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Welcome.. And glad to help you out.
As far your other comments, I would say that's where you begin to earn your experience.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.