How To Display Parent and Child Records in a Windows Forms DataGrid by Using Visual Basic .NET

This article describes how to create a Windows Form that displays a parent (or master) record and all of the related child (or detail) records by using the Northwind Customers and Orders tables. This article also describes the CurrencyManager object and its purpose.

In this article, the parent record information appears in TextBox controls, and the child record information appears in a DataGrid control. The project that you create in this article also contains Button controls so that you can browse through the records.

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:

• Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
• Microsoft SQL Server version 7.0 or 2000, or Microsoft Data Engine (MSDE) with the Northwind sample database installed
• Microsoft Visual Studio .NET

Understanding the CurrencyManager

It is important that you understand the function of the CurrencyManager object to help you follow the sample in this article. Any data source that you bind to in a Windows Forms control contains an associated CurrencyManager object. The CurrencyManager object monitors the position and otherwise supervises bindings to that data source. The form contains a CurrencyManager object for each discrete data source to which you are binding. If all of the controls on the form bind to a single source (for example, if several TextBox controls are bound to the same data table), they share the same CurrencyManager. When the controls on the form are bound to different sources, the form contains multiple CurrencyManager objects, each of which tracks which record or data element the controls are using.

Create the Project

This section describes how to create the project that demonstrates this functionality.

1. Start Visual Studio .NET, and create a new Visual Basic Windows Application project.
2. Add three TextBox controls, five Button controls, and one DataGrid control to the default form.
3. Change the Text properties of the Button controls to Fill, First, Next, Previous, and Last.
4. Switch to Code view, and add the following statement to the top of the Code window:

   Imports System.Data.SqlClient
   					

5. Add the following member variable to the Form1 class after the Inherits statement:

   Dim cm as CurrencyManager
   					

6. Switch to Form view, and double-click Fill. Add the following code to the Click event:

   ' Get the data.
   Dim con as New SqlConnection("server=<YourServer>;uid=<your_user_id>;" & _
                                "pwd=<your_password>;database=northwind")
   Dim daCust as New SqlDataAdapter("Select * from Customers", con)
   Dim daOrders as New SqlDataAdapter("Select * from Orders",con)
   Dim ds as New DataSet()
   daCust.Fill(ds,"Cust")
   daOrders.Fill(ds,"Orders")
   ds.Relations.Add("CustOrd",ds.Tables!Cust.Columns!CustomerID,_
   ds.Tables!Orders.Columns!CustomerID)
   '
   'Bind the controls.
   '
   TextBox1.DataBindings.Add("Text", ds.Tables!Cust,"CustomerID")
   TextBox2.DataBindings.Add("Text", ds.Tables!Cust,"CompanyName")
   TextBox3.DataBindings.Add("Text", ds.Tables!Cust,"ContactName")
   DataGrid1.DataSource=ds.Tables!Cust
   DataGrid1.DataMember="CustOrd"
   '
   'Initialize the CurrencyManager.
   '
   cm=CType(Me.BindingContext(ds.Tables!Cust),CurrencyManager)

7. Modify the connection string as appropriate for your environment.
8. Double-click First, and add the following code to the Click event:

   If Not (cm Is Nothing) AndAlso cm.Count Then cm.Position = 0
   					

9. Double-click Next, and add the following code to the Click event:

   If Not (cm Is Nothing) AndAlso cm.Count AndAlso cm.Position <cm.Count -1 Then cm.Position +=1
   					

10. Double-click Previous, and add the following code to the Click event:

    If Not (cm Is Nothing) AndAlso cm.Count AndAlso cm.Position >0 Then cm.Position -=1
    					

11. Double-click Last, and add the following code to the Click event:

    If Not (cm Is Nothing) AndAlso cm.Count Then cm.Position=cm.Count-1

Test the Application

1. Press the F5 key to compile and run the application. Note that the form is initially empty.
2. Click Fill to load and bind the data, and use the navigation buttons to move through the data.

For more information about the CurrencyManager object, refer to the Visual Studio .NET online help documentation.

For more general information about ADO.NET or Visual Basic .NET, refer to the following MSDN newsgroups: For more information, refer to the following Microsoft Training & Certification course:
For more information, refer to the following book: