asked on

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) ?

UberDeveloper

Use a DataRelation as in:

Dim myRelation As New DataRelation("Data Relation 1", Column1, Column2)

UberDeveloper

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.

UberDeveloper

Oh and if you want to add a constraint to a column:

Dim myConstraint As New UniqueConstraint(myDataColumn)

ds_XWALK.Constraints.Add(myConstraint)

amw22

ASKER

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

Dim myRelation As New DataRelation("Data Relation 1", Column1, Column2)

UberDeveloper

I belive its just an identifier

UberDeveloper

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

amw22

ASKER

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?

ASKER CERTIFIED SOLUTION

UberDeveloper

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

UberDeveloper

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

Dim parentCol As DataColumn
Dim childCol As DataColumn

amw22

ASKER

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.

SOLUTION

xorcrack

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

amw22

ASKER

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)