Solved

Creating relationships in VB.Net using ADO.Net

Posted on 2004-08-03
12
999 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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now