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

Gridview SqlDataSource toggle between Ascending and Descding order using a control param

Medium Priority
539 Views
Last Modified: 2013-11-07
Am using a simple radiobutton to let the user toggle between ascending and descending order on a gridview. Already have a CASE WHEN THEN statement to let the user toggle between the sort key but the same approach does not seem to work on ASC or DESC.

See code for a clearer explanation. NB : code does not work
<asp:SqlDataSource ID="SqlDataSource_ContactHyperlink" runat="server" ConnectionString="<%$ ConnectionStrings:conn_db1 %>"
                                            ProviderName="System.Data.SqlClient" 
                                           SelectCommand="SELECT IndexID, OrderID, hyperlink_url, DomainName, 
                                            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 cast(IndexID AS varchar(256))
                                            WHEN 'starRating' THEN cast(starRating AS varchar(256))
                                            WHEN 'domain' THEN DomainName
                                            END
                                            CASE @SortOrder
                                            WHEN 'ascending' THEN ASC
                                            WHEN 'descending' THEN DESC
                                            END">
                                            <SelectParameters>
                                                <asp:SessionParameter Name="curUserID" SessionField="UserGUID" Type="String" />
                                                <asp:ControlParameter ControlID="DropDownList_sortOrder" Name="SortBy" PropertyName="SelectedValue" Type="String" />
                                                 <asp:ControlParameter ControlID="RadioButtonList_sortType" Name="SortOrder" PropertyName="SelectedValue" Type="String" />
                                            </SelectParameters>
                                        </asp:SqlDataSource>

Open in new window

Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
You cannot achieve this is in a T-SQL statement and you need to use Dynamic SQL like below:

Hope this helps
Declare @sql nvarchar(1000);
set @sql = 'SELECT IndexID, OrderID, hyperlink_url, DomainName, 
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 cast(IndexID AS varchar(256))
WHEN 'starRating' THEN cast(starRating AS varchar(256))
WHEN 'domain' THEN DomainName
END ' + (SELECT CASE @SortOrder WHEN 'ascending' THEN 'ASC' WHEN 'descending' THEN 'DESC' END);
EXEC sp_executesql @sql;

Open in new window

Author

Commented:
think I am losing something in my implementation as am getting an error

"Incorrect syntax near 'IndexID'

See code snippet my interpretation of what you have suggested
  <asp:SqlDataSource ID="SqlDataSource_ContactHyperlink" runat="server" ConnectionString="<%$ ConnectionStrings:conn_trymoreharder %>"
                                            ProviderName="System.Data.SqlClient" 
                                           SelectCommand="Declare @sql nvarchar(1000);
                                            set @sql = 'SELECT IndexID, OrderID, hyperlink_url, DomainName, 
                                            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 cast(IndexID AS varchar(256))
                                            WHEN 'starRating' THEN cast(starRating AS varchar(256))
                                            WHEN 'domain' THEN DomainName
                                            END ' + (SELECT CASE @SortOrder WHEN 'ascending' THEN 'ASC' WHEN 'descending' THEN 'DESC' END);
                                            EXEC sp_executesql @sql;">
 
                                            <SelectParameters>
                                                <asp:SessionParameter Name="curUserID" SessionField="UserGUID" Type="String" />
                                                <asp:ControlParameter ControlID="DropDownList_sortOrder" Name="SortBy" PropertyName="SelectedValue" Type="String" />
                                                <asp:ControlParameter ControlID="RadioButtonList_sortType" Name="SortOrder" PropertyName="SelectedValue" Type="String" />
                                            </SelectParameters>
                                        </asp:SqlDataSource>

Open in new window

SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
genius
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Thanks..
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*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.