Using SqlParameter to create Order By clause

Viewed 19427

I am trying to move all of my references to variables in SQL statements to the SqlParameter class however for some reason this query fails.

string orderBy = Request.QueryString["OrderBy"];
//Fix up the get vars
if (orderBy == null)
    orderBy = "name ASC";

string selectCommand = "SELECT cat_id AS id, cat_name AS name FROM table_name ORDER BY @OrderBy";
SqlCommand cmd = new SqlCommand(selectCommand, dataConnection);
cmd.Parameters.Add(new SqlParameter("@OrderBy", orderBy));

//Create the SQLDataAdapter instance
SqlDataAdapter dataCommand = new SqlDataAdapter(cmd);

//Create the DataSet instance
DataSet ds = new DataSet();
//Get data from a server and fill the DataSet  
dataCommand.Fill(ds);

Here is the error

System.Data.SqlClient.SqlException: 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.

It fails on this line.

dataCommand.Fill(ds);
5 Answers
Related