Populate DropdownList based upon other DropDownList VB

Question

I have found a couple of examples on the internet to do this but really struggling to get it working in VB. (Tried a converter but had mixed results)

I need the selection options of a Dropdownlist to be populated based upon the differing values in the first dropdown list.

Can anyone help with a releativley simple example in VB? Not fussed if the values are "hard coded" in the script. Or a SQL bit that pulls the data from a table

Thanks in advance!

Solution 1

The way it is done is to populate second dropdown in SelectedIndexChanged event of the first dropdown

Example:

Protected Sub ddlCountry_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
    Dim CountryID As Integer = Convert.ToInt32(ddlCountry.SelectedValue.ToString())
    FillStates(CountryID)
End Sub


Private Sub FillStates(ByVal countryID As Integer)
  Dim strConn As String = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ConnectionString
  Dim con As New SqlConnection(strConn)
  Dim cmd As New SqlCommand()
  cmd.Connection = con
  cmd.CommandType = CommandType.Text
  cmd.CommandText = "Select StateID, State from State where CountryID =@CountryID"
  cmd.Parameters.AddWithValue("@CountryID", countryID)
  Dim objDs As New DataSet()
  Dim dAdapter As New SqlDataAdapter()
  dAdapter.SelectCommand = cmd
  con.Open()
  dAdapter.Fill(objDs)
  con.Close()
  If objDs.Tables(0).Rows.Count > 0 Then
    ddlState.DataSource = objDs.Tables(0)
    ddlState.DataTextField = "State"
    ddlState.DataValueField = "StateID"
    ddlState.DataBind()
    ddlState.Items.Insert(0, "--Select--")
  Else
    lblMsg.Text = "No states found"
  End If
 End Sub

With html source as so:

   <asp:DropDownList ID="ddlState" runat="server" AutoPostBack="True">
  </asp:DropDownList>

  <asp:DropDownList ID="ddlCountry" runat="server" 
  AutoPostBack="True" OnSelectedIndexChanged="ddlCountry_SelectedIndexChanged">
 </asp:DropDownList>

Solution 2

You could accomplish this declaratively in the ASPX page like this:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnString %>" SelectCommand="SELECT id, name FROM planets"></asp:SqlDataSource>
<asp:DropDownList ID="ddlPlanets" AutoPostBack="true" DataTextField="name" DataValueField="id" DataSourceID="SqlDataSource1" runat="server" AppendDataBoundItems="true" />

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:myConnString %>" SelectCommand="SELECT planetid, name FROM moons" FilterExpression="planetid = '{0}'">
    <FilterParameters>
        <asp:ControlParameter Name="planetid" ControlID="ddlPlanets" PropertyName="SelectedValue" />
    </FilterParameters>
</asp:SqlDataSource>      
<asp:DropDownList ID="ddlMoons" DataTextField="name" DataValueField="planetid" DataSourceID="SqlDataSource2" runat="server" />