Solved

Sorting a DataBound Drop-Down List

Posted on 2007-03-29
6
245 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
  • 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

808 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