Population of Three Dependant Dropdownlists

[bubberz]

I have three dropdownlists. After I pick from the first list, the second list has it's possible records filtered via the first dropdownlist's DataValueField. Once I select from the second ddl, the third ddl has it's records even further filtered.

I can show the DataFieldValue in a label right under the first ddl which is how I want to make the decision in my "Where" clause which populates my second ddl. The propblem is I can figure out how to get the string value of the label for the first ddl or the selected value of the first ddl to populate my SQL string's where clause. I am using DataAdapters and DataSets. I know I should probably be using DataReaders instead.

I've been trying to mess with the Page_Load handler, and using If (Not Page.IsPostBack)....Else...End IF, but this isn't working as well.

Thanks!

bubberz

 

[RTomes]

dim sState as string
dim sCounty as string
dim sCity as string

if ddlState.SelectedIndex>-1 then
sState=ddlState.SelectedValue

End if
if ddlCounty.SelectedIndex>-1 then
sState=ddlCounty.SelectedValue

End if
if ddlCity.SelectedIndex>-1 then
sState=ddlCity.SelectedValue

End if

dim sqlCounties as stirng="select * from tblCounties where county is not null "
If not sState=nothing then
sqlCounties = sqlCounties & " and state='" & sState & "' "
end if
If not sCounty=nothing then
sqlCounties = sqlCounties & " and county ='" & sCounty & "' "
end if

'Use sqlCounties to bind the counties dropdownlist
'If you use Autopostback in the listboxes then do your databinding in the Page_Prerender not the page load.

 

[bubberz]

Thanks RTomes!

I'll try it now and let 'cha know!

bubberz

 

[bubberz]

TRomes,

I can't dimention any type of reader or dropdownlist in the Page_Prerender handler.

Should I try it in the Page_Load like:

If (Not Page.IsPostBack)...then
...
Else
(insert code here)..
End If

 

[bubberz]

TRomes,

Sorry..missed my Imports...

I'll keep ya posted!

Thanks!

bubberz

 

[bubberz]

TRomes,

I'm can't get my second dropdownlist to populate. I do have my first dropdownlist to "AutoPostBack".
I've got the following code in my lblDivisionCode_load subroutine (this label holds the key value in my first dropdown),:

Con1.Open()
Dim sqlGrp As String
sqlGrp = "SELECT GC+' - '+GD as Expr2, GC, G, DivisionCode FROM [Div] WHERE GD = " & "'" & ddlDivision.SelectedValue.ToString() & "'"

Dim myRDR As OleDbDataReader
Dim cmdGrp As New OleDbCommand(sqlGrp, Con1)
ddlGroup.DataSource = cmdGrp.ExecuteReader()
ddlGroup.DataBind()
'show SQL in label
lblSelect2.Text = sqlGrp.ToString()

 

[checkai]

you would want the code under the _SelectedIndexChanged event of the AutoPostBack dropdown list...

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[RTomes]

You can use Autopost for the first two of the dropdowns.
The third one does not need it.
Take the code above and put it in the Page_Load or Page_Prerender. It just get less confusing in the Page_Prerender in case you are makeing other changes to the data then binding on PreRender is easier to maintain.

dim sState as string
dim sCounty as string
dim sCity as string

if ddlState.SelectedIndex>-1 then
sState=ddlState.SelectedValue

End if
if ddlCounty.SelectedIndex>-1 then
sState=ddlCounty.SelectedValue

End if
if ddlCity.SelectedIndex>-1 then
sState=ddlCity.SelectedValue

End if

dim sqlCounties as stirng="select * from tblCounties where county is not null "
If not sState=nothing then
sqlCounties = sqlCounties & " and state='" & sState & "' "
end if
If not sCounty=nothing then
sqlCounties = sqlCounties & " and county ='" & sCounty & "' "
end if


'Use sqlCities to bind the cities dropdownlist

dim sqlCities as stirng="select * from tblCities where county is not null "
If not sState=nothing then
sqlCities = sqlCities & " and state='" & sState & "' "
end if
If not sCounty=nothing then
sqlCities = sqlCities & " and county ='" & sCounty & "' "
end if
If not sCity=nothing then
sqlCities = sqlCities & " and city ='" & sCity& "' "
end if

sqlCities = sqlCities & " order by city"

Dim tbl as new DataTable
dim cmd as new OleDbCommand(sqlCities,MyConnectionObject)
dim DataAdapterCites new OleDBDataAdapter(cmd)
DataAdapterCites.Fill(tbl)
ddlCites.DataTextField="city"
ddlCites.DataValueField="city"
ddlCites.datasource=tbl
ddlCites.databind

 

[bubberz]

TRomes,

When I do AutoPostBack=True for the second dropdownlist, it reverts back to the first record in the list even if I select the third, fourth, or fifth index. I must have the databind() wrong somewhere.

I bind the second list in:
Sub Page_Load
If (Not Page.IsPostBack)
....fill first ddl and show it's value in a label (lblDiv)underneath
fill second ddl and use lblDiv in Where clause
Else
fill second ddl
End If
End Sub

 

[RTomes]

Regardless if it is states,counties,cities or divisions,subdivisions,sub-subdivisions.
save all of your ddl values before binding them.
Their selected values my not be availble after filtering them down.

dim sState as string=""
dim sCounty as string=""
dim sCity as string=""

if state.selectedIndex>-1 then
sState =state.selectedvalue
end if
if county.selectedIndex>-1 then
sCounty=county.selectedvalue
end if
if city.selectedIndex>-1 then
sCity=city.selectedvalue
end if


' Do all your binds and filtering queries as show above for all three dropdowns.


'Then select the values
SelectListBoxValue(state,sState)
SelectListBoxValue(county,sCounty)
SelectListBoxValue(city,sCity)
lblState.text=GetListBoxValue(state,"Unknown")
lblCounty.text=GetListBoxValue(county,"Unknown")
lblCity.text=GetListBoxValue(city,"Unknown")

You can do all this in the page load if you want.
You would be using Autopostback only to make the page postback when the user changes one of the dropdowns but you don't have to use any code in the dropdown's change events for what you are doing.






Private Sub SelectListBoxValue(ByRef MylistBox As Object, ByVal MyVal As Object)
Dim obj As DropDownList = CType(MylistBox, DropDownList)

Dim v As String = CStr(MyVal)
If Not v = Nothing Then v = v.ToLower
Dim i As Integer
For i = 0 To obj.Items.Count - 1
If CType(obj.Items(i), ListItem).Value.ToLower = v Then
obj.SelectedIndex = i
Exit For
End If
Next

End Sub


Private Function GetListBoxValue(ByVal obj As Object, Optional ByVal sDefaultValue As Object = "") As Object
If obj.SelectedIndex = -1 Then Return sDefaultValue
'Return obj.SelectedItem.Value
Return obj.items(obj.SelectedIndex).value
End Function

 

[bubberz]

TRomes,
Here's what I've got so far. I still get the second ddl, ddlGroup2, resetting back to the first option and not keeping it's selected value when it's doing a PostBack:

Private Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.PreRender
Dim sDiv As String = ""
Dim sGrp As String = ""
Dim sRes As String = ""

If ddlDivision.SelectedIndex > -1 Then
sDiv = ddlDivision.SelectedValue
End If

If ddlGroup.SelectedIndex > -1 Then
sGrp = ddlGroup2.SelectedValue
End If

If ddlResource.SelectedIndex > -1 Then
sRes = ddlResource.SelectedValue
End If

Dim sqlGrp As String = "SELECT GC+' - '+GD as Expr2, GC, GD, "
sqlGrp &= "DC FROM [RL-DivG]"

If Not sDiv = Nothing Then
sqlGrp = sqlGrp & " WHERE DC ='" & sDiv & "' "
End If
If Not sGrp = Nothing Then
sqlGrp = sqlGrp & " and GD ='" & sGrp & "' "
End If

Dim cmdGrp As New OleDbCommand(sqlGrp, Con1)
Con1.Open()
Dim myRDR As OleDbDataReader = cmdGrp.ExecuteReader()
If myRDR.HasRows = True Then
ddlGroup2.DataSource = myRDR
ddlGroup2.DataTextField = "Expr2"
ddlGroup2.DataValueField = "GD"
ddlGroup2.DataBind()
End If
Con1.Close()

SelectListBoxValue(ddlDivision, sDiv)
SelectListBoxValue(ddlGroup2, sGrp)
SelectListBoxValue(ddlResource, sRes)

lblDiv.text = GetListBoxValue(ddlDivision, "Unknown")
lblGrp2.text = GetListBoxValue(ddlGroup2, "Unknown")
lblRes.text = GetListBoxValue(ddlResource, "Unknown")

End Sub

Private Sub SelectListBoxValue(ByRef MylistBox As Object, ByVal MyVal As Object)
Dim obj As DropDownList = CType(MylistBox, DropDownList)
Dim v As String = CStr(MyVal)

If Not v = Nothing Then v = v.ToLower
Dim i As Integer
For i = 0 To obj.Items.Count - 1
If CType(obj.Items(i), ListItem).Value.ToLower = v Then
obj.SelectedIndex = i
Exit For
End If
Next
End Sub

Private Function GetListBoxValue(ByVal obj As Object, Optional ByVal sDefaultValue As Object = "") As Object
If obj.SelectedIndex = -1 Then Return sDefaultValue
'Return obj.SelectedItem.Value
Return obj.items(obj.SelectedIndex).value
End Function

 

[RTomes]

Make sure the datavalue and datatext are correct.
This simple example works:

Private Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.PreRender



Dim previous As Integer = Me.DropDownList1.SelectedIndex

'Bind Data
Dim tbl As New DataTable
tbl.Columns.Add(New DataColumn("myval"))
For i As Integer = 0 To 9
Dim rw As DataRow = tbl.NewRow
rw("myval") = CStr(i)
tbl.Rows.Add(rw)
Next
Me.DropDownList1.DataTextField = "myval"
Me.DropDownList1.DataValueField = "myval"
Me.DropDownList1.DataSource = tbl
Me.DropDownList1.DataBind()

'Reset to previous selected item
Me.DropDownList1.SelectedIndex = previous

End Sub

 

[bubberz]

TRomes,

The only real problem now is if I select an option from the first ddl, and it populates the second ddl with only one record, how do I pull that second ddl value out. I was trying to go the route of inserting "Select From Below" as the first record to force a PostBack and get the value if there is in fact only one DB record.
What I've done, is place a textbox above each of the three ddls, so the user knows what they've selected. I'll eventually pass these three text boxes back to a DataGrid and have the FooterTemplate populated with these new records.

 

[RTomes]

It should work like this, is this what you are doing?
Page first loads:
Three ddls. The first is populated but not selected the other two are empty.
The has to select from the first one to populate the second. The third is still empty until the user selects from the second one. When he does the third one is populated. When he selected from the third then you invoke some kind of action like populate a datagrid or something, or could be progressively filtering down a datagrid.

 

[bubberz]

RTomes,
Yes, you have to select from the first two to build the third ddl...you're correct about that.
I put the building of the third ddl in the Page_Prerender handler. I've got the first two working correctly and populating text boxes above each of those two ddls, but the third is acting weird. If I select an item from it, the text box above only populates with the first record on post back regardless if I select the 5th, 6th, 7th or so on. I populate the text box from the 3rdddl_SelectedItemChanged handler:
txtBox.Text = 3rdddl.SelectedItem.ToString

..but that isn't working, since it always inserts the first value.