ASP.NET DropDownList + SQL Query not showing anything

Question

I have 5 dropdown lists and am trying to make it so if I choose a certain value on a certain dropdown list, it won't show that specific item on the following dropdown lists.

I am doing this through a specific SQL query on each dropdown list's data source, the code is in the following pastebin link because the StackOverflow textbox is buggy for me for some reason: http://pastebin.com/gDm8MPrL

I'm not exactly sure why, but it isn't displaying anything from the database. When I test a normal "SELECT *" query it works, so it isn't a connection problem.

Could anyone give me a hand?

Thanks!

PASTED CODE FROM PASTE BIN

<asp:DropDownList ID="ddlHero1" runat="server" DataSourceID="ddlDataSource0"                        DataTextField="ChampName" DataValueField="ChamadpName" AutoPostBack="True">
</asp:DropDownList>
<asp:SqlDataSource ID="ddlDataSource0" runat="server" ConnectionString="<%$ ConnectionStrings:mobamanagerConnectionString%>" SelectCommand="SELECT [ChampName] FROM [Champions] WHERE Champname NOT IN (
SELECT [ChampName] FROM [Champions] WHERE Champname=@ddlHero2 
UNION 
SELECT [ChampName] FROM [Champions] WHERE Champname=@ddlHero3
UNION
SELECT [ChampName] FROM [Champions] WHERE Champname=@ddlHero4
UNION
SELECT [ChampName] FROM [Champions] WHERE Champname=@ddlHero5)">
   <SelectParameters>
        <asp:ControlParameter ControlID="ddlHero2" Name="ddlHero2" 
                                                PropertyName="SelectedValue" />
        <asp:ControlParameter ControlID="ddlHero3" Name="ddlHero3" 
                                                PropertyName="SelectedValue" />
        <asp:ControlParameter ControlID="ddlHero4" Name="ddlHero4" 
                                                PropertyName="SelectedValue" />
        <asp:ControlParameter ControlID="ddlHero5" Name="ddlHero5" 
                                                PropertyName="SelectedValue" />
   </SelectParameters>
</asp:SqlDataSource>

This process is repeated another 4 times with different queries for each DDL, but the procedure is identical.

EDIT: Thanks for pasting the code in, mod!

**`"StackOverflow textbox is buggy"`**. Mentioned text is wrong. Since you have not formatted your code properly. That's reason you are not able to see it. — Siva Charan, May 26 '12 at 20:28
I can't use tabs for some reason and it isn't formatting properly when i use 4 spaces. Thanks for editing it anyway. — yotsubanned, May 26 '12 at 20:29
Try to doing code behind aspx page I faced same issue with Datasource control. Use Ajax Cascade Dropdown control. It's May be helpful to you. Because your sql query not properly execute here. Also AutoPostback missing here. — Saroop Trivedi, May 26 '12 at 20:35
And can I solve my problem with Ajax Cascade Dropdown Control? I just want a decent way to not let the user choose the same value in different dropdown lists. — yotsubanned, May 26 '12 at 20:38

score 0 · Accepted Answer · answered May 26 '12 at 21:18

For your case you can do it with or without Ajax, but essentially make all the lists AutoPostback="true" and enabled="false" except the first and then do something like the following: this sample code in VB

   Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If Not Page.IsPostBack Then
        'here load the first only
        'make the 2d enabled
    Else
        If ViewState("ddlHero1").ToString <> "" Then
            'get the value of the 1st from the viewstate
            'ddlHero1.selectedindex=cint(ViewState("ddlHero1").ToString)
            'make the 2nd enabled
            'bind the second
        End If
        If ViewState("ddlHero2").ToString <> "" Then
            'get the value from viewstate
            'ddlHero2.selectedindex=cint(ViewState("ddlHero2").ToString)
            'make the 3nd enabled
            'bind the 3rd
        End If
        If ViewState("ddlHero3").ToString <> "" Then
            'like the 2nd..etc
        End If
    End If
End Sub

on the SelectedIndexChanged event for each list save the selected value in the viewstate

Protected Sub ddlHero1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlHero1.SelectedIndexChanged

    ViewState("ddlHero1") = ddlHero1.SelectedIndex.ToString
    'and the same for the other lists

End Sub

Thanks a lot for the answer! What do you mean by "bind the second" and "bind the 3rd" though? Also, the dropdown lists are always enabled, what do you mean by "make the 2nd enabled"? — yotsubanned, May 27 '12 at 13:26
based on your question comments, If you'll not let the user to choose the same value in different dropdown lists, you either have to rebind the next ddl after emitting the selected item in the previous or to bind all the data for all ddl's on page is not postback and on ddlHero1_SelectedIndexChanged event you'll delete the selected item in ddlHero1 from other ddls, but the first method is better. since the second needs hard maintenance. — Alaa Alweish, May 28 '12 at 08:23
and if you want all the lists to be enabled on page load you'll have to bind all the data on Page load when page.IsNotPostBack — Alaa Alweish, May 28 '12 at 08:26

ASP.NET DropDownList + SQL Query not showing anything

PASTED CODE FROM PASTE BIN

1 Answers1