Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
 
MSDN Visual Basic Community
 
 
 
 
 
TitleDefine a relationship between two tables in a DataSet in VB .NET
DescriptionThis example shows how to define a relationship between two tables in a DataSet in VB .NET.
KeywordsDataGrid, DataTable, bind, ADO.NET, data, database, relationship
CategoriesDatabase, Controls
 
When the program starts, it creates OleDbDataAdapters to select data from the Addresses and TestScores tables. It creates a DataSet and uses the adapters' Fill methods to load the data.

The program then creates a DataRelation object relating the two tables' ContactID fields. That defines a master/detail relationship between the two tables.

It finishes by setting a DataGrid control's DataSource property to the DataSet.

 
Private m_da_Addresses As OleDbDataAdapter
Private m_da_TestScores As OleDbDataAdapter
Private m_DataSet As DataSet

Private Sub Form1_Load(ByVal sender As System.Object, ByVal _
    e As System.EventArgs) Handles MyBase.Load
    Const SELECT_ADDRESSES As String = "SELECT * FROM " & _
        "Addresses"
    Const SELECT_TEST_SCORES As String = "SELECT * FROM " & _
        "TestScores"

    ' Get the database file name.
    Dim db_name As String = Application.StartupPath
    db_name = db_name.Substring(0, db_name.LastIndexOf("\"))
    db_name &= "\Contacts.mdb"

    ' Compose the connection string.
    Dim connect_string As String = _
          "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & db_name & ";" & _
          "Persist Security Info=False"

    ' Create a DataAdapter to load the Addresses table.
    m_da_Addresses = New OleDbDataAdapter(SELECT_ADDRESSES, _
        connect_string)

    ' Create a DataAdapter to load the Addresses table.
    m_da_TestScores = New _
        OleDbDataAdapter(SELECT_TEST_SCORES, connect_string)

    ' Create and fill the DataSet.
    m_DataSet = New DataSet
    m_da_Addresses.Fill(m_DataSet, "Addresses")
    m_da_TestScores.Fill(m_DataSet, "TestScores")

    ' Define the relationship between the tables.
    Dim data_relation As New _
        DataRelation("Addresses_TestScores", _
        m_DataSet.Tables("Addresses").Columns("ContactID"), _
            _
        m_DataSet.Tables("TestScores").Columns("ContactID"))
    m_DataSet.Relations.Add(data_relation)

    ' Bind the DataGrid to the DataSet.
    dgContacts.DataSource = m_DataSet
End Sub
 
When the program is ending, it makes an OleDbCommandBuilder object for the adapter. The adapter will use the command builder to make SQL insert, update, and delete commands as necessary to update the data. It then calls the adapter's Update method to save the changes to the data.
 
' Save changes to the data.
Private Sub Form1_Closing(ByVal sender As Object, ByVal e _
    As System.ComponentModel.CancelEventArgs) Handles _
    MyBase.Closing
    ' Use a CommandBuilder to make the INSERT,
    ' UPDATE, and DELETE commands as needed.
    Dim cb_addresses As New _
        OleDbCommandBuilder(m_da_Addresses)
    Dim cb_testscores As New _
        OleDbCommandBuilder(m_da_TestScores)

    ' Update the database.
    Try
        m_da_Addresses.Update(m_DataSet, "Addresses")
        m_da_TestScores.Update(m_DataSet, "TestScores")
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub
 
For information on database programming in VB .NET, see my book Visual Basic .NET Database Programming.
 
 
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated