Solved

Creating relationships in VB.Net using ADO.Net

Posted on 2004-08-03
12
1,002 Views
Last Modified: 2011-10-03
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
Comment
Question by:amw22
  • 7
  • 4
12 Comments
 
LVL 2

Expert Comment

by:UberDeveloper
ID: 11711597
Use a DataRelation as in:

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

Expert Comment

by:UberDeveloper
ID: 11711605
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
 
LVL 2

Expert Comment

by:UberDeveloper
ID: 11711619
Oh and if you want to add a constraint to a column:

Dim myConstraint As New UniqueConstraint(myDataColumn)

ds_XWALK.Constraints.Add(myConstraint)
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:amw22
ID: 11711811
What does "Data Relation 1" specify in the line below?

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

Expert Comment

by:UberDeveloper
ID: 11711920
I belive its just an identifier
0
 
LVL 2

Expert Comment

by:UberDeveloper
ID: 11711940
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
 

Author Comment

by:amw22
ID: 11712033
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
 
LVL 2

Accepted Solution

by:
UberDeveloper earned 200 total points
ID: 11712149
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
 
LVL 2

Expert Comment

by:UberDeveloper
ID: 11712158
oh, And of course, you have to dimesion your parentCol and childCol variables:

Dim parentCol As DataColumn
Dim childCol As DataColumn
0
 

Author Comment

by:amw22
ID: 11712194
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
 
LVL 1

Assisted Solution

by:xorcrack
xorcrack earned 50 total points
ID: 11712408
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
 

Author Comment

by:amw22
ID: 11716348
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question