country/state filter

[Rani]

I have two drop down list box. One for country and the other for state.
I am populating the country ddl. I am using a row filter and want to populate the state in the state dll per selected country.
here is my code.

in my page load event
If Not IsPostBack Then
Me.fillcountry()
End If
Me.fillstate()

then filling the country ddl which works

Private Sub fillcountry()
Dim con As New SqlConnection(Application("connection"))
Dim sql As String = "select * from countries"
Dim da As New SqlDataAdapter(sql, con)
Dim ds As New DataSet
da.Fill(ds)
Me.ddlcountry.DataSource = ds
Me.ddlcountry.DataTextField = "country"
Me.ddlcountry.DataValueField = "country_id"
Me.ddlcountry.DataBind()
con.Close()
da = Nothing
ds = Nothing
con = Nothing
End Sub

then here is the trouble. can you please suggest right direction?

Private Sub fillstate()
Dim con As New SqlConnection(Application("connection"))
Dim sql As String = "select * from states"
Dim da As New SqlDataAdapter(sql, con)
Dim ds As New DataSet
da.Fill(ds)
Dim text As String = ds.GetXml
Dim dtbl As DataTable = ds.Tables(0)
Dim dtv As New DataView(dtbl)
dtv.RowFilter = "country_id=" & (Me.ddlcountry.SelectedItem.Value.ToString)
Me.ddlstate.DataSource = dtv.Sort
'dtv.Sort = "state"
Me.ddlstate.DataTextField = "state"
Me.ddlstate.DataBind()
End Sub

thanks

 

[Rani]

Please disregard my request. I figured it out.
I had to omit sort in my dtv
basically, it should have been
me.ddlstate.datasource = dtv
and it worked
Thanks for your time.