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:
<asp:DropDownList 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>
</asp:DropDownList>

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**@emailme.com> wrote in message
news:Ou**************@TK2MSFTNGP11.phx.gbl...

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:
<asp:DropDownList 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>
</asp:DropDownList>

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.

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

"CK" <c_**********@hotmail.com> wrote in message
news:_D******************@newssvr13.news.prodigy.c om...

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**@emailme.com> wrote in message
news:Ou**************@TK2MSFTNGP11.phx.gbl...

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:
<asp:DropDownList 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>
</asp:DropDownList>

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.

[VB Programmer]

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

"CK" <c_**********@hotmail.com> wrote in message
news:_D******************@newssvr13.news.prodigy.c om...

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**@emailme.com> wrote in message
news:Ou**************@TK2MSFTNGP11.phx.gbl...

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:
<asp:DropDownList 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>
</asp:DropDownList>

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]

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="<%$

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 CASE @SortBy

WHEN 'Most Viewed' THEN TimesViewed
WHEN 'Newest' THEN VideoID
WHEN 'UserName' THEN UserName
END
">

CK

"VB Programmer" <do**@emailme.com> wrote in message
news:uP**************@TK2MSFTNGP12.phx.gbl... Is there a way to change the SelectCommand SQL statement in code?

"CK" <c_**********@hotmail.com> wrote in message
news:_D******************@newssvr13.news.prodigy.c om...

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**@emailme.com> wrote in message
news:Ou**************@TK2MSFTNGP11.phx.gbl...

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:
<asp:DropDownList 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>
</asp:DropDownList>

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.