I'm attempting to move all my references to variables in SQL claims towards the SqlParameter class however for whatever reason this question 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);

This is actually the error

System.Data.SqlClient.SqlException: The Choose item recognized through the ORDER BY # 1 consists of a flexible included in the expression determining a column position. Variables are just permitted when ordering by a manifestation referencing a column title.

It fails about this line.

dataCommand.Fill(ds);

You actually have three options.

1) Make use of a dataview to buy the end result set

2) Knowing the posts that may be purchased you can look at for that string after which use then choose an order. e.g.

For instance this works

DECLARE @orderby varchar(255)
SET @orderby = 'Name ASC'

SELECT [Your Column here ]FROM sys.tables 
ORDER BY    
   case WHEN @orderby = 'Name ASC' Then name ELSE null END ASC,
   case WHEN @orderby = 'Name DESC' Then name ELSE null END DESC,
   CASE WHEN @orderby = 'Object_id ASC' then object_id ELSE null END,
   CASE WHEN @orderby = 'Object_id DESC' then object_id ELSE null END

3) The ultimate choice is to complete just like #2 however in your C# code. Make certain you don't just add an order BY clause from user input because that'll be prone to SQL injection.

This really is safe since the OrderBy Url parameter "Name Desc; DROP table Users"only will be overlooked

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

if (orderby == "name Desc")
{
     SafeOrderBy == "name Desc"
}


string selectCommand = "SELECT cat_id AS id, cat_name AS name FROM table_name ORDER BY "
selectCommand  += SafeOrderBy ;

Using SqlCommand is the best way to prevent from sql injection. The right path of altering from the order by is equivalent to using sql injection within this context therefore it shouldnt be permitted - params are utilized because the constants, can't be utilized for column or table names.

u do not have to concatenate content of sortBy only use it as being enum and based on its value concatenate something you are sure that's safe. Such as this:

If(orderBy == "some_column")
{
   selectColumn += "someColumn";
}
...

I do not think ordering with a parameter is permitted. If guess what happens posts you've why must you make use of a param?

I discovered a good example how to get this done here

you are able to define different sort orders inside a Situation-structure and execute them appropriatly for your variable value:

  SELECT CompanyName,
         ContactName,
         ContactTitle

    FROM Customers

ORDER BY CASE WHEN @SortOrder = 1 THEN CompanyName
              WHEN @SortOrder = 2 THEN ContactName
         ELSE ContactTitle

i did not test drive it myself however it perform. You are able to try it out. An apparent disadvantage is you need to code all of the order-by claims.

You are just concatenating strings. A less complicated approach could be:

string orderBy = "name ASC";
string selectCommand = "SELECT cat_id AS id, cat_name AS name FROM table_name ORDER BY " + orderBy;

I am presuming you are carrying this out whatsoever because you are letting the caller decide sort area/direction, hence orderBy separated.

Parameters, because the error message kind of obliquely shows, could be utilized in a WHERE clause, e.g. WHERE someColumn = @someValue etc.