Microsoft KB Archive/319082

= HOW TO: Extend the Windows Form DataGridTextBoxColumn to Display Data From Other Tables by Using Visual Basic .NET =

Article ID: 319082

Article Last Modified on 9/3/2003

-

APPLIES TO


 * Microsoft Visual Basic .NET 2003 Standard Edition
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft ADO.NET 1.1
 * Microsoft ADO.NET 1.0
 * Microsoft .NET Framework 1.1
 * Microsoft .NET Framework 1.0

-



This article was previously published under Q319082





For a Microsoft Visual C# .NET version of this article, see 319076.

IN THIS TASK
SUMMARY
 * Sample Application
 * Troubleshooting



SUMMARY
Use this step-by-step guide to extend the Windows Form DataGridTextBoxColumn control so that you can display data from other tables in the DataGrid, effectively joining two or more related DataTables for the purposes of display.

NOTE: This technique is not required if you use individual controls, because you can link them hierarchically.

The sample application later in this article demonstrates how to display fields from the Orders, Employees, and Customers tables in the Northwind database in a Windows Form DataGrid at the same time. The fields from the Orders table are editable; the fields from the related reference tables are not. You can select a new EmployeeID or CustomerID to change the reference table fields automatically to reflect the new value.

The JoinTextBoxColumn class must be mapped to a field in the many-side table (in this case, Orders), even though it displays a field from the reference table. The actual field does not matter. However, you cannot bind more than one column style to a field in the underlying DataView. If you run out of fields, you can add some dummy fields to the DataTable and bind to those, as demonstrated in the Form.Load code in the sample application.

back to the top

Sample Application
 Start Visual Studio .NET and create a new Visual Basic Windows Form application.  Add a reference to System.Data.SQLClient at the top of the Form1.vb file: Imports System.Data.SqlClient   Add the following DataSet variable declaration at the Form1 class level: Private ds As DataSet  Add a DataGrid control to the form.  Double-click the form background. Type or paste the following code to add a Form1_Load event handler to generate sample data and to initialize the DataGrid to use the JoinTextBoxColumn class. '   ' Read the data and set relationships between the tables. '   Dim cn As New SqlConnection(&quot;Server=localhost;uid=myuser;pwd=mypassword;Database=Northwind&quot;) Dim daCust As New SqlDataAdapter(&quot;SELECT * FROM Customers&quot;, cn) Dim daEmp As New SqlDataAdapter(&quot;SELECT * FROM Employees&quot;, cn) Dim daOrd As New SqlDataAdapter(&quot;SELECT * FROM Orders WHERE CustomerID LIKE 'B%'&quot;, cn) ds = New DataSet daCust.Fill(ds, &quot;Cust&quot;) daEmp.Fill(ds, &quot;Emp&quot;) daOrd.Fill(ds, &quot;Ord&quot;) ds.Relations.Add(&quot;CustOrd&quot;, ds.Tables!Cust.Columns!CustomerID, ds.Tables!Ord.Columns!CustomerID) ds.Relations.Add(&quot;EmpOrd&quot;, ds.Tables!Emp.Columns!EmployeeID, ds.Tables!Ord.Columns!EmployeeID)

'   ' Add dummy fields for mapping the JoinTextBoxColumn styles. ' Do this only if you are mapping more fields than ' there are in the underlying DataTable. You can map the ' JoinTextBoxColumn class to any column because it references ' fields in another table. '   With ds.Tables!Ord .Columns.Add(&quot;FirstName&quot;, GetType(String)) .Columns.Add(&quot;LastName&quot;, GetType(String)) .Columns.Add(&quot;CompanyName&quot;, GetType(String)) .Columns.Add(&quot;ContactName&quot;, GetType(String)) End With

'   ' Map fields from all three tables. Use a standard ' DataGridTextBoxColumn style for base table fields because they can ' be updated. Use the JoinTextBoxColumn style for reference table ' fields. These are read-only. '   Dim ts As New DataGridTableStyle Dim cs As DataGridColumnStyle

cs = New DataGridTextBoxColumn cs.Width = 75 cs.MappingName = &quot;OrderID&quot; cs.HeaderText = &quot;Order ID&quot; ts.GridColumnStyles.Add(cs)

cs = New DataGridTextBoxColumn cs.Width = 75 cs.MappingName = &quot;EmployeeID&quot; cs.HeaderText = &quot;Emp ID&quot; ts.GridColumnStyles.Add(cs)

cs = New JoinTextBoxColumn(&quot;EmpOrd&quot;, ds.Tables!Emp.Columns!FirstName) cs.Width = 120 cs.MappingName = &quot;FirstName&quot; cs.HeaderText = &quot;First Name&quot; ts.GridColumnStyles.Add(cs)

cs = New JoinTextBoxColumn(&quot;EmpOrd&quot;, ds.Tables!Emp.Columns!LastName) cs.Width = 120 cs.MappingName = &quot;LastName&quot; cs.HeaderText = &quot;Last Name&quot; ts.GridColumnStyles.Add(cs)

cs = New DataGridTextBoxColumn cs.Width = 75 cs.MappingName = &quot;CustomerID&quot; cs.HeaderText = &quot;Cust ID&quot; ts.GridColumnStyles.Add(cs)

cs = New JoinTextBoxColumn(&quot;CustOrd&quot;, ds.Tables!Cust.Columns!CompanyName) cs.Width = 120 cs.MappingName = &quot;CompanyName&quot; cs.HeaderText = &quot;Company&quot; ts.GridColumnStyles.Add(cs)

cs = New JoinTextBoxColumn(&quot;CustOrd&quot;, ds.Tables!Cust.Columns!ContactName) cs.Width = 120 cs.MappingName = &quot;ContactName&quot; cs.HeaderText = &quot;Contact&quot; ts.GridColumnStyles.Add(cs)

cs = New DataGridTextBoxColumn cs.Width = 75 cs.MappingName = &quot;OrderDate&quot; cs.HeaderText = &quot;Ord. Date&quot; ts.GridColumnStyles.Add(cs)

ts.MappingName = &quot;Ord&quot; DataGrid1.TableStyles.Add(ts)

DataGrid1.DataSource = ds   DataGrid1.DataMember = &quot;Ord&quot;

 Change the connection string that is being passed to the SqlConnection constructor so that it contains the correct server name, username, and password for your instance of SQL Server.</li>  Add the following class definition to the Form1.cs file. You can place it inside the Form1 class definition or outside the form definition. This code contains the JoinTextBoxColumn class that extends the DataGridTextBoxColumn class. Class JoinTextBoxColumn Inherits DataGridTextBoxColumn

' ' Store the information to get to a field in the referenced table. ' Private m_RelationName As String Private m_ParentField As DataColumn

Public Sub New(ByVal RelationName As String, ByVal ParentField As DataColumn) m_RelationName = RelationName m_ParentField = ParentField MyBase.ReadOnly = True   ' this column's base style is read only End Sub

Protected Overrides Function GetColumnValueAtRow(ByVal cm As CurrencyManager, ByVal RowNum As Integer) As Object '   ' Get the current DataRow from the CurrencyManager. ' Use the GetParentRow and the DataRelation name to get the parent row. ' Return the field value from the parent row. '   Try Dim dr As DataRow = CType(cm.List, DataView).Item(RowNum).Row Dim drParent As DataRow = dr.GetParentRow(m_RelationName) Return drParent(m_ParentField).ToString Catch Return &quot;&quot; ' handles NullReferenceException case when adding record End Try End Function

Protected Overrides Function Commit(ByVal cm As CurrencyManager, ByVal RowNum As Integer) As Boolean '   ' Dummy implementation because it is read-only. '   Return False End Function

Public Shadows ReadOnly Property [ReadOnly] As Boolean ' ' Shadow the base property so it cannot be set. ' Return TRUE so the DataGrid cannot allow edits. '   Get Return True End Get End Property End Class </li> Run the application. The DataGrid has several columns. Besides the OrderID, CustomerID, EmployeeID, and OrderDate columns in the Orders table, the DataGrid contains the FirstName and LastName fields from the Employees table and the CompanyName and ContactName fields from the Customers table.</li> Edit the data (add, delete, modify rows). The fields from the Employees and Customers tables are read-only. If you edit the EmployeeID or CustomerID fields from the Orders table, the referenced fields will reflect the change.</li></ol>

back to the top

Troubleshooting
Some common problems that you may run into when you use this code are:
 * If you give the JoinTextBoxColumn object a non-existant DataRelation name, you may receive an exception error when the DataGrid is rendered. The exception error does not identify which line of code caused the error. You can redesign the constructor of the class to accept a DataRelation object so that this type of error can be caught during the initialization phase and to isolate the problem to a specific line of code.
 * The column is not displayed unless you set the Width property. The default setting is 0 (hidden).
 * The data is not displayed unless you set the MappingName for both the DataGridTableStyle and DataGridColumnStyle objects and assign them to their respective collections. The Mapping name for the JoinTextBoxColumn object can be any field name; the actual field that is displayed is set by the constructor.
 * The column heading will be blank if you don't set the HeaderText property.

back to the top

Additional query words: DataView JOIN

Keywords: kbhowtomaster KB319082

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.