JOHNFROG
asked on
Gridview SqlDataSource toggle between Ascending and Descding order using a control param
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
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>
ASKER
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
"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>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
genius
Thanks..
Hope this helps
Open in new window