Microsoft KB Archive/836672

= How to sum the fields in a Windows Forms DataGrid control and then display the calculated totals in a footer by using Visual Basic .NET =

Article ID: 836672

Article Last Modified on 5/31/2007

-

APPLIES TO


 * Microsoft Visual Basic .NET 2003 Standard Edition
 * Microsoft Visual Basic .NET 2002 Standard Edition

-



SUMMARY
''To sum the fields in a Microsoft Windows Forms DataGrid control and then to display the calculated totals in a footer, you must first create a user control that inherits from the System.Windows.Forms.DataGrid class. Then you must handle the events that are raised when a cell in this user control is changed.

You must disable the default sorting feature of the DataGrid control to prevent the footer row from being sorted. To implement custom sorting for your DataGrid control, you must handle the MouseDown event.

You must also disable the footer row of the DataGrid control to prevent users from editing the cells of the footer row. To provide data for the event that prevents users from editing the cells of the DataGrid control, you must define an event arguments class, and then you must define a class that contains methods to paint and to disable the footer row.

To sum the fields and to display the calculated totals in a footer, build the DataGrid control, add an instance of the DataGrid control to a Windows Application project, bind the DataGrid control to the related data, and then build and run the application.''



IN THIS TASK

 * INTRODUCTION
 * Requirements
 * Create a Windows Control Library project
 * Inherit from the Windows Forms DataGrid control, and then add variables, properties, and methods
 * Handle the events that are raised when a cell in the DataGrid control is changed
 * Handle the MouseDown event of the DataGrid control to implement custom sorting
 * Disable the footer row of the DataGrid control
 * Define an event arguments class that provides data for the DataGridDisableCell event
 * Define a class that contains methods to paint and to disable the footer row
 * Save the DataGridControlVB.vb file, and then build the DataGrid control
 * Create a Windows Application project that uses the DataGrid control
 * Bind the custom DataGrid control to the related data
 * Complete Code Listing
 * Build and then run your application
 * REFERENCES



INTRODUCTION
This step-by-step article describes how to sum the fields in a Windows Forms DataGrid control by using Microsoft Visual Basic .NET. This article also describes how to customize the Windows Forms DataGrid control to display the calculated totals in a footer.

back to the top

Requirements
This article assumes that you are familiar with the following topics:
 * The Windows Forms DataGrid control
 * Data binding by using Windows Forms and Microsoft ADO.NET
 * Handling and raising events

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:
 * Microsoft Windows 2000, Microsoft Windows XP, or Microsoft Windows Server 2003
 * Microsoft Visual Studio .NET

back to the top

Create a Windows Control Library project

 * 1) Start Visual Studio .NET.
 * 2) On the File menu, point to New, and then click Project. The New Project dialog box appears.
 * 3) Under Project Types, click Visual Basic Projects.
 * 4) Under Templates, click Windows Control Library.
 * 5) In the Name box, type DataGridControl, and then click OK. By default, a user control that is named UserControl1 is created.
 * 6) On the View menu, click Solution Explorer.
 * 7) In Solution Explorer, right-click UserControl1.vb, and then click Rename.
 * 8) Rename the UserControl1.vb file as DataGridControlVB.vb.

back to the top

Inherit from the Windows Forms DataGrid control, and then add variables, properties, and methods
 In Solution Explorer, right-click DataGridControlVB.vb, and then click View Code.  Import the required namespaces, and then declare a delegate for the event that disables the cells of the DataGrid control. To do this, add the following code at the top of the code window: Imports System Imports System.Collections Imports System.ComponentModel Imports System.Drawing Imports System.Data Imports System.Data.SqlClient Imports System.Windows.Forms Imports System.Text

' Declare a delegate for the event that disables the cells of the DataGrid control. Public Delegate Sub DataGridDisableCellEventHandler _ (ByVal sender As System.Object, ByVal e As DataGridDisableCellEventArgs)   Locate the following code: Public Class UserControl1 Inherits System.Windows.Forms.UserControl   Make your Windows Forms DataGrid control inherit from the System.Windows.Forms.DataGrid class. To do this, replace the code that you located in step 3 with the following code: Public Class DataGridControlVB Inherits System.Windows.Forms.DataGrid   Add the following variable declarations after the code that you added in step 4: ' Declare private variables for your DataGrid control. Private RowCount As Integer Private ColCount As Integer Private SortedColNum As Integer

Private Ascending As Boolean Private CellValueChanged As Boolean

Private SourceTable As String

Private MyDataView As DataView Private MyDataSet As DataSet Private MyDataRow As DataRow

Private SummaryCols As ArrayList Private CurrentDataGridCellLocation As DataGridCell

Private WithEvents MyDataTable As DataTable

Private Shared FooterBackColor As Brush Private Shared FooterForeColor As Brush   Locate the following code comment in the &quot;Windows Form Designer generated code&quot; area: 'Add any initialization after the InitializeComponent call   Perform custom initialization in the constructor of your Windows Forms DataGrid control. To do this, replace the code comment that you located in step 6 with the following code: RowCount = 0 ColCount = 0

CellValueChanged = False Ascending = False

MyDataRow = Nothing MyDataTable = New DataTable(&quot;NewTable&quot;) CurrentDataGridCellLocation = New DataGridCell

SummaryCols = New ArrayList </li>  Locate the following code:  Add the following property definitions after the code that you located in step 8: Public WriteOnly Property GridDataSet As DataSet Set(ByVal Value As DataSet) MyDataSet = Value End Set End Property
 * 1) End Region </li>

Public Property SummaryColumns As ArrayList Get Return SummaryCols End Get Set(ByVal Value As ArrayList) SummaryCols = Value End Set End Property

Public Property DataSourceTable As String Get Return SourceTable End Get Set(ByVal Value As String) SourceTable = Value End Set End Property

Public Shared Property FooterColor As Brush Get Return FooterBackColor End Get Set(ByVal Value As Brush) FooterBackColor = Value End Set End Property

Public Shared Property FooterFontColor As Brush Get Return FooterForeColor End Get Set(ByVal Value As Brush) FooterForeColor = Value End Set End Property </li>  Disable the default sorting feature of the DataGrid control to prevent the footer row from being sorted. To do this, and to bind the custom DataGrid control to the related data, add the following code after the code that you added in step 9: Public Sub BindDataGrid MyDataTable = MyDataSet.Tables(0) MyDataView = MyDataTable.DefaultView Me.DataSource = MyDataView

Dim TableStyle As DataGridTableStyle = New DataGridTableStyle TableStyle.MappingName = SourceTable

' Add a Boolean data type column to the DataTable object. ' You can use this column during your custom sorting. MyDataTable.Columns.Add(&quot;ID&quot;, System.Type.GetType(&quot;System.Boolean&quot;)) MyDataTable.Columns(&quot;ID&quot;).DefaultValue = False MyDataTable.Columns(&quot;ID&quot;).ColumnMapping = MappingType.Hidden ColCount = MyDataTable.Columns.Count

' Create a footer row for the DataTable object. MyDataRow = MyDataTable.NewRow

' Set the footer value as an empty string for all columns that contains string values. Dim MyIterator As Integer For MyIterator = 0 To ColCount - 1 If (MyDataTable.Columns(MyIterator).DataType.ToString = &quot;System.String&quot;) Then MyDataRow(MyIterator) = &quot;&quot; End If     Next

' Add the footer row to the DataTable object. MyDataTable.Rows.Add(MyDataRow) RowCount = MyDataTable.Rows.Count

' Add a MyDataGridTextBox control to each cell of the DataGrid control. Dim TempDataGridTextBox As MyDataGridTextBox For MyIterator = 0 To ColCount - 2 TempDataGridTextBox = New MyDataGridTextBox(MyIterator) TempDataGridTextBox.HeaderText = MyDataTable.Columns(MyIterator).ColumnName TempDataGridTextBox.MappingName = MyDataTable.Columns(MyIterator).ColumnName AddHandler TempDataGridTextBox.DataGridDisableCell, _ New DataGridDisableCellEventHandler(AddressOf SetEnableValues)

' Disable the default sorting feature of the DataGrid control. TableStyle.AllowSorting = False TableStyle.GridColumnStyles.Add(TempDataGridTextBox) Next

Me.TableStyles.Add(TableStyle) Me.DataSource = MyDataView MyDataView.ApplyDefaultSort = False MyDataView.AllowNew = False

' Set the value of the footer cell. Dim MyCell As DataGridCell = New DataGridCell MyCell.RowNumber = MyDataTable.Rows.Count - 1

' Calculate the value for each of the cells in the footer. Dim MyArray(2) As String Dim MyString As String For Each MyString In SummaryCols MyArray = MyString.Split(&quot;,&quot;c) MyCell.ColumnNumber = Convert.ToInt32(MyArray(0)) Me(MyCell) = MyDataTable.Compute(MyArray(1), &quot;ID is null&quot;).ToString Next

End Sub </li></ol>

back to the top

Handle the events that are raised when a cell in the DataGrid control is changed
You must handle the ColumnChanged event of the DataTable object, and then you must handle the CurrentCellChanged event of the DataGrid control to track when a cell value in the DataGrid control is changed. To do this, follow these steps: <ol>  Handle the ColumnChanged event of the DataTable object. To do this, add the following code after the code that you added in step 10 of the &quot;Inherit from the Windows Forms DataGrid control, and then add variables, properties, and methods&quot; section: ' Handle the DataTable object's ColumnChanged event ' to track whether the value in a cell has changed. Private Sub MyDataTable_ColumnChanged(ByVal sender As Object, _     ByVal e As System.Data.DataColumnChangeEventArgs) Handles MyDataTable.ColumnChanged

Dim Row As Integer, Col As Integer Row = 0 Col = 0

' Determine the row that contains the changed cell. Dim TempDataRow As DataRow For Each TempDataRow In MyDataTable.Rows If (TempDataRow.Equals(e.Row)) Then CurrentDataGridCellLocation.RowNumber = Row CellValueChanged = True Exit For Row = Row + 1 End If     Next

' Determine the column that contains the changed cell. Dim TempDataColumn As DataColumn For Each TempDataColumn In MyDataTable.Columns If (TempDataColumn.Equals(e.Column)) Then CurrentDataGridCellLocation.ColumnNumber = Col CellValueChanged = True Exit For Col = Col + 1 End If     Next End Sub </li>  Handle the CurrentCellChanged event of the DataGrid control. To do this, add the following code after the code that you added in step 1: ' Handle the CurrentCellChanged event of the DataGrid control. Private Sub DataGridControlVB_CurrentCellChanged(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles MyBase.CurrentCellChanged

If (CellValueChanged = True) Then Dim MyCell As DataGridCell = New DataGridCell MyCell.RowNumber = MyDataTable.Rows.Count - 1

' Calculate the value for each cell in the footer. Dim MyArray(2) As String Dim MyString As String For Each MyString In SummaryCols MyArray = MyString.Split(&quot;,&quot;) MyCell.ColumnNumber = Convert.ToInt32(MyArray(0)) Me(MyCell) = MyDataTable.Compute(MyArray(1), &quot;ID is null&quot;).ToString Next End If

CellValueChanged = False End Sub </li></ol>

back to the top

Handle the MouseDown event of the DataGrid control to implement custom sorting
Because you have disabled the default sorting feature of the DataGrid control, you must perform custom sorting when a user clicks a column header. You must handle the MouseDown event of the DataGrid control to implement custom sorting.

To do this, add the following event handler after the code that you added in step 2 of the &quot;Handle the events that are raised when a cell in the DataGrid control is changed&quot; section: ' Handle the MouseDown event to perform custom sorting. Private Sub DataGridControlVB_MouseDown(ByVal sender As Object, _     ByVal e As System.Windows.Forms.MouseEventArgs) Handles MyBase.MouseDown

Dim MyHitTestInfo As DataGrid.HitTestInfo MyHitTestInfo = Me.HitTest(e.X, e.Y)

Dim ColName As String

If (MyHitTestInfo.Type = DataGrid.HitTestType.ColumnHeader) Then Dim ColNum As Integer = MyHitTestInfo.Column

If (ColNum <> -1) Then ColName = MyDataTable.Columns(ColNum).ColumnName

' Perform custom sorting. To do this, always sort the Boolean data type column in           ' ascending order so that the footer row stays at the end. Dim MyChar As Char = {&quot;↑&quot;c, &quot;↓&quot;c}

Dim NewString As String = _ Me.TableStyles(0).GridColumnStyles(SortedColNum).HeaderText.TrimEnd(MyChar).Trim Me.TableStyles(0).GridColumnStyles(SortedColNum).HeaderText = NewString

If (Ascending = True) Then MyDataView.Sort = &quot;ID Asc,&quot; + ColName + &quot; desc&quot; Ascending = False Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText = _ Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText + &quot; ↑&quot; SortedColNum = ColNum Else MyDataView.Sort = &quot;ID Asc,&quot; + ColName + &quot; asc&quot; Ascending = True Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText = _ Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText + &quot; ↓&quot; SortedColNum = ColNum End If        End If      End If   End Sub back to the top

Disable the footer row of the DataGrid control
To disable the footer row of the DataGrid control, add the following code after the code that you added in the &quot;Handle the MouseDown event of the DataGrid control to implement custom sorting&quot; section: ' Disable the footer row of the DataGrid control. Public Sub SetEnableValues(ByVal sender As Object, ByVal e As DataGridDisableCellEventArgs) If (e.Row = RowCount - 1) Then e.EnableValue = False Else e.EnableValue = True End If  End Sub back to the top

Define an event arguments class that provides data for the DataGridDisableCell event
To define an event arguments class that provides data for the DataGridDisableCell event, follow these steps: <ol>  In the Form1.vb file, locate the following code: End Class </li>  Add the following code after the code that you located in step 1: ' Define a custom event arguments class that inherits from the EventArgs class. Public Class DataGridDisableCellEventArgs Inherits EventArgs

Private MyCol As Integer Private MyRow As Integer Private MyEnableValue As Boolean

Public Sub New(ByVal Row As Integer, ByVal Col As Integer) MyRow = Row MyCol = Col MyEnableValue = True End Sub

Public Property Column As Integer Get Return MyCol End Get Set(ByVal Value As Integer) MyCol = Value End Set End Property

Public Property Row As Integer Get Return MyRow End Get Set(ByVal Value As Integer) MyRow = Value End Set End Property

Public Property EnableValue As Boolean Get Return MyEnableValue End Get Set(ByVal Value As Boolean) MyEnableValue = Value End Set End Property

End Class </li></ol>

back to the top

Define a class that contains methods to paint and to disable the footer row
To define a class that contains methods to paint and to disable the footer row, add the following code after the code that you added in step 2 of the &quot;Define an event arguments class that provides data for the DataGridDisableCell event&quot; section: Public Class MyDataGridTextBox Inherits DataGridTextBoxColumn

' Declare an event for the DataGridDisableCellEventHandler delegate that you have defined. Public Event DataGridDisableCell As DataGridDisableCellEventHandler

Private MyCol As Integer

' Save the column number of the column to add the MyDataGridTextBox control to. Public Sub New(ByVal Column As Integer) MyCol = Column End Sub

' Override the Paint method to set colors for the footer row. Protected Overloads Overrides Sub Paint(ByVal g As System.Drawing.Graphics, _     ByVal bounds As System.Drawing.Rectangle, ByVal source As System.Windows.Forms.CurrencyManager, _         ByVal rowNum As Integer, ByVal backBrush As System.Drawing.Brush, _            ByVal foreBrush As System.Drawing.Brush, ByVal alignToRight As Boolean)

' Initialize the event arguments by using the number ' of the current row and the current column. Dim e As New DataGridDisableCellEventArgs(rowNum, MyCol)

' Raise the DataGridDisableCell event. RaiseEvent DataGridDisableCell(Me, e)

' Set the foreground color and the background color for the footer row. If Not e.EnableValue Then If DataGridControlVB.FooterColor Is Nothing _ Or DataGridControlVB.FooterFontColor Is Nothing Then backBrush = Brushes.White foreBrush = Brushes.Black Else backBrush = DataGridControlVB.FooterColor foreBrush = DataGridControlVB.FooterFontColor End If     End If

' Call the Paint event of the DataGridTextBoxColumn class. MyBase.Paint(g, bounds, source, rowNum, backBrush, foreBrush, alignToRight) End Sub

' Override the Edit method to disable the footer row. Protected Overloads Overrides Sub Edit(ByVal source As System.Windows.Forms.CurrencyManager, _     ByVal rowNum As Integer, ByVal bounds As System.Drawing.Rectangle, ByVal readOnlyFlag As Boolean, _         ByVal instantText As String, ByVal cellIsVisible As Boolean)

Dim e As DataGridDisableCellEventArgs = Nothing

' Initialize the event arguments by using the number ' of the current row and the current column. e = New DataGridDisableCellEventArgs(rowNum, MyCol)

' Raise the DataGridDisableCell event. RaiseEvent DataGridDisableCell(Me, e)

' Call the Edit event of the DataGridTextBoxColumn ' class for all rows other than the footer row. If e.EnableValue Then MyBase.Edit(source, rowNum, bounds, readOnlyFlag, instantText, cellIsVisible) End If  End Sub End Class back to the top

Save the DataGridControlVB.vb file, and then build the DataGrid control

 * 1) On the File menu, click Save DataGridControlVB.vb As. The Save File As dialog box appears.
 * 2) Click the arrow next to the Save button, and then click Save with Encoding. You receive a message to replace the existing DataGridControlVB.vb file.
 * 3) Click Yes. The Advanced Save Options dialog box appears.
 * 4) In the Encoding box, select Unicode (UTF-8 with signature) - Codepage 65001, and then click OK.
 * 5) On the Build menu, click Build DataGridControl to build the DataGridControl.dll assembly.

back to the top

Create a Windows Application project that uses the DataGrid control
<ol> In Solution Explorer, right-click the DataGridControl solution, point to Add, and then click New Project. The Add New Project dialog box appears.</li> Under Project Types, click Visual Basic Projects.</li> Under Templates, click Windows Application.</li> In the Name box, type TestApplication, and then click OK. By default, a Windows Form that is named Form1 is created.</li> In Solution Explorer, right-click TestApplication, and then click Set as StartUp Project.</li> On the View menu, click Toolbox.</li> Do one of the following, depending on the version of Visual Studio .NET that you have: <ul> If you are using Visual Studio .NET 2003, click Add/Remove Toolbox Items on the Tools menu.</li> <li>If you are using Visual Studio .NET 2002, click Customize Toolbox on the Tools menu.</li></ul>

The Customize Toolbox dialog box appears.</li> <li>On the .NET Framework Components tab, click Browse. The Open dialog box appears.</li> <li>Locate and then click the DataGridControl.dll assembly that you created in step 5 of the &quot;Save the DataGridControlVB.vb file, and then build the DataGrid control&quot; section.</li> <li>Click Open, and then click OK. The DataGridControlVB control is added to the Toolbox.</li> <li>In the Toolbox, double-click the DataGridControlVB control to add the DataGridControlVB1 control to the Form1 form.</li> <li>Click Form1.</li> <li>On the View menu, click Properties Window to view the Properties window for the Form1 form.</li> <li>Set the Size property to 450, 200.</li> <li>In the Design view of the Form1 form, click the DataGridControlVB1 control.</li> <li>On the View menu, click Properties Window to view the Properties window for the DataGridControlVB1 control.</li> <li>Set the Size property to 420, 115.</li></ol>

back to the top

Bind the custom DataGrid control to the related data
<ol> <li>In Solution Explorer, right-click Form1.vb, and then click View Code.</li> <li> Import the required namespaces. To do this, add the following code at the top of the code window: Imports System.Data Imports System.Data.SqlClient </li> <li> Locate the following code: Public Class Form1 Inherits System.Windows.Forms.Form </li> <li> Add the following variable declarations after the code that you located in step 3: Dim MyConnString As String Dim MyDataSet As DataSet = Nothing

Dim MyDataAdapter As SqlDataAdapter = Nothing Dim MyConn As SqlConnection </li> <li> Bind the DataGrid control to the related data. To do this, add the following code after the &quot;Windows Form Designer generated code&quot; area.

Note In the following code, replace  with the appropriate value for an instance of Microsoft SQL Server: Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim SqlString As String = &quot;SELECT * FROM discounts&quot;

MyConnString = &quot;server=<ServerName>;Integrated Security=SSPI;database=pubs&quot; MyConn = New SqlConnection(MyConnString) MyDataAdapter = New SqlDataAdapter(SqlString, MyConn) MyDataSet = New DataSet

Try MyDataAdapter.Fill(MyDataSet, &quot;discounts&quot;) ' Specify the dataset that you want your DataGrid control to use. DataGridControlVB1.GridDataSet = MyDataSet ' Specify the source table that you want your DataGrid control to use. DataGridControlVB1.DataSourceTable = &quot;discounts&quot;

' Add the columns that you want to sum to an array list. Use the following format: ' &quot;<ColumnNumber>,summing expression&quot; Dim Summary As New ArrayList Summary.Add(&quot;2,sum(lowqty)&quot;) Summary.Add(&quot;3,sum(highqty)&quot;) ' Map the array list to the SummaryColumns property of your DataGrid control. DataGridControlVB1.SummaryColumns = Summary

' Set the foreground color and the background color for the footer row. DataGridControlVB1.FooterColor = Brushes.BlueViolet DataGridControlVB1.FooterFontColor = Brushes.White

' Bind the DataGrid control to the related data. DataGridControlVB1.BindDataGrid

' Dispose the data adapter, and then close the connection.

Catch DatabaseException As SqlException MessageBox.Show(&quot;Database exception: &quot; & DatabaseException.Message) Catch OtherException As Exception MessageBox.Show(OtherException.Message) Finally MyDataAdapter.Dispose MyConn.Dispose End Try End Sub </li></ol>

back to the top

DataGridControlVB.vb
Imports System Imports System.Collections Imports System.ComponentModel Imports System.Drawing Imports System.Data Imports System.Data.SqlClient Imports System.Windows.Forms Imports System.Text

' Declare a delegate for the event that disables the cells of the DataGrid control. Public Delegate Sub DataGridDisableCellEventHandler _ (ByVal sender As System.Object, ByVal e As DataGridDisableCellEventArgs)

Public Class DataGridControlVB Inherits System.Windows.Forms.DataGrid

' Declare private variables for your DataGrid control. Private RowCount As Integer Private ColCount As Integer Private SortedColNum As Integer

Private Ascending As Boolean Private CellValueChanged As Boolean

Private SourceTable As String

Private MyDataView As DataView Private MyDataSet As DataSet Private MyDataRow As DataRow

Private SummaryCols As ArrayList Private CurrentDataGridCellLocation As DataGridCell

Private WithEvents MyDataTable As DataTable

Private Shared FooterBackColor As Brush Private Shared FooterForeColor As Brush


 * 1) Region &quot; Windows Form Designer generated code &quot;

Public Sub New MyBase.New

'This call is required by the Windows Form Designer. InitializeComponent

RowCount = 0 ColCount = 0

CellValueChanged = False Ascending = False

MyDataRow = Nothing MyDataTable = New DataTable(&quot;NewTable&quot;) CurrentDataGridCellLocation = New DataGridCell

SummaryCols = New ArrayList

End Sub

'UserControl1 overrides dispose to clean up the component list. Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean) If disposing Then If Not (components Is Nothing) Then components.Dispose End If     End If      MyBase.Dispose(disposing) End Sub

'Required by the Windows Form Designer Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form Designer 'It can be modified using the Windows Form Designer. 'Do not modify it using the code editor. <System.Diagnostics.DebuggerStepThrough> Private Sub InitializeComponent components = New System.ComponentModel.Container End Sub


 * 1) End Region

Public WriteOnly Property GridDataSet As DataSet Set(ByVal Value As DataSet) MyDataSet = Value End Set End Property

Public Property SummaryColumns As ArrayList Get Return SummaryCols End Get Set(ByVal Value As ArrayList) SummaryCols = Value End Set End Property

Public Property DataSourceTable As String Get Return SourceTable End Get Set(ByVal Value As String) SourceTable = Value End Set End Property

Public Shared Property FooterColor As Brush Get Return FooterBackColor End Get Set(ByVal Value As Brush) FooterBackColor = Value End Set End Property

Public Shared Property FooterFontColor As Brush Get Return FooterForeColor End Get Set(ByVal Value As Brush) FooterForeColor = Value End Set End Property

Public Sub BindDataGrid MyDataTable = MyDataSet.Tables(0) MyDataView = MyDataTable.DefaultView Me.DataSource = MyDataView

Dim TableStyle As DataGridTableStyle = New DataGridTableStyle TableStyle.MappingName = SourceTable

' Add a Boolean data type column to the DataTable object. ' You can use this column during your custom sorting. MyDataTable.Columns.Add(&quot;ID&quot;, System.Type.GetType(&quot;System.Boolean&quot;)) MyDataTable.Columns(&quot;ID&quot;).DefaultValue = False MyDataTable.Columns(&quot;ID&quot;).ColumnMapping = MappingType.Hidden ColCount = MyDataTable.Columns.Count

' Create a footer row for the DataTable object. MyDataRow = MyDataTable.NewRow

' Set the footer value as an empty string for all columns that contains string values. Dim MyIterator As Integer For MyIterator = 0 To ColCount - 1 If (MyDataTable.Columns(MyIterator).DataType.ToString = &quot;System.String&quot;) Then MyDataRow(MyIterator) = &quot;&quot; End If     Next

' Add the footer row to the DataTable object. MyDataTable.Rows.Add(MyDataRow) RowCount = MyDataTable.Rows.Count

' Add a MyDataGridTextBox control to each cell of the DataGrid control. Dim TempDataGridTextBox As MyDataGridTextBox For MyIterator = 0 To ColCount - 2 TempDataGridTextBox = New MyDataGridTextBox(MyIterator) TempDataGridTextBox.HeaderText = MyDataTable.Columns(MyIterator).ColumnName TempDataGridTextBox.MappingName = MyDataTable.Columns(MyIterator).ColumnName AddHandler TempDataGridTextBox.DataGridDisableCell, _ New DataGridDisableCellEventHandler(AddressOf SetEnableValues)

' Disable the default sorting feature of the DataGrid control. TableStyle.AllowSorting = False TableStyle.GridColumnStyles.Add(TempDataGridTextBox) Next

Me.TableStyles.Add(TableStyle) Me.DataSource = MyDataView MyDataView.ApplyDefaultSort = False MyDataView.AllowNew = False

' Set the value of the footer cell. Dim MyCell As DataGridCell = New DataGridCell MyCell.RowNumber = MyDataTable.Rows.Count - 1

' Calculate the value for each of the cells in the footer. Dim MyArray(2) As String Dim MyString As String For Each MyString In SummaryCols MyArray = MyString.Split(&quot;,&quot;c) MyCell.ColumnNumber = Convert.ToInt32(MyArray(0)) Me(MyCell) = MyDataTable.Compute(MyArray(1), &quot;ID is null&quot;).ToString Next

End Sub

' Handle the DataTable object's ColumnChanged event ' to track if the value in a cell has changed. Private Sub MyDataTable_ColumnChanged(ByVal sender As Object, _     ByVal e As System.Data.DataColumnChangeEventArgs) Handles MyDataTable.ColumnChanged

Dim Row As Integer, Col As Integer Row = 0 Col = 0

' Determine the row that contains the changed cell. Dim TempDataRow As DataRow For Each TempDataRow In MyDataTable.Rows If (TempDataRow.Equals(e.Row)) Then CurrentDataGridCellLocation.RowNumber = Row CellValueChanged = True Exit For Row = Row + 1 End If     Next

' Determine the column that contains the changed cell. Dim TempDataColumn As DataColumn For Each TempDataColumn In MyDataTable.Columns If (TempDataColumn.Equals(e.Column)) Then CurrentDataGridCellLocation.ColumnNumber = Col CellValueChanged = True Exit For Col = Col + 1 End If     Next End Sub

' Handle the CurrentCellChanged event of the DataGrid control. Private Sub DataGridControlVB_CurrentCellChanged(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles MyBase.CurrentCellChanged

If (CellValueChanged = True) Then Dim MyCell As DataGridCell = New DataGridCell MyCell.RowNumber = MyDataTable.Rows.Count - 1

' Calculate the value for each of the cells in the footer. Dim MyArray(2) As String Dim MyString As String For Each MyString In SummaryCols MyArray = MyString.Split(&quot;,&quot;) MyCell.ColumnNumber = Convert.ToInt32(MyArray(0)) Me(MyCell) = MyDataTable.Compute(MyArray(1), &quot;ID is null&quot;).ToString Next End If

CellValueChanged = False End Sub

' Handle the MouseDown event to perform custom sorting. Private Sub DataGridControlVB_MouseDown(ByVal sender As Object, _     ByVal e As System.Windows.Forms.MouseEventArgs) Handles MyBase.MouseDown

Dim MyHitTestInfo As DataGrid.HitTestInfo MyHitTestInfo = Me.HitTest(e.X, e.Y)

Dim ColName As String

If (MyHitTestInfo.Type = DataGrid.HitTestType.ColumnHeader) Then Dim ColNum As Integer = MyHitTestInfo.Column

If (ColNum <> -1) Then ColName = MyDataTable.Columns(ColNum).ColumnName

' Perform custom sorting. To do this, always sort the Boolean data type column in           ' ascending order so that the footer row stays at the end. Dim MyChar As Char = {&quot;↑&quot;c, &quot;↓&quot;c}

Dim NewString As String = _ Me.TableStyles(0).GridColumnStyles(SortedColNum).HeaderText.TrimEnd(MyChar).Trim Me.TableStyles(0).GridColumnStyles(SortedColNum).HeaderText = NewString

If (Ascending = True) Then MyDataView.Sort = &quot;ID Asc,&quot; + ColName + &quot; desc&quot; Ascending = False Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText = _ Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText + &quot; ↑&quot; SortedColNum = ColNum Else MyDataView.Sort = &quot;ID Asc,&quot; + ColName + &quot; asc&quot; Ascending = True Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText = _ Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText + &quot; ↓&quot; SortedColNum = ColNum End If        End If      End If   End Sub

' Disable the footer row of the DataGrid control. Public Sub SetEnableValues(ByVal sender As Object, ByVal e As DataGridDisableCellEventArgs) If (e.Row = RowCount - 1) Then e.EnableValue = False Else e.EnableValue = True End If  End Sub

End Class

' Define a custom event arguments class that inherits from the EventArgs class. Public Class DataGridDisableCellEventArgs Inherits EventArgs

Private MyCol As Integer Private MyRow As Integer Private MyEnableValue As Boolean

Public Sub New(ByVal Row As Integer, ByVal Col As Integer) MyRow = Row MyCol = Col MyEnableValue = True End Sub

Public Property Column As Integer Get Return MyCol End Get Set(ByVal Value As Integer) MyCol = Value End Set End Property

Public Property Row As Integer Get Return MyRow End Get Set(ByVal Value As Integer) MyRow = Value End Set End Property

Public Property EnableValue As Boolean Get Return MyEnableValue End Get Set(ByVal Value As Boolean) MyEnableValue = Value End Set End Property

End Class

Public Class MyDataGridTextBox Inherits DataGridTextBoxColumn

' Declare an event for the DataGridDisableCellEventHandler delegate that you have defined. Public Event DataGridDisableCell As DataGridDisableCellEventHandler

Private MyCol As Integer

' Save the column number of the column to add the MyDataGridTextBox control to. Public Sub New(ByVal Column As Integer) MyCol = Column End Sub

' Override the Paint method to set colors for the footer row. Protected Overloads Overrides Sub Paint(ByVal g As System.Drawing.Graphics, _     ByVal bounds As System.Drawing.Rectangle, ByVal source As System.Windows.Forms.CurrencyManager, _         ByVal rowNum As Integer, ByVal backBrush As System.Drawing.Brush, _            ByVal foreBrush As System.Drawing.Brush, ByVal alignToRight As Boolean)

' Initialize the event arguments with the number ' of the current row and the current column. Dim e As New DataGridDisableCellEventArgs(rowNum, MyCol)

' Raise the DataGridDisableCell event. RaiseEvent DataGridDisableCell(Me, e)

' Set the foreground color and the background color for the footer row. If Not e.EnableValue Then If DataGridControlVB.FooterColor Is Nothing _ Or DataGridControlVB.FooterFontColor Is Nothing Then backBrush = Brushes.White foreBrush = Brushes.Black Else backBrush = DataGridControlVB.FooterColor foreBrush = DataGridControlVB.FooterFontColor End If     End If

' Call the Paint event of the DataGridTextBoxColumn class. MyBase.Paint(g, bounds, source, rowNum, backBrush, foreBrush, alignToRight) End Sub

' Override the Edit method to disable the footer row. Protected Overloads Overrides Sub Edit(ByVal source As System.Windows.Forms.CurrencyManager, _     ByVal rowNum As Integer, ByVal bounds As System.Drawing.Rectangle, ByVal readOnlyFlag As Boolean, _         ByVal instantText As String, ByVal cellIsVisible As Boolean)

Dim e As DataGridDisableCellEventArgs = Nothing

' Initialize the event arguments with the number ' of the current row and the current column. e = New DataGridDisableCellEventArgs(rowNum, MyCol)

' Raise the DataGridDisableCell event. RaiseEvent DataGridDisableCell(Me, e)

' Call the Edit event of the DataGridTextBoxColumn ' class for all rows other than the footer row. If e.EnableValue Then MyBase.Edit(source, rowNum, bounds, readOnlyFlag, instantText, cellIsVisible) End If  End Sub End Class

Form1.vb
Note In the following code, replace  with the appropriate value for an instance of Microsoft SQL Server. Imports System.Data Imports System.Data.SqlClient

Public Class Form1 Inherits System.Windows.Forms.Form

Dim MyConnString As String Dim MyDataSet As DataSet = Nothing

Dim MyDataAdapter As SqlDataAdapter = Nothing Dim MyConn As SqlConnection


 * 1) Region &quot; Windows Form Designer generated code &quot;

Public Sub New MyBase.New

'This call is required by the Windows Form Designer. InitializeComponent

'Add any initialization after the InitializeComponent call

End Sub

'Form overrides dispose to clean up the component list. Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean) If disposing Then If Not (components Is Nothing) Then components.Dispose End If     End If      MyBase.Dispose(disposing) End Sub

'Required by the Windows Form Designer Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form Designer 'It can be modified using the Windows Form Designer. 'Do not modify it using the code editor. Friend WithEvents DataGridControlVB1 As DataGridControl.DataGridControlVB <System.Diagnostics.DebuggerStepThrough> Private Sub InitializeComponent Me.DataGridControlVB1 = New DataGridControl.DataGridControlVB CType(Me.DataGridControlVB1, System.ComponentModel.ISupportInitialize).BeginInit Me.SuspendLayout '     'DataGridControlVB1 '     Me.DataGridControlVB1.DataMember = &quot;&quot; Me.DataGridControlVB1.DataSourceTable = Nothing Me.DataGridControlVB1.HeaderForeColor = System.Drawing.SystemColors.ControlText Me.DataGridControlVB1.Location = New System.Drawing.Point(0, 0) Me.DataGridControlVB1.Name = &quot;DataGridControlVB1&quot; Me.DataGridControlVB1.Size = New System.Drawing.Size(420, 115) Me.DataGridControlVB1.TabIndex = 0 '     'Form1 '     Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13) Me.ClientSize = New System.Drawing.Size(442, 173) Me.Controls.Add(Me.DataGridControlVB1) Me.Name = &quot;Form1&quot; Me.Text = &quot;Form1&quot; CType(Me.DataGridControlVB1, System.ComponentModel.ISupportInitialize).EndInit Me.ResumeLayout(False)

End Sub


 * 1) End Region

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim SqlString As String = &quot;SELECT * FROM discounts&quot;

MyConnString = &quot;server=<ServerName>;Integrated Security=SSPI;database=pubs&quot; MyConn = New SqlConnection(MyConnString) MyDataAdapter = New SqlDataAdapter(SqlString, MyConn) MyDataSet = New DataSet

Try MyDataAdapter.Fill(MyDataSet, &quot;discounts&quot;) ' Specify the dataset that you want your DataGrid control to use. DataGridControlVB1.GridDataSet = MyDataSet ' Specify the source table that you want your DataGrid control to use. DataGridControlVB1.DataSourceTable = &quot;discounts&quot;

' Add the columns that you want to sum to an array list in the following format: ' &quot;<ColumnNumber>,summing expression&quot; Dim Summary As New ArrayList Summary.Add(&quot;2,sum(lowqty)&quot;) Summary.Add(&quot;3,sum(highqty)&quot;) ' Map the array list to the SummaryColumns property of your DataGrid control. DataGridControlVB1.SummaryColumns = Summary

' Set the foreground color and the background color for the footer row. DataGridControlVB1.FooterColor = Brushes.BlueViolet DataGridControlVB1.FooterFontColor = Brushes.White

' Bind the DataGrid control to the related data. DataGridControlVB1.BindDataGrid

' Dispose the data adapter, and then close the connection.

Catch DatabaseException As SqlException MessageBox.Show(&quot;Database exception: &quot; & DatabaseException.Message) Catch OtherException As Exception MessageBox.Show(OtherException.Message) Finally MyDataAdapter.Dispose MyConn.Dispose End Try End Sub

End Class back to the top

Build and then run your application

 * 1) On the Build menu, click Build Solution.
 * 2) On the Debug menu, click Start.

The Form1 form appears. Your custom DataGrid control is present on the Form1 form. The footer row of the DataGrid control contains the sums of the values of the fields that you specified to sum. You cannot edit the footer row. However, when you change the value in any one of the cells, the corresponding footer cell is updated.

back to the top

<div class="references_section">