Solved

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

Posted on 2009-07-06
5
463 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

0
Comment
Question by:JOHNFROG
  • 3
  • 2
5 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24784744
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

0
 

Author Comment

by:JOHNFROG
ID: 24784834
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

0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24785056
Script which I gave you will work fine.
Just take care of single and double quotes to correct it out.
0
 

Author Closing Comment

by:JOHNFROG
ID: 31600131
genius
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24785132
Thanks..
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

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

12 Experts available now in Live!

Get 1:1 Help Now