[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2141
  • Last Modified:

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
0
Curriculum
Asked:
Curriculum
  • 7
  • 5
1 Solution
 
Bob LearnedCommented:
Here is one possibility (untested):

Bob
  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
  End Sub

Open in new window

0
 
CurriculumAuthor Commented:
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

0
 
Bob LearnedCommented:
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
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
CurriculumAuthor Commented:
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.  

0
 
Bob LearnedCommented:
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
0
 
CurriculumAuthor Commented:
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.
0
 
Bob LearnedCommented:
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
0
 
CurriculumAuthor Commented:
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.

0
 
Bob LearnedCommented:
Did you set AutoPostBack for each of the 3 DropDownList controls?

Bob
0
 
CurriculumAuthor Commented:
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.  
0
 
CurriculumAuthor Commented:
Thanks Bob, I'll make an annotation of where I'm at but your suggestion was the nucleus of the solution.
Steve
0
 
CurriculumAuthor Commented:
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.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now