Solved

Creating relationships in VB.Net using ADO.Net

Posted on 2004-08-03
12
1,001 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

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…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
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…

809 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