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

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

JOHNFROGAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
JOHNFROGAuthor Commented:
thanks. That makes sense and it works. Feel really dumb but sometimes I cant see the trees for the forest
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.