Solved

Sorting a DataBound Drop-Down List

Posted on 2007-03-29
6
243 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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