Solved

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

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 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

16 Experts available now in Live!

Get 1:1 Help Now