< Day Day Up > |
Try this : Drop twogrids on a form. Open the Data Environment and add the Orders and OrderDetails tables from the Northwind database. Set the Order property of each of the two tables to OrderID . Finally, drag the OrderID field from the Orders table to the OrderId entry under Indexes at the bottom of the OrderDetails cursor icon. The line that appears to connect them documents the relationship. On the form's design surface, set the top grid's RowSource to Orders and the bottom grid's RowSource to OrderDetails . Run the form. Cool, n'est-ce pas? The same trick doesn't work in Visual Basic .NET. Build the equivalent two-grid form, as shown in Figure 9.8. Figure 9.8. Scrolling through a grid and displaying related records in a second grid in Visual Basic .NET.
In order for this to work, you have to create two data adapters, using the following SQL statements in their respective wizards: DataAdapter1: SELECT * FROM Orders DataAdapter2: SELECT * FROM [Order Details] WHERE (OrderID = @OrderID) We'll do something a little different here. Datasets can contain multiple tables, so we'll use the same dataset for both orders and order details. Right-click on SQLDataAdapter1 and select Generate Dataset to create a dataset named dsOrders1 . When you do the same thing with SQLDataAdapter2 , don't change the name of the dataset ; just use the same one (see Listing 9.9). Listing 9.9. Code to Display Related Records in a Second GridPrivate Sub Form1_Load( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles MyBase.Load SqlDataAdapter1.Fill(DsOrders1, "Orders") DataGrid1.DataMember = "Orders" With SqlDataAdapter2 .SelectCommand.Parameters(0).Value = _ DsOrders1.Tables("Orders").Rows(DataGrid1.CurrentRowIndex).Item("OrderID") .Fill(DsOrders1, "[Order Details]") End With DataGrid1_CurrentCellChanged(Me, New System.EventArgs) End Sub Private Sub DataGrid1_CurrentCellChanged( _ ByVal sender As Object, _ ByVal e As System.EventArgs) _ Handles DataGrid1.CurrentCellChanged DsOrders1.Tables("[Order Details]").Clear() With SqlDataAdapter2 .SelectCommand.Parameters(0).Value = _ DsOrders1.Tables("Orders").Rows(DataGrid1.CurrentRowIndex).Item("OrderID") .Fill(DsOrders1, "[Order Details]") End With With DataGrid2 .DataSource = DsOrders1 .DataMember = "[Order Details]" End With End Sub This technique is appropriate for relatively small amounts of data. If you're working with huge tables, you should filter the primary table as well. |
< Day Day Up > |