Filling a drop down list from db at IndexChanged

I am attempting to fill a drop down list (from a database) once I choose a value from another drop down list. For instance the first drop down list is named ddlYear. When I select the year I want the next list to fill with all the makes from that year. Here is the code I have so far.

VB Code:

Protected Sub ddlYear_SelectedIndexChanged( _
        ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles ddlYear.SelectedIndexChanged
 
        If IsPostBack = True Then
            Dim cnn As New OleDbConnection(obConn)
            Dim cmd As New OleDbCommand
            cmd = cnn.CreateCommand
            cmd.Connection = cnn
            Dim da As New OleDbDataAdapter
            Dim dsMake As New DataSet
            Try
                cnn.Open()
                cmd.CommandText = "Select Make From tblRecall Where Year = '" & _
                    ddlYear.Text & "'"
                da.SelectCommand = cmd
                da.Fill(dsMake, "Makes")
                ddlMake.DataSource = dsMake.Tables("Makes")
                ddlMake.DataMember = ("Makes")
            Catch ex As Exception
                WriteLine(ex.Message)
            Finally
                cnn.Close()
                da.Dispose()
            End Try
        End If
    End Sub

After I select the year nothing at all happens. What am I doing wrong?

First make sure if the second dropdown list is filled even if you don't make a selection in the first one (attempt to try to isolate the problem). Try filling up both dropdown lists on the page load.

What happens then?

Also make sure that when you fill in the year dropdown list (in this case the first one) that it is filled up only once. Something like...

VB Code:

If Not Page.IsPostBack Then
' fill up year dropdown list
End if

silentthread I tried isolating it by putting the code I posted behind a button click and hard coding the year to 2000 and neither made any difference. I am not getting any errors. It is just not loading the drop down list.

FE you are setting the datasource for the dropdownlist but not databinding it. All you need to add is bind your control to that data.

Code:

---

                ddlMake.DataSource = dsMake.Tables("Makes")
                ddlMake.DataMember = ("Makes")
                ddlMake.DataBind()

---

This is what I have and I am still not getting the drop down lis filled. No error message and I am doing this with a button (for testing purposes). The page does repost but the list stays empty.

VB Code:

Dim cnn As New OleDbConnection(obConn)
        Dim cmd As New OleDbCommand
        cmd = cnn.CreateCommand
        cmd.Connection = cnn
        Dim da As New OleDbDataAdapter
        Dim dsMake As New DataSet
        Try
            cnn.Open()
            cmd.CommandText = "Select Distinct Make From tblRecall Where Year = 2000"
            da.SelectCommand = cmd
            da.Fill(dsMake, "Makes")
            ddlMake.DataSource = dsMake.Tables("Makes")
            ddlMake.DataMember = ("Makes")
            ddlMake.DataBind()
        Catch ex As Exception
            'WriteLine(ex.Message)
        Finally
            cnn.Close()
            da.Dispose()
        End Try

The drop down looks like this:

VB Code:

<td style="width: 100px">
    <asp:DropDownList ID="ddlMake" runat="server" />
</td>

ah ok. I don't usually do it quite the same way as you as I tend to use DataReaders instead of DataAdapters.

So anyway, if DataMember does what I think it does you still aren't assigning text and data values to your dropdownlist. Here how I do it.

Code:

---

        this.ddlSector.DataSource = flaxwebnet.BusinessLogic.Sector.Combo();
        this.ddlSector.DataTextField = "Value";
        this.ddlSector.DataValueField = "ID";
        this.ddlSector.DataBind();

---

Thanks Fishcake your sample helped me get it right. I had two problems. The first being that I was using distinct in my SQL incorrectly and the next is that I was using the year as a number instead of text. Once I took that Try Catch out of the code it was made clear to me.

Thanks for giving me the code sample though because without it I wouldn't have continued to tweak this until I got it figured out.

One more question. I am now getting my dropdown lists to fill when I press the buttons but I want to make it fill when I change the value in the preceeding drop down list.

VB Code:

Protected Sub ddlYear_TextChanged( _
        ByVal sender As Object, _
        ByVal e As System.EventArgs) _
        Handles ddlYear.TextChanged
 
        FillMake()
 
    End Sub
    Private Sub FillMake()
        Dim cnn As New OleDbConnection(obConn)
      .....
    End Sub

Do I have to do something else to fire the event.

Set 'Autopostback=true' on your first dropdownlist and then place your code in the dropdownlists SelectedIndexChanged event.