Link to home
Start Free TrialLog in
Avatar of Curriculum
CurriculumFlag for United States of America

asked on

using parameters in SQLDataSource ORDER BY

Well it seemed like it should work:
Visual Studio 2005, gridview, with three dropdownlists above the gridview allowing the user to select 1, 2, or 3 fields that would be fed into the ORDER BY clause of the SQLDataSource.

SQLDataSource has (simplified for this question):
Select fname, mname, lname,gender from people ORDER BY @sort1, @sort2, @sort3

and
<SelectParameters>
....
                <asp:ControlParameter ControlID="ddlSort1" DefaultValue="'' Name="sort1"
                    PropertyName="SelectedValue" />
etc etc
</SelectParameters>

Error response back is:
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.

Any ideas on a way around this ? I saw one reference that suggests coding a CASE statment within the SelectCommand.
thanks in advance
ASKER CERTIFIED SOLUTION
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Curriculum

ASKER

Sounds promising. I've got a rather complicated set of ControlParameters in my <SelectParameters> block for the SQLDataSource's <SelectCommand>

Should the Me.Init SelectCommand override the SelectCommand contained within the SQLDataSource ? What I have as the default ORDER BY in the SQLDataSource's <SelectCommand> still determines what is displayed in the gridview after I introduced the suggested Page_Init sub.

If I need to omit the <SelectCommand> for the SQLDataSource, then can I assume that I'd need to re-code all the <asp:ControlParameter...> elements into the Sub, something like:
SQLDataSource1.SelectCommand.[something relating to ControlParameter].  
however, I don't see anything obvious in my choices for the [something relating to ControlParameter.

tnx

You would be building the ORDER BY manually, so you wouldn't need any ControlParameter entries.  The Init event should occur before the SqlDataSource gets used, so it should override the default SelectCommand text.

bob
I have about 30 ControlParameters needed for the query, outside of the ORDER BY needs. I'm filtering the selection based on values in textboxes and ddl's that are provided by the user.

So my WHERE clause has a bunch of segments like:
AND (@lname = '' OR lname LIKE @lname + '%')

... and matching up with that in the <SelectParameters> section are 30 or so entries like:
 <asp:ControlParameter ControlID="tb_lname" Name="lname" PropertyName="Text"  Type="String" ConvertEmptyStringToNull="False" Size="25" />

which get their values from what the user types (or doesn't type) into a bunch of potential filters on the data.

So the question would be then, how to bring all the <asp:ControlParameter material into the Me.Init sub. Sounds like the select query would need to directly reference the value of the textbox and ddl's rather than name a parameter.

So rather than
WHERE
....
AND (@lname = '' OR lname LIKE @lname + '%')


I would instead use:
WHERE
...
AND (Me.tb_lname.Text = '' OR lname LIKE Me.tb_lname.Text+ '%')

I"ll try this idea and see how it goes.  

Since you oversimplified your command text, then I would suggest that you keep the ControlParameter entries for the WHERE clause, and include that in the Page_Init calls.

Bob
HI, sorry, I'm not following. What does:
...'and include that in the Page_Init calls'

mean ? I may need an example of how to refer to the ControlParameter entries from within the Page_Init calls, if I am following your idea.

thanks in advance.
1) Leave the ControlParameter definitions in the HTML view (use @ in the parameter name):

<asp:ControlParameter ControlID="tb_lname" Name="@lname" PropertyName="Text"  Type="String" ConvertEmptyStringToNull="False" Size="25" />

2) Include the WHERE clause when you are building the dynamic SQL:

Example:

Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
    Dim orderBy As String = sort1.SelectedValue & ", " & sort2.SelectedValue & ", " & sort3.SelectedValue
    SqlDataSource1.SelectCommand = "Select fname, mname, lname,gender from people ORDER BY " & orderBy & " WHERE lname=@lname"
  End Sub

Bob
OK, thanks that clarifies things. I removed the <SelectCommand> but kept the <asp:ControlParameter..> entries in the HTML.

I'm probably 90% there but not getting the ORDER BY to apply to the query.  I changed the Me.Init to GridView1.DataBinding because the Me.Init wasn't firing each time the user changed the dropdown list containing a potential sort field. I've but in a diagnostic to verify that the orderBy variable is changing each time I select one of the three sort fields, however, the GridView is not changing its sorting even though my other filter parameters are filtering correctly. So this appears to indicate that the new value for SqlDataSource.SelectCommand is not setting set, or taking hold.

By the way, I have also added this line to the Sub:

     orderBy = ddlSort1.SelectedValue & ", " & ddlSort2.SelectedValue & ", " & ddlSort3.SelectedValue

so that it gets set new each time the user selects another value from a DDL.

Did you set AutoPostBack for each of the 3 DropDownList controls?

Bob
Yes, they are set to AutoPost, and I'm verifying all this by having a label on the HTML page display the orderBy value, and it's getting properly set each time I change a ddl, so it's looking like
fname, lname, gender
or
schoolyear,lname,gender

etc.
The sort of the GridView is staying with the initial orderBy unfortunately.  
Thanks Bob, I'll make an annotation of where I'm at but your suggestion was the nucleus of the solution.
Steve
I have now successfully got the gridview sorting on the three dropdownlist values, by setting the SqlDataSource.SelectCommand via a different trigger. I've got it working temporarily with TextChanged property of a sort dropdown list, but that's not a permanent solution. Shouldn't be long before I get the right trigger going, but for others looking for a solution, the Me.Init and GridView1.DataBinding didn't produce a change to the SQLDataSource.SelectCommand - at least in my environment.

Also a note to others trying this - you'll need to add code to disallow your sort dropdownlists from containing the same value, as that'll throw an error in the SQL query - it'll report that you can't have more than one column of the same name.