linking two tables in a query

[kareem100]

Hi,

Could someone tell me how to solve this problem.

I have two tables in the same database, customer and orders. They have a common field ( customer number). I am trying to set up a form to display the order(s) for any given customer. I have two datagridviews on my form, one for the customer table and one for orders. At present the datagridviews are showing the entire data from both tables. Ideally I like the top datagridview to show all the customers, and the bottom datagridview to show only the order enteries for the currently selected customer.

My question is how do I do this.

Kareem

[jmcilhinney]

Firstly, you need both DataTables to be in the same DataSet. You then add a DataRelation to the DataSet relating the two tables. If you're using a typed DataSet then this should already have been done for you.

You now bind the DataSet to a BindingSource and then bind the BindingSource to both grids. You set the DataMember of the parent grid to the name of the parent table so it shows all the parent records. You then set the DataMember of the child table to the name of the relationship, so it shows only those records related to the selected parent record.

Data-binding can do a lot of grunt work for you with just a few properties set at design time. You can also set up data-binding in code but it is a little bit more work, plus you have to ensure that you do everything in the correct order.

[kareem100]

Hi,

I just tried doing what you've told me, but i get an error. when i tried setting that datamember

of the child grid ( where orders are meant to be displayed ) to the name of the relation, I get

an error saying propert "value is not valid - DataMember property 'relcust' cannot be found on

the DataSource".

I think I did what you told me to do correctly, but I'll briefly tell you what I did so that you

can check it.


I added the data relation by clicking on the dataset in the datasources window and then clicking
on edit dataset with designer. THen from the top menu I clicked data -> add -> relation. This

bought up a window entitled relation. here I typed in a name for the relation and choose the

common field etc.

I added a new binding source for the form ( bindingsource1 ). The datasource property of this

binding source I set to database1dataset ( name of the dataset ).


I then set the datasource property of the parent grid to bindingsource1.
I then set the datasource property of the child grid also to bindingsource1. I also set the

datamember property of the parent grid to the name of the parent table ( customer ).

Is this correct?


Regards,

Kareem.

[jmcilhinney]

OK, sorry. I didn't actually test that myself and I have usually set most of it up in the designer before. This bit is not quite correct:

You now bind the DataSet to a BindingSource and then bind the BindingSource to both grids. You set the DataMember of the parent grid to the name of the parent table so it shows all the parent records. You then set the DataMember of the child table to the name of the relationship, so it shows only those records related to the selected parent record.

What you need to do is bind the parent table, not the DataSet, to the BindingSource. You then set the BindingSource as the DataSource for both grids and set the name of the relation as the DataMember for the child grid.

Try this:

1. Create a new WinForms project.
2. Add two DataGridViews and one BindingSource to your form.
3. Add this code:

Private Sub Form1_Load(ByVal sender As System.Object, _
                       ByVal e As System.EventArgs) Handles MyBase.Load
    Dim parentTable As New DataTable("Parent")

    With parentTable.Columns
        .Add("ID", GetType(Integer))
        .Add("Name", GetType(String))
    End With

    With parentTable.Rows
        .Add(1, "Parent 1")
        .Add(2, "Parent 2")
        .Add(3, "Parent 3")
    End With

    Dim childTable As New DataTable("Child")

    With childTable.Columns
        .Add("ID", GetType(Integer))
        .Add("ParentID", GetType(Integer))
        .Add("Name", GetType(String))
    End With

    With childTable.Rows
        .Add(1, 1, "Child 1")
        .Add(2, 1, "Child 2")
        .Add(3, 2, "Child 3")
        .Add(4, 2, "Child 4")
        .Add(5, 3, "Child 5")
        .Add(6, 3, "Child 6")
    End With

    Dim data As New DataSet("Data")

    With data.Tables
        .Add(parentTable)
        .Add(childTable)
    End With

    Dim relation As New DataRelation("ParentChild", _
                                     parentTable.Columns("ID"), _
                                     childTable.Columns("ParentID"))

    data.Relations.Add(relation)

    Me.BindingSource1.DataSource = data.Tables("Parent")

    Me.DataGridView1.DataSource = Me.BindingSource1

    Me.DataGridView2.DataMember = "ParentChild"
    Me.DataGridView2.DataSource = Me.BindingSource1
End Sub

Now run the project and select records in the first grid and watch the second grid update automatically.

[kareem100]

Hi,

The code that you gave me worked a treat.

I then went back to my tried the previous/original application and tried to make the changes you suggested via the designer. Here I still came accross some problems.


I could not bind the parent table to the binding source. When I clicked on the datasource property of the bindingsource, a window appeared showing several sources of data, but the parent table was not listed here. It also impossible to type over the value held in the datasource property.


Also When i tried to type in the name of the relation in the datamember property of the bottom/child grid I got the same messages as before. Could this be because I set up the relation between the two tables after I'd initially 'set up' the dataset. I ran the data wizard (via configure dataset with wizard option), but still got the same result. I did not change anything when i ran the wizard though.

Any ideas how to overcome these problems?

Regards,

Kareem

[jmcilhinney]

You can't bind a DataTable directly to a BindingSource in the designer because a DataTable is not a component. You'd have to create a DataSet in the designer, select that as the DataSource and then select the DataTable as the DataMember.

[kareem100]

Hi,

Okay, and what about the second problem, setting the DataMember of the child table to the name of the relationship (between the tables ). I still get the same error message as before. Any ideas what may cause this ?

Regards,
Mohammed.

[jmcilhinney]

If your DataSet contains two DataTables and one DataRelation you need two BindingSources. The first BindingSource gets the DataSet as the DataSource and the DataTable as the DataMember. The second BindingSource gets the first BindingSource as the DataSource and the DataRelation as the DataMember.

As an example, if you have DataSet1 with ParentTable, ChildTable and ParentChildRelation, you need ParentBindingSource and ChildBindingSource and you'd assign properties in the designer like this:

ParentBindingSource.DataSource = DataSet1
ParentBindingSource.DataMember = ParentTable

ChildBindingSource.DataSource = ParentBindingSource
ChildBindingSource.DataMember = ParentChildRelation

[kareem100]

Hi

Yep, it works perfectly now. I really appreciate the help you've given me.


Regards,

Kareem.