vb.net bindingSource to navigate two tables

Question

I am using VB.Net to load data from SQL server, institute and country and fill each table in a dataset. Country table has countryId, countryName where countryId as the primary key. Institute has instId, name, countryId columns where countryId as a foreign key taken from country table. I am using bindingSource to navigate through data in table institute and display data in text boxes. In other hand I have a combobox that also has a bindingSource which is the country table. I want now when I navigate the institute table I also get data in my combobx get changed based on the value in institute countryId. Have a look to the following code:

Private Sub frmSystemOptions_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            dsOptions = New DataSet
            loadOptions()

            bsInstitute = New BindingSource(dsOptions, "institute")

            InstIdTextBox.DataBindings.Add("Text", bsInstitute, "instId")
            NameTextBox.DataBindings.Add("Text", bsInstitute, "name")
            CountryIdTextBox.DataBindings.Add("Text", bsInstitute, "countryId")

            bsCountry = New BindingSource(dsOptions, "country")

            cmbCountry.DataSource = bsCountry
            cmbCountry.DisplayMember = "countryName"
            cmbCountry.ValueMember = "countryId"

        Catch ex As Exception
            MsgBox(Err.Description)
        End Try
    End Sub

    Sub loadOptions()
        Dim sql As String

        Try
            sqlConn = New SqlConnection(connString)
            sqlConn.Open()

            sql = "select instId, name, countryId from institute"
            daInstitute = New SqlDataAdapter(sql, sqlConn)
            daInstitute.Fill(dsOptions, "institute")
            '----------------------------------------------------------------------

            sql = "select countryId, countryName from country"
            daAdapter = New SqlDataAdapter(sql, sqlConn)
            daAdapter.Fill(dsOptions, "country")
            '----------------------------------------------------------------------

            sqlConn.Close()
        Catch ex As Exception
            sqlConn.Close()
            MsgBox(Err.Description)
        End Try
    End Sub

Also for navigation I use the following code:

Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        If bsInstitute.Position + 1 < bsInstitute.Count Then
            bsInstitute.MoveNext()
        Else
            bsInstitute.MoveFirst()
        End If
        Me.Invalidate()
    End Sub

Now how can I make the countryName in the combobx changes based on the value of the countryId in the institute table?

Hilal Al-Rajhi Hilal Al-Rajhi · Accepted Answer · 2014-01-07T16:31:42

You need to bind the combobox as following:

Private Sub frmSystemOptions_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            dsOptions = New DataSet
            loadOptions()

            bsInstitute = New BindingSource(dsOptions, "institute")
            bsCountry = New BindingSource(dsOptions, "country")

            InstIdTextBox.DataBindings.Add("Text", bsInstitute, "instId")
            NameTextBox.DataBindings.Add("Text", bsInstitute, "name")
            CountryIdTextBox.DataBindings.Add("Text", bsInstitute, "countryId")

            cmbCountry.DataSource = bsCountry
            cmbCountry.DisplayMember = "countryName"
            cmbCountry.ValueMember = "countryId"

            cmbCountry.DataBindings.Add(New Binding("SelectedValue", _
             Me.bsInstitute, "countryId", True))

        Catch ex As Exception
            MsgBox(Err.Description)
        End Try
    End Sub

    Sub loadOptions()
        Dim sql As String

        Try
            sqlConn = New SqlConnection(connString)
            sqlConn.Open()

            sql = "select instId, name, countryId from institute"
            daInstitute = New SqlDataAdapter(sql, sqlConn)
            daInstitute.Fill(dsOptions, "institute")
            '----------------------------------------------------------------------

            sql = "select countryId, countryName from country"
            daAdapter = New SqlDataAdapter(sql, sqlConn)
            daAdapter.Fill(dsOptions, "country")
            '----------------------------------------------------------------------

            sqlConn.Close()
        Catch ex As Exception
            sqlConn.Close()
            MsgBox(Err.Description)
        End Try
    End Sub

vb.net bindingSource to navigate two tables

1 Answers