none
Data binding for two tables RRS feed

  • Question

  • Hi,

    I'm new in VB, I need an advice with databinding in Visual Basic. I have two tables in Mysql. 
    Table1- Products - fields ProductID, Name, CategoryID (foreign key to table Categories) 
    Table2- Categories - fields CategoryID, Name 

    First I fill the combobox Categories with records from table Categories (FillCombobox). 
    Then I need to know how to "synchronize" displayed value of combobox following the value CategoryID from table Products. Is this possible in VB or my view is completely wrong? 
    My code now is:

     
     Dim connection As New OdbcConnection("DSN=Mysql")
    
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        FillCombobox()
        Dim ds As New DataSet
        Dim command As OdbcCommand
        Dim cmdString As String = "Select Name, CategoryID from Products"
        command = New OdbcCommand(cmdString, connection)
        connection.Open()
    
        Dim adapter As New OdbcDataAdapter(cmdString, connection)
        adapter.Fill(ds, "products")
        Dim bs As New BindingSource
        bs.DataSource = ds.Tables(0)
        BindingNavigator1.BindingSource = bs
    
        txtProductName.DataBindings.Add("Text", bs, "name")
    
    End Sub
    
    Private Sub FillCombobox()
    
        Dim cmdString As String = "Select CategoryID, Name from Categories"
        Dim command As New OdbcCommand(cmdString, connection)
        Dim ds2 As New DataSet
        Dim adapter2 As New OdbcDataAdapter(cmdString, connection)
        adapter2.Fill(ds2, "categories")
    
        cbxCategories.DataSource = ds2.Tables(0)
        cbxCategories.DisplayMember = "Name"
        cbxCategories.ValueMember = "CategoryID"
    
    End Sub

    Wednesday, April 18, 2018 4:54 AM

All replies

  • Hello,

    What is missing is setting up the data relationship. If the link is not enough then (and this is more on the complex side) at my code sample which does relations using ListBox but that would be the same for ComboBox's too. In the code sample I have a special language extension that when used looks like this.

    ds.SetRelation("ProductColor", "ProductSize", "Id", "ProductColorId") 

    With that said there is another way which is load both tables into DataTable objects then on SelectChanged filter the child table e.g. State is the master, city the child in the relations.

    Using SQL-Server, same works for MySql and other databases. by changing the data provider.

    Imports System.Data.SqlClient
    
    Public Class Operations
    
        Private ReadOnly _connectionString As String =
                    "Data Source=KARENS-PC;Initial Catalog=ForumExample;" &
                    "Integrated Security=True"
    
        Private _mStateTable As DataTable
        Public ReadOnly Property StateTable As DataTable
            Get
                Return _mStateTable
            End Get
        End Property
        Private _mCityTable As DataTable
        Public ReadOnly Property CityTable As DataTable
            Get
                Return _mCityTable
            End Get
        End Property
        Private Sub LoadStatesCityTables()
            _mStateTable = New DataTable
            _mCityTable = New DataTable
    
            Using cn As New SqlConnection With {.ConnectionString = _connectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
    
                    cmd.CommandText = "SELECT Id,[Name] AS name " &
                                      "FROM dbo.StateMaster ORDER BY Name"
    
                    cn.Open()
    
                    _mStateTable.Load(cmd.ExecuteReader)
    
                    cmd.CommandText = 
                        "SELECT ID,Name AS name,StateID " &
                        "FROM dbo.CityMaster ORDER BY Name"
    
                    _mCityTable.Load(cmd.ExecuteReader)
                End Using
            End Using
        End Sub
        Public Sub New()
            LoadStatesCityTables()
        End Sub
    End Class
    

    Form code, has two ComboBox controls.

    Public Class Form1
        Private _cityTable As DataTable
        Private Sub Form1_Load(sender As Object, e As EventArgs) _
            Handles MyBase.Load
    
            Dim ops As New Operations
    
            cboStates.DataSource = ops.StateTable
            cboStates.DisplayMember = "Name"
    
            _cityTable = ops.CityTable
    
            cboCity.DataSource = _cityTable
            cboCity.DisplayMember = "Name"
    
            AddHandler cboStates.SelectedIndexChanged,
                AddressOf cboStates_SelectedIndexChanged
    
            GetCities()
    
        End Sub
        Private Sub cboStates_SelectedIndexChanged(sender As Object, e As EventArgs)
            GetCities()
        End Sub
        Private Sub GetCities()
            Dim source As New AutoCompleteStringCollection
    
            Dim stateId = CType(cboStates.SelectedItem, DataRowView).
                    Row.Field(Of Integer)("Id")
    
            _cityTable.DefaultView.RowFilter = $"StateId = {stateId}"
    
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, April 18, 2018 8:51 AM
    Moderator
  • Hi, 

    Wrong answer, however, I used the image so please leave that

    http://www.vb-tips.com/DataGridRelation.aspx


    Success
    Cor




    Wednesday, April 18, 2018 10:20 AM

  • Then I need to know how to "synchronize" displayed value of combobox following the value CategoryID from table Products.
     

    Hi js1980_svk,

    You want to change combobox selecteditem when you change select item from product? If yes, I do one example that you can take a look:

      Dim dt As New DataTable
        Dim dt1 As New DataTable
        Dim bindsource As New BindingSource
        Private Sub Frmdatatbinding_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim str As String = "Data Source=(LOCALDB)\MSSQLLOCALDB;Initial Catalog=Testlocaldb;Integrated Security=True"
            Dim sqlquery As String = "select p.productID, p.Name as pname, c.CategoryID, c.Name as cName from Products p left join Categories c on p.CategoryID=c.CategoryID"
            Using con As New SqlConnection(str)
                con.Open()
                Using cmd As New SqlCommand(sqlquery, con)
                    Dim adapter As New SqlDataAdapter(cmd)
                    adapter.Fill(dt)
                    bindsource.DataSource = dt
                    DataGridView1.DataSource = bindsource
                    TextBox1.DataBindings.Add("Text", bindsource, "pname")
                End Using
            End Using
            loaddate()
        End Sub
    
        Private Sub loaddate()
            Dim str As String = "Data Source=(LOCALDB)\MSSQLLOCALDB;Initial Catalog=Testlocaldb;Integrated Security=True"
            Dim sqlquery As String = "select distinct c.CategoryID, c.Name as cName from Products p left join Categories c on p.CategoryID=c.CategoryID "
            Using con As New SqlConnection(str)
                con.Open()
                Using cmd As New SqlCommand(sqlquery, con)
                    Dim adapter As New SqlDataAdapter(cmd)
                    adapter.Fill(dt1)
                    ComboBox1.DataSource = dt1
                    ComboBox1.DisplayMember = "cName"
                    ComboBox1.ValueMember = "CategoryID"
                    ComboBox1.DataBindings.Add("Text", bindsource, "cName")
                End Using
            End Using
        End Sub

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 19, 2018 6:30 AM
    Moderator
  • Hello, thanks, your code works - when I navigate through records, combobox always display saved category for current record, that's fine, BUT I need to fill combobox with all records from table categories to have a possibility to change the category for current records

    Thursday, April 19, 2018 1:14 PM
  • Sorry, 

    Like many others I did look at the answer and not the question. My previous reply does not fit to your problem. I tried to correct your code. Of course it is not tested because I don't even have MySQL, but should keep the category combobox in line. 

    Imports System.ComponentModel
    Public Class Form1
        Private Connection As New OdbcConnection("DSN=Mysql")
        Private BS As New BindingSource
        Private WithEvents Cm As CurrencyManager
        Private Products As DataTable
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim cm = CType(Me.BindingContext(BS.DataSource), CurrencyManager)
            FillCombobox()
            Dim dt As New DataSet
            Dim command As OdbcCommand
            Dim cmdString As String = "Select Name, CategoryID from Products"
            command = New OdbcCommand(cmdString, Connection)
            Connection.Open()
            Dim adapter As New OdbcDataAdapter(cmdString, Connection)
            adapter.Fill(Products)
            BS.DataSource = Products.DefaultView
            BindingNavigator1.BindingSource = BS
            txtProductName.DataBindings.Add("Text", BS, "name")
        End Sub
        Private Sub FillCombobox()
            Dim cmdString As String = "Select CategoryID, Name from Categories"
            Dim command As New OdbcCommand(cmdString, Connection)
            Dim categories As New DataTable
            Dim adapter2 As New OdbcDataAdapter(cmdString, Connection)
            adapter2.Fill(categories)
            cbxCategories.DataSource = categories
            cbxCategories.DisplayMember = "Name"
            cbxCategories.ValueMember = "CategoryID"
        End Sub
        Private Sub cm_ListChanged(sender As Object, e As ListChangedEventArgs) Handles Cm.ListChanged
            cbxCategories.SelectedIndex = CInt(DirectCast(BS.DataSource, DataView)(Cm.Position)("CategoryID"))
        End Sub
    End Class


    Success
    Cor


    Friday, April 20, 2018 12:12 PM