Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1011
  • Last Modified:

Creating relationships in VB.Net using ADO.Net

I am creating an VB.Net program that is using a MS Access database on the backend.  I need to create a relationship between two tables but I am not familar with how to do this with ADO.Net.  

Right now I have the following data adapters and datasets:

 Dim da1 As New OleDb.OleDbDataAdapter("SELECT * FROM table1", Conn)
 Dim da2 As New OleDb.OleDbDataAdapter("Select * From table2", Conn)

 da1.Fill(ds_XWALK, "table1")
 da2.Fill(ds_mpsms, "table2")

How do I make a relationship between two tables (and two datasets) using ADO.Net in code (I do not want to use any of the wizards for this, I would like to code it all myself) ?
0
amw22
Asked:
amw22
  • 7
  • 4
2 Solutions
 
UberDeveloperCommented:
Use a DataRelation as in:

Dim myRelation As New DataRelation("Data Relation 1", Column1, Column2)
0
 
UberDeveloperCommented:
Oh and then after you do that you have to add your relation to the dataset, as in:


ds_XWALK.Relations.Add(myRelation)

ds_mpsms.Relations.Add(myRelation2) etc.
0
 
UberDeveloperCommented:
Oh and if you want to add a constraint to a column:

Dim myConstraint As New UniqueConstraint(myDataColumn)

ds_XWALK.Constraints.Add(myConstraint)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
amw22Author Commented:
What does "Data Relation 1" specify in the line below?

Dim myRelation As New DataRelation("Data Relation 1", Column1, Column2)
0
 
UberDeveloperCommented:
I belive its just an identifier
0
 
UberDeveloperCommented:
Hee is a good example that you should be able to adapt to your project:


Private Sub CreateRelation()
    ' Get the DataColumn objects from two DataTable objects in a DataSet.
    Dim parentCol As DataColumn
    Dim childCol As DataColumn
    ' Code to get the DataSet not shown here.
    parentCol = DataSet1.Tables("Customers").Columns("CustID")
    childCol = DataSet1.Tables("Orders").Columns("CustID")
    ' Create DataRelation.
    Dim relCustOrder As DataRelation
    relCustOrder = New DataRelation("CustomersOrders", parentCol, childCol)
    ' Add the relation to the DataSet.
    DataSet1.Relations.Add(relCustOrder)
 End Sub
0
 
amw22Author Commented:
The example above uses only one dataset.  In my project I have two different datasets.  Is it possible to create relationships between tables in two different datasets, and what is the circumstances in which I would want one dataset over multiple datasets?
0
 
UberDeveloperCommented:
You could, in theory use 1 dataset with several hundred tables.  The bigest advantage I can think about for using several datasets is that you can more effectively use the disconnected nature of ADO.NET.  And to use the above example with more than one dataset is easy enough

Dim da1 As New OleDb.OleDbDataAdapter("SELECT * FROM table1", Conn)
Dim da2 As New OleDb.OleDbDataAdapter("Select * From table2", Conn)

da1.Fill(ds_XWALK, "table1")
da2.Fill(ds_mpsms, "table2")

parentCol = ds_XWALK.Tables("Customers").Columns("CustID")
childCol = ds_mpsms.Tables("Orders").Columns("CustID")

Dim relCustOrder As DataRelation
relCustOrder = New DataRelation("CustomersOrders", parentCol, childCol)
' Add the relation to the DataSet.
ds_XWALK.Relations.Add(relCustOrder)
ds_mpsms.Relations.Add(relCustOrder)


0
 
UberDeveloperCommented:
oh, And of course, you have to dimesion your parentCol and childCol variables:

Dim parentCol As DataColumn
Dim childCol As DataColumn
0
 
amw22Author Commented:
I tried creating the relation using the two different datasets as you explained above, but it gives an error saying it cannot create a relationship between tables in two different datasets.
0
 
xorcrackCommented:
by default a unique key constraint is added to the primary table and a foreign key constraint is added to the secondary table.

if the 2 tables have a many to many relationship this may pose a problem and give you the error.

to override this functionality try setting the createConstraints property to false
0
 
amw22Author Commented:
The two tables should have a one to many relationship.
This is what I have tried and it gives me an error saying a relationship cannot be made between tables in different datasets:  

 Dim da_XWALK As New OleDb.OleDbDataAdapter("SELECT * FROM XWALKV43II", Conn)
        Dim da_mpsms As New OleDb.OleDbDataAdapter("Select * From MPSMS", Conn)
        da_XWALK.Fill(ds_XWALK, "XWALKV43II")
        da_mpsms.Fill(ds_mpsms, "MPSMS")
        ds_XWALK.EnforceConstraints = False
        ds_mpsms.EnforceConstraints = False
       Dim parentCol As DataColumn = ds_XWALK.Tables("XWALKV43II").Columns("MPSMS1")
        Dim childCol As DataColumn = ds_mpsms.Tables("MPSMS").Columns("MPSMSCode")
        Dim relMPSMS As DataRelation
        relMPSMS = New DataRelation("MPSMS", parentCol, childCol)
        ds_XWALK.Relations.Add(relMPSMS)
        ds_mpsms.Relations.Add(relMPSMS)

0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now