How to make relationship between tow tables

[Nader]

In the first thank you for this site.
How to make relationship ( join Table1 to Table2) between tow tables connecting to DBaccess.
Is this possible to make the to relationship ( join Table1 to Table2) not in Dataset.

Thank you for hep!

 

[md110]

Private Sub Relation_ship_2combobox()
        Dim DS As New DataSet
        Dim SQL As String = "select *  from Countries "
        Dim SQL1 As String = "select *  from city"
        Dim Connection As New OleDbConnection("Provider= Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\db1.mdb")
        Dim AdapterParent As New OleDbDataAdapter(SQL, Connection)
        Dim AdapterChild As New OleDbDataAdapter(SQL1, Connection)
        AdapterParent.Fill(DS, "Countries")
        AdapterChild.Fill(DS, "city")
        DS.Relations.Add("RelCountrycity", _
                        DS.Tables("Countries").Columns("ID_contry"), _
                        DS.Tables("city").Columns("ID_contry"))
        With Cbocontry

            .DisplayMember = "Countries.name_contry"
            .ValueMember = "ID"
            .DataSource = DS
        End With
        With Cbocity
            .DataSource = DS
            .DisplayMember = "Countries.RelCountrycity.name_city"
            .ValueMember = "ID"

        End With
    End Sub

 

[Nader]

Thank You!. I'm going to try it.

 

[cjard]

In the first thank you for this site.
How to make relationship ( join Table1 to Table2) between tow tables connecting to DBaccess.
Is this possible to make the to relationship ( join Table1 to Table2) not in Dataset.

Thank you for hep!

You seem to be confused between JOIN and relationship. A relationship typically is a CONSTRAINT where a row in one table must have a matching row in another table. It is not needed for a JOIN to function, and a join is merely associating rows from one or more tables (tables can be joined to themselves) on some values.

Further, relationships can be found in databases themselves, or datasets. Ina dataset, they are usualy present to make it easy to show related data on a single form. In databases, they enforce data integrity.

Please use this information to form a more accurate question

 

[wakh]

If you are looking for creating relationships between tables, which will later on help you in joining tables together, look for something called Normalisation and read on it.

 

[TKap]

exception in this code

hi i tried out thid code in a slightly diff manner as:
dc1 = ds1.Tables("ghouseMaster").Columns("ghID")
dc2 = ds1.Tables("roomMaster").Columns("ghID")
datarelation = New DataRelation("Tab1andTab2", dc1, dc2)
so while debugging null values are getting stored in dc1 and dc2 both which are datacoloumn objects and because of that am getting an exception in the datarelation constructor which says that can't have empty column values.
What is the mistake?

Private Sub Relation_ship_2combobox()
        Dim DS As New DataSet
        Dim SQL As String = "select *  from Countries "
        Dim SQL1 As String = "select *  from city"
        Dim Connection As New OleDbConnection("Provider= Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\db1.mdb")
        Dim AdapterParent As New OleDbDataAdapter(SQL, Connection)
        Dim AdapterChild As New OleDbDataAdapter(SQL1, Connection)
        AdapterParent.Fill(DS, "Countries")
        AdapterChild.Fill(DS, "city")
        DS.Relations.Add("RelCountrycity", _
                        DS.Tables("Countries").Columns("ID_contry"), _
                        DS.Tables("city").Columns("ID_contry"))
        With Cbocontry

            .DisplayMember = "Countries.name_contry"
            .ValueMember = "ID"
            .DataSource = DS
        End With
        With Cbocity
            .DataSource = DS
            .DisplayMember = "Countries.RelCountrycity.name_city"
            .ValueMember = "ID"

        End With
    End Sub

 

[IanRyder]

Hi,

You do not give any definition of the column types in your data source but my guess would be that the Parent column defined in your relationship contains Null values. You need to check your ghouseMaster table for any null values in the ghID field. If all is OK in your parent table then you need to do the same for the ghID field in your related table.

Hope that helps.

Cheers,

Ian

 

[TKap]

I found out the bug and no it was not cause the table contained empty fields.
Another query that I have is that, after having created this relation I now want to add certain fields of both these tables to a dataGridView.
ds.relations.add(dr)
dataGridView1.dataSource=ds

Now what should I do to display certain columns from both these tables?

 

[IanRyder]

Hi,

There are a few ways to use the DataGridView depending on what you want to do. Start here with the Documentation on the control. Scroll towards the bottom and there are lots of "How To's" to get you started.

DataGridView Class (System.Windows.Forms)

Cheers,

Ian