dynamically populate a third dropdownlist by two independent (not cascading) dropdown lists

Question

Hi

I have two dropdown lists (one dynamically populated, the other not) that determine the values of a third dropdown list. The idea is that the first ddl1 is compulsory and ddl2 is optional. How can I get the null value (is not value is selected) of ddl2 once ddl1 value is selected. Thanks!

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:conn %>" SelectCommand="SELECT Category FROM Categories"></asp:SqlDataSource>

  <asp:DropDownList ID="DropDownList1" AutoPostBack="True" runat="server" DataSourceID="SqlDataSource1" DataTextField="Category" DataValueField="Category" AppendDataBoundItems="True" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
   <asp:ListItem Text="Select category" Value=""/>
   </asp:DropDownList>

   <asp:DropDownList ID="DropDownList2" runat="server">
   <asp:ListItem Text="All types" Value="" />
         <asp:ListItem Value="Policy">Policy</asp:ListItem>
         <asp:ListItem Value="Form">Form</asp:ListItem>
         <asp:ListItem Value="Other">Other</asp:ListItem>
          </asp:DropDownList>

     <asp:DropDownList ID="DropDownList3" runat="server" DataSourceID="SqlDataSource3" DataTextField="DocName" DataValueField="DocID" AppendDataBoundItems="True">
   <asp:ListItem Text="Select document" Value=""/>
   </asp:DropDownList>

code behind:

    Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)

        DropDownList3.Items.Clear()
        DropDownList3.Items.Add(New ListItem("Select document", ""))

      If (DropDownList1.SelectedIndex <> 0 Or DropDownList1.SelectedIndex > 0) Then
        SqlDataSource3.SelectCommand = "SELECT DocID, DocName, Category, DocType FROM Doc_LibraryTable WHERE (Category = @Cat) AND (DocType = @DocType OR @DocType IS NULL)"            
        Dim controlDdl1 As ControlParameter = New ControlParameter
        controlDdl1.ControlID = "DropDownList1"
        controlDdl1.Name = "Cat"
        controlDdl1.Type = TypeCode.String
        controlDdl1.PropertyName = "SelectedValue"
        SqlDataSource3.SelectParameters.Clear()
        SqlDataSource3.SelectParameters.Add(controlDdl1)

        Dim controlDdl2 As ControlParameter = New ControlParameter
        controlDdl2.ControlID = "DropDownList2"
        controlDdl2.Name = "DocType"
        controlDdl2.Type = TypeCode.String
        controlDdl2.PropertyName = "SelectedValue"
        SqlDataSource3.SelectParameters.Clear()
        SqlDataSource3.SelectParameters.Add(controlDdl2)
        SqlDataSource3.SelectParameters("DocType").DefaultValue = ""


    End If
End Sub

Answer 1

Lots of things to change.

• Both DropDownList1 & DropDownList2 should be autopostback=true, and the event handler should be used for both. As you have it, the user would have to select DropDownList2 and then DropDownList1 for this to work. We'd like them to be able to select them in either order.

• Next the line DropDownList1.SelectedIndex <> 0 Or DropDownList1.SelectedIndex > 0 is redundant (Any value that would make the second half true would also make the first half true.) If DropDownList1.SelectedIndex <> 0 Then is sufficent.

• Do not clear the parameter list twice -- you're erasing your first parameter.

• Finally, the part you wanted. There is no need to use ControlParameter in code. It was intended to be used in cases where you wanted to set the relationship in markup. If you are using code, you can specify the values directly:

  SqlDataSource3.SelectCommand = "SELECT DocID, DocName, Category, DocType " +_
                                  "FROM Doc_LibraryTable "+_
                                  "WHERE (Category = @Cat) "+_
                                  "AND (DocType = @DocType "+_
                                  "OR @DocType IS NULL)"
  
  
  SqlDataSource3.SelectParameters.Clear() 
  SqlDataSource3.SelectParameters.Add("Cat", TypeCode.String, _ 
           DropDownList1.SelectedValue) 
  SqlDataSource3.SelectParameters.Add("DocType", TypeCode.String, _ 
           DropDownList2.SelectedValue ?? "") 
  

(P.S., I'm a C# guy, so with luck, I've got the line continuations right...)