Sorting a DataBound Drop-Down List

Hi,

I have an ASP.NET page with two dropdown lists. The first list is a set of sort conditions, and the second list is a set of data. I'd like the user to be able to select a sort condition from the first drop-down list, have the page do a postback, and then reload the second list using the sort condition.

I've got all this programmed in, but for some reason, the list isn't sorting. Let me show you what I have:

                <asp:DropDownList ID="SortBy" runat="server" AutoPostBack="True">
                    <asp:ListItem Value="Title" Text="Sort List by..." />
                    <asp:ListItem Value="Title" Text="Title" />
                    <asp:ListItem Value="Code" Text="Code" />
                    <asp:ListItem Value="Purpose" Text="Purpose" />
                </asp:DropDownList>
                <asp:DropDownList ID="PurposeCode" runat="server" DataSourceID="dsPurposeList" DataTextField="Purpose" DataValueField="Code" />
     <asp:SqlDataSource ID="dsPurposeList" runat="server" ConnectionString="<%$ ConnectionStrings:Core %>"
        SelectCommand="GetPurposeList" SelectCommandType="StoredProcedure">
        <SelectParameters>
            <asp:Parameter Name="SortBy" DefaultValue="Title" />
        </SelectParameters>
     </asp:SqlDataSource>


In the code-behind, I have this:

    Protected Sub SortBy_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles SortBy.SelectedIndexChanged
        Select Case SortBy.SelectedValue
            Case "Title"
                dsPurposeList.SelectParameters.Item("SortBy").DefaultValue = "Title"
            Case "Code"
                dsPurposeList.SelectParameters.Item("SortBy").DefaultValue = "Code"
            Case "Purpose"
                dsPurposeList.SelectParameters.Item("SortBy").DefaultValue = "Purpose"
        End Select
        PurposeCode.DataBind()
    End Sub


I'm using SQL Server 2005, so I've run traces, and I can see that the correct parameter is being passed when I select a sort parameter. I've also tested the SQL using all sort parameters, and the result sets are correct.

It's like the page is making the correct call to the database, but never updating the content of the drop-down list. What am I doing wrong?
CMES-ITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TornadoVCommented:
Is SortBy_SelectedIndexChanged the only place where you bind your PurposeCode dropdown list?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CMES-ITAuthor Commented:
No, I'm not binding it in the Code-Behind, except the code you see during SelectedIndexChanged. I only threw that PurposeCode.DataBind() in for good measure to see if it would do anything. It doesn't. My expectation was that I would set the Parameter's value in the Code-Behind, and then when the page loads, the drop-down list would populate based on the asp:SqlDataSource and the new parameter.

By default, when the page loads the drop-down is bound through the .aspx code using the asp:SqlDataSource. I'm not doing anything interesting in the code-behind.
0
TornadoVCommented:
Have you tried using SortParameterName?

Select Case SortBy.SelectedValue
            Case "Title"
                dsPurposeList.SortParameterName = "Title"
            Case "Code"
                dsPurposeList.SortParameterName = "Code"
            Case "Purpose"
                dsPurposeList.SortParameterName = "Purpose"
        End Select
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

CMES-ITAuthor Commented:
Unfortunately, that doesn't work. It ends up sending this to the database:

exec GetPurposeList @SortBy=N'Title',@Title=N''
exec GetPurposeList @SortBy=N'Title',@Code=N''
exec GetPurposeList @SortBy=N'Title',@Purpose=N''

It just append the sort parameter name value to the end of the SQL as an empty parameter that my Stored Procedure isn't looking for.
0
CMES-ITAuthor Commented:
I just tried this all a different way:

     <asp:SqlDataSource ID="dsPurposeList" runat="server" ConnectionString="<%$ ConnectionStrings:Core %>"
        SelectCommand="Accounting.GetPurposeList" SelectCommandType="StoredProcedure" />

Then in the code-behind:

    Private prmSortBy As New SqlParameter("SortBy", SqlDbType.VarChar, 24)

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not IsPostBack Then
            dsPurposeList.SelectParameters.Add(prmSortBy.ToString, "Title")
        Else
            dsPurposeList.SelectParameters.Clear()
        End If
    End Sub

    Protected Sub SortBy_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles SortBy.SelectedIndexChanged
        Select Case SortBy.SelectedValue
            Case "Title"
                dsPurposeList.SelectParameters.Add(prmSortBy.ToString, "Title")
            Case "Code"
                dsPurposeList.SelectParameters.Add(prmSortBy.ToString, "Code")
            Case "Purpose"
                dsPurposeList.SelectParameters.Add(prmSortBy.ToString, "Purpose")
        End Select
        dsPurposeList.DataBind()
        PurposeCode.DataBind()
    End Sub


And this all didn't work either. So, it's not a matter of getting the right SQL to the database, or the database sending the correct result... I've checked all that a dozen times.

The problem seems to be that the Drop-Down won't update it's data once it's set initially.
0
CMES-ITAuthor Commented:
Ok,

Well, I had to get a solution to this, so I solved it all in the Code-Behind. It's more complicated than what I wanted to do, but it works.

Here's the code for anyone interested:

Page:
<asp:DropDownList ID="PurposeCode" runat="server" />

Code-Behind:
        '*** Here, we check to see if the form has Posted Back or not. If it hasn't, the default
        '*** sort criteria for the Purpose List drop-down box is "Title". If it is a
        '*** PostBack, then the Sort Criteria is whatever the value of the selected SortBy
        '*** list is. After we set this Parameter, we can populate all the drop-downs.
        If Not IsPostBack Then
            dsPurposeList.SelectParameters.Item("SortBy").DefaultValue = "Title"
        Else
            Select Case SortBy.SelectedValue
                Case "Title"
                    dsPurposeList.SelectParameters.Item("SortBy").DefaultValue = "Title"
                Case "Code"
                    dsPurposeList.SelectParameters.Item("SortBy").DefaultValue = "Code"
                Case "Purpose"
                    dsPurposeList.SelectParameters.Item("SortBy").DefaultValue = "Purpose"
            End Select
        End If

        Dim theItem As New ListItem
        theItem.Value = ""
        theItem.Text = "Please Select the Purpose"
        theItem.Selected = "True"

        PurposeCode.Items.Clear()
        PurposeCode.Items.Add(theItem)
        PurposeCode.DataSourceID = "dsPurposeList"
        PurposeCode.DataTextField = "PurposeCode"
        PurposeCode.DataValueField = "AccountCode"
        PurposeCode.AppendDataBoundItems = "True"
        PurposeCode.DataBind()
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.