Curriculum
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="SelectedValu e" />
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
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="SelectedValu
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
bob
ASKER
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.
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="
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
Bob
ASKER
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.
...'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.SelectComma nd = "Select fname, mname, lname,gender from people ORDER BY " & orderBy & " WHERE lname=@lname"
End Sub
Bob
<asp:ControlParameter ControlID="tb_lname" Name="@lname" PropertyName="Text" Type="String" ConvertEmptyStringToNull="
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.SelectComma
End Sub
Bob
ASKER
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.SelectComman d 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.
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.SelectComman
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
Bob
ASKER
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.
fname, lname, gender
or
schoolyear,lname,gender
etc.
The sort of the GridView is staying with the initial orderBy unfortunately.
ASKER
Thanks Bob, I'll make an annotation of where I'm at but your suggestion was the nucleus of the solution.
Steve
Steve
ASKER
I have now successfully got the gridview sorting on the three dropdownlist values, by setting the SqlDataSource.SelectComman d 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.SelectComman d - 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.
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.
ASKER
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.SelectComma
however, I don't see anything obvious in my choices for the [something relating to ControlParameter.
tnx