Passing combobox selection to FillBy SQL code

[Aleksandor51]

Beginner here with VB 2008 Express.
Thanks to this forum, I have one problem solved, properly converting text to numbers. Now I want to use that conversion as a selection parameter in a FillBy statement. Here's what I'm trying to do:

I have two comboboxes linked to an Access 2007 database. One box lists the names of the States in the US. This is what I used for the Table Adapter Configuration Wizard for the combobox "State"

SELECT ST_UID, ST_NAME FROM STATE

For the combobox County I used:

SELECT ST_UID, CNTY_NAME FROM COUNTY

ST_UID is an Integer. I created a label with a bound value to STATE.ST_UID so that when a user selects the name of a state from the State box the appropriate ST_UID populates the label, (i.e, California has an ST_UID = 5). The problem is when a state is selected, I only want the Counties for that state to appear in the County box, i.e, COUNTY.ST_UID = 5 if California is the selected state.

Apparently the following code converts the value in the st_uid label to an Integer:
Dim StUID As Integer
StUID = CInt(Trim(lblStUID.Text))

This is what I last tried when again using the Table Adapter Configuration Wizard for the table that populates the County box:

SELECT CNTY_FIPS_CD, CNTY_NAME, CNTY_UID, ST_UID
FROM COUNTY
WHERE (ST_UID = '" & StUID & "')
ORDER BY ST_UID, CNTY_NAME

Problem is I'm not getting any results, the "County" combobox is blank. I've tried a lot of variations on the code, such as % StUID %, etc., but no luck. I have experience with VB 6 but am a beginner with .NET. All suggestions most appreciated!

[kvprajapati]

Try it,

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ComboBox1.DataSource = GetTable("select st_uid,st_name from state")
        ComboBox1.DisplayMember = "st_name"
        ComboBox1.ValueMember = "st_uid"
    End Sub

    Public Function GetTable(ByVal query As String) As System.Data.DataTable
        Dim adp As New OleDb.OleDbDataAdapter(query, "put_connection_string_here")
        Dim dt As New Data.DataTable
        adp.Fill(dt)
        Return dt
    End Function

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim r As DataRowView = ComboBox1.SelectedItem
        If Not IsNothing(r) Then

            ComboBox2.DataSource = GetTable("select cnty_name from county where st_uid=" & r("st_uid"))
            ComboBox2.DisplayMember = "cnty_name"
        End If
    End Sub

[Aleksandor51]

Thanks adatapost. Here's what I entered into my code, but I think my string after "OleDb.OleDbDataAdapter(query," isn't right as I'm getting empty comboboxes when I run the code.

Private Sub frmEditOrganization_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'WQX_ODS_MS_AccessDataSet1.COUNTY' table. You can move, or remove it, as needed.
        REM   Me.COUNTYTableAdapter.Fill(Me.WQX_ODS_MS_AccessDataSet1.COUNTY)
        'TODO: This line of code loads data into the 'WQX_ODS_MS_AccessDataSet.STATE' table. You can move, or remove it, as needed.
        REM    Me.STATETableAdapter.Fill(Me.WQX_ODS_MS_AccessDataSet.STATE)
        cmbState.DataSource = gettable("Select st_uid, st_name from state")
        cmbState.DisplayMember = "st_name"
        cmbState.ValueMember = "st_uid"
    End Sub
    Public Function gettable(ByVal query As String) As System.Data.DataTable

        Dim adp As New OleDb.OleDbDataAdapter(query, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DatastorWQXNet\WQX_ODS_MS_Access.mdb")
        Dim dt As New Data.DataTable
        adp.Fill(dt)
        Return dt
    End Function

    Private Sub cmbState_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbState.SelectedIndexChanged
        Dim r As DataRowView = cmbState.SelectedItem
        If Not IsNothing(r) Then
            cmbCounty.DataSource = gettable("Select cnty_name from county where st_uid=" & r("st_uid"))
            cmbCounty.DisplayMember = "cnty_name"
        End If

        REM    Me.StCountyTableAdapter.FillBy(Me.WQX_ODS_MS_AccessDataSet1.County)
    End Sub