Solved

Creating relationships in VB.Net using ADO.Net

Posted on 2004-08-03
12
1,004 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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

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!

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

630 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