How use dropdown to fill ORDER BY of SqlData Source

[VB Programmer]

I am trying to use a dropdownlist to fill in the ORDER BY of a
SqlDataSource. Here's part of my HTML...

<asp:SqlDataSource ID="VideoList" runat="server" ConnectionString="<%$
ConnectionStrings:MyMainDatabase %>"
SelectCommand="SELECT aspnet_Users.UserName AS Username,
VideoCategory.VideoCategory, Videos.VideoId AS VideoId,
Videos.VideoDescription, Videos.VideoCaption, Videos.VideoPathUrl,
Videos.IsRacerProfileVideo, Videos.VideoCategoryId, Videos.TimesViewed AS
TimesViewed FROM Videos INNER JOIN VideoCategory ON Videos.VideoCategoryId =
VideoCategory.VideoCategoryId INNER JOIN aspnet_Users ON Videos.UserId =
aspnet_Users.UserId WHERE (Videos.IsRacerProfileVideo = 0) AND
(Videos.VideoCategoryId = @VideoCategoryId) ORDER BY @SortBy">
<SelectParameters>
<asp:ControlParameter ControlID="ddlCategory"
Name="VideoCategoryId" PropertyName="SelectedValue" DefaultValue="" />
<asp:ControlParameter ControlID="ddlSort" DefaultValue=""
Name="SortBy" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>

The dropdownlist is as follows:
<aspropDownList ID="ddlSort" runat="server" AutoPostBack="True">
<asp:ListItem Selected="True" Value="TimesViewed">Most
Viewed</asp:ListItem>
<asp:ListItem Value="VideoId">Newest</asp:ListItem>
<asp:ListItem Value="Username">Username</asp:ListItem>
</aspropDownList>

Any ideas why this isn't working? Here's the error I'm getting...
The SELECT item identified by the ORDER BY number 1 contains a variable as
part of the expression identifying a column position. Variables are only
allowed when ordering by an expression referencing a column name.

 

[CK]

SqlServer does not like variables in the ORDER BY Clause
ReWrite your SQL using a case statement similiar to this

DECLARE @SortBy varchar(30)
SET @SortBy = 'SomeColumn' *****I think you already decalre this in your
control

SELECT * FROM SomeTable
ORDER BY
CASE @SortBy
WHEN 'SomeColumn' THEN SomeColumn
WHEN 'OtherColumn' THEN OtherColumn
WHEN 'xColumn' THEN xColumn
END

HTH,
CK

 

[VB Programmer]

Do I have to create a new Stored Proc for this? I can't seem to use this as
SQL in the SqlDataSource SELECT statement...

 

[VB Programmer]

Is there a way to change the SelectCommand SQL statement in code?

 

[CK]

See if that works for you.
I added the CASE clause to account for all the values in your drop down
list.

<asp:SqlDataSource ID="VideoList" runat="server" ConnectionString="<%$WHEN 'Most Viewed' THEN TimesViewed
WHEN 'Newest' THEN VideoID
WHEN 'UserName' THEN UserName
END
">

CK