Solved

Sorting a DataBound Drop-Down List

Posted on 2007-03-29
6
249 Views
Last Modified: 2012-06-22
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?
0
Comment
Question by:CMES-IT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 11

Accepted Solution

by:
TornadoV earned 500 total points
ID: 18818948
Is SortBy_SelectedIndexChanged the only place where you bind your PurposeCode dropdown list?
0
 

Author Comment

by:CMES-IT
ID: 18818996
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
 
LVL 11

Expert Comment

by:TornadoV
ID: 18819181
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:CMES-IT
ID: 18836098
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
 

Author Comment

by:CMES-IT
ID: 18836350
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
 

Author Comment

by:CMES-IT
ID: 18839451
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question