?
Solved

Sorting a DataBound Drop-Down List

Posted on 2007-03-29
6
Medium Priority
?
250 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 1500 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

752 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