SQL Select Statement with order by as parameter

Question

Having trouble getting this code to print what I need it to print. I have a Drop Down list that lets the user select what they want the table to be sorted as. Im getting that Value and binding it to the Parameter '@param'. It is not putting the items of the table in the correct order.

NOTE: when I change the 'string sql' to a value that is in my Drop down list it works fine.

string sql = "Select * from yeet order by @param desc;";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Prepare();
cmd.Parameters.AddWithValue("@param", DropTopWop.SelectedValue.ToString());
MySqlDataReader reader = cmd.ExecuteReader();

So, how did you fill in that list of values in you DropDownList? — Jimi, Dec 08 '17 at 21:47
Parameters cannot be used for that. Parameters are used to represent a value in a where statement or values in insert/update/delete queries. If you are absolutely sure that your user cannot TYPE the value for the field but just select one of predefined fields name then you can use string concatenation — Steve, Dec 08 '17 at 21:47
@Steve So what else can I use instead of Parameters for a Select Statement — JOhn, Dec 08 '17 at 21:52
As I have said. You should be absolutely certain that your user cannot type the value for your Order By statement but just select an item from a readonly dropdown. Then you just concatenate the SelectedValue inside the sql string — Steve, Dec 08 '17 at 21:53
@JOhn Good. Sometimes someone fills the list manually, so .SelectedValue is null. You already have the answer. — Jimi, Dec 08 '17 at 21:56

CodingYoshi · Accepted Answer · 2017-12-08T22:02:33.237

You need to do it like this instead:

order by    
    case @param
        when 'PutYourColumNameHere' then PutYourColumNameHere
        when 'AnotherColumn' then AnotherColumn--, and so on                    
    end desc

So for example if your table players has 2 columns named Age and Name, then it will look like this:

Select * from players 
order by 
    case @param 
        when 'Age' then Age
        when 'Name' then Name               
    end desc;

SQL Select Statement with order by as parameter

1 Answers1