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?
Re: Filling a drop down list from db at IndexChanged
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
Re: Filling a drop down list from db at IndexChanged
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.
Re: Filling a drop down list from db at IndexChanged
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()
Re: Filling a drop down list from db at IndexChanged
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>
Re: Filling a drop down list from db at IndexChanged
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();
Re: Filling a drop down list from db at IndexChanged
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.
Re: Filling a drop down list from db at IndexChanged
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.
Re: Filling a drop down list from db at IndexChanged
Set 'Autopostback=true' on your first dropdownlist and then place your code in the dropdownlists SelectedIndexChanged event.