data binding to show 3 layer M/S tables in datagrid

[yuanh23]

Hi,

I have 3 talbes "customers","orders","details". i wanna to use 3 datagrids
to show those tables. and when the selected row in parent talbe changes, the
selected row in child table changes automaticly. If there is only 2 tables,
that's quite easy. add the relation "CusVsOrder" to dataset. binding the
"patient" with the parent datagrid, binding the relation
"customers.CusVsOrder" with the child datagrid. while, now there are 3
tables, and two one-to-many relations in the dataset. anyone know how to
realize it?


I figure out an unefficient method, the codes are showed as following, while
a question raised,what's is the event for the datagrid when the selected row
changed?

Thanks in advance.


Protected Overrides Sub OnLoad(ByVal e As System.EventArgs)
OleDbDataAdapter1.Fill(DataSet11)
OleDbDataAdapter2.Fill(DataSet11)
OleDbDataAdapter3.Fill(DataSet11)
'add relations "customers.CusVsOrder" and "orders.OrdVsDet" here.
code ignored
...
DataGrid1.DataMember = "customers"
DataGrid2.DataMember = "customers.CusVsOrder"
DataGrid3.DataMember = "orders"
DataGrid4.DataMember = "orders.OrdVsDet"
bind1 = BindingContext(DataSet11, "customers")
bind2 = BindingContext(DataSet11, customers
bind3 = BindingContext(DataSet11, "orders")
bind4 = BindingContext(DataSet11, "orders.OrdVsDet")
If bind2.Position < 0 Then
bind3.Position = bind2.Position
Else
Dim a, b As DataRowView
Dim id As Integer
a = CType(bind2.Current, DataRowView)
id = a.Row("orderID")
Dim c As DataRow
Dim i As Integer
For i = 0 To DataSet11.Tables("orders").Rows.Count - 1
bind3.Position = i
b = CType(bind3.Current, DataRowView)
If id = b.Row("orderID") Then
Exit For
End If
Next
End If

AddHandler bind2.PositionChanged, _
AddressOf bind2_PositionChanged

' addhander to handle the event when the select row in datagrid2 changed?
....

end sub


Private Sub bind2_PositionChanged _
(ByVal sender As Object, ByVal e As EventArgs)

If bind2.Position < 0 Then
bind3.Position = bind2.Position
Else
If Not bind2.Current Is Nothing Then
Dim a, b As DataRowView
Dim id As Integer
a = CType(bind2.Current, DataRowView)
id = a.Row("orderID")
Dim c As DataRow
Dim i As Integer
For i = 0 To DataSet11.Tables("orders").Rows.Count - 1
bind3.Position = i
b = CType(bind3.Current, DataRowView)
If id = b.Row("orderID") Then
Exit For
End If
Next
End If
End If
End Sub

 

[Jay B. Harlow [MVP - Outlook]]

yuanh23,
The following sample using 3 grids uses the Northwind Sample SQL Server
database:

customerAdapter.Fill(customerDataSet, "Customers")
orderAdapter.Fill(customerDataSet, "Orders")
orderDetailsAdapter.Fill(customerDataSet, "OrderDetails")

' Create relationships.
customerDataSet.Relations.Add("CustomerOrders", _
customerDataSet.Tables("Customers").Columns("CustomerID"), _
customerDataSet.Tables("Orders").Columns("CustomerID"))

customerDataSet.Relations.Add("OrderDetails", _
customerDataSet.Tables("Orders").Columns("OrderID"), _
customerDataSet.Tables("OrderDetails").Columns("OrderID"))

' Bind to the DataGrids.
Me.DataGrid1.SetDataBinding(customerDataSet, _
"Customers")
Me.DataGrid2.SetDataBinding(customerDataSet, _
"Customers.CustomerOrders")
Me.DataGrid3.SetDataBinding(customerDataSet, _
"Customers.CustomerOrders.OrderDetails")

Notice that the first grid is bound to Customers. The second grid is bound
to Orders that are related to Customers, the "Customers.CustomerOrders".
While the third grid is bound to OrderDetails that are related to Orders
that are related to Customers, the "Customers.CustomerOrders.OrderDetails".

When the selection changes in the first grid, the contents of the second two
grids will change. When the selection of the second grid changes, the
contents of the third grid changes.

Hope this helps
Jay

 

[John Yuan]

Hi, Jay,
It really help me a lot. Thank you

yuanh23,
The following sample using 3 grids uses the Northwind Sample SQL Server
database:

customerAdapter.Fill(customerDataSet, "Customers")
orderAdapter.Fill(customerDataSet, "Orders")
orderDetailsAdapter.Fill(customerDataSet, "OrderDetails")

' Create relationships.
customerDataSet.Relations.Add("CustomerOrders", _
customerDataSet.Tables("Customers").Columns("CustomerID"), _
customerDataSet.Tables("Orders").Columns("CustomerID"))

customerDataSet.Relations.Add("OrderDetails", _
customerDataSet.Tables("Orders").Columns("OrderID"), _
customerDataSet.Tables("OrderDetails").Columns("OrderID"))

' Bind to the DataGrids.
Me.DataGrid1.SetDataBinding(customerDataSet, _
"Customers")
Me.DataGrid2.SetDataBinding(customerDataSet, _
"Customers.CustomerOrders")
Me.DataGrid3.SetDataBinding(customerDataSet, _
"Customers.CustomerOrders.OrderDetails")

Notice that the first grid is bound to Customers. The second grid is bound
to Orders that are related to Customers, the "Customers.CustomerOrders".
While the third grid is bound to OrderDetails that are related to Orders
that are related to Customers, the "Customers.CustomerOrders.OrderDetails".

When the selection changes in the first grid, the contents of the second two
grids will change. When the selection of the second grid changes, the
contents of the third grid changes.

Hope this helps
Jay