Microsoft KB Archive/836672

From BetaArchive Wiki

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.


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

  1. In Solution Explorer, right-click DataGridControlVB.vb, and then click View Code.
  2. 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)
  3. Locate the following code:

    Public Class UserControl1
       Inherits System.Windows.Forms.UserControl
  4. 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
  5. 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
  6. Locate the following code comment in the "Windows Form Designer generated code" area:

    'Add any initialization after the InitializeComponent() call
  7. 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("NewTable")
          CurrentDataGridCellLocation = New DataGridCell
    
          SummaryCols = New ArrayList
  8. Locate the following code:

    #End Region
  9. 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
    
       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
  10. 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("ID", System.Type.GetType("System.Boolean"))
          MyDataTable.Columns("ID").DefaultValue = False
          MyDataTable.Columns("ID").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() = "System.String") Then
                MyDataRow(MyIterator) = ""
             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(","c)
             MyCell.ColumnNumber = Convert.ToInt32(MyArray(0))
             Me(MyCell) = MyDataTable.Compute(MyArray(1), "ID is null").ToString()
          Next
    
       End Sub

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:

  1. 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 "Inherit from the Windows Forms DataGrid control, and then add variables, properties, and methods" 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
  2. 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(",")
                MyCell.ColumnNumber = Convert.ToInt32(MyArray(0))
                Me(MyCell) = MyDataTable.Compute(MyArray(1), "ID is null").ToString()
             Next
          End If
    
          CellValueChanged = False
       End Sub

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 "Handle the events that are raised when a cell in the DataGrid control is changed" 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 = {"↑"c, "↓"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 = "ID Asc," + ColName + " desc"
               Ascending = False
               Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText = _
                  Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText + " ↑"
               SortedColNum = ColNum
            Else
               MyDataView.Sort = "ID Asc," + ColName + " asc"
               Ascending = True
               Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText = _
                  Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText + " ↓"
               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 "Handle the MouseDown event of the DataGrid control to implement custom sorting" 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:

  1. In the Form1.vb file, locate the following code:

    End Class
  2. 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

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 "Define an event arguments class that provides data for the DataGridDisableCell event" 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

  1. In Solution Explorer, right-click the DataGridControl solution, point to Add, and then click New Project. The Add New Project dialog box appears.
  2. Under Project Types, click Visual Basic Projects.
  3. Under Templates, click Windows Application.
  4. In the Name box, type TestApplication, and then click OK. By default, a Windows Form that is named Form1 is created.
  5. In Solution Explorer, right-click TestApplication, and then click Set as StartUp Project.
  6. On the View menu, click Toolbox.
  7. Do one of the following, depending on the version of Visual Studio .NET that you have:
    • If you are using Visual Studio .NET 2003, click Add/Remove Toolbox Items on the Tools menu.
    • If you are using Visual Studio .NET 2002, click Customize Toolbox on the Tools menu.
    The Customize Toolbox dialog box appears.
  8. On the .NET Framework Components tab, click Browse. The Open dialog box appears.
  9. Locate and then click the DataGridControl.dll assembly that you created in step 5 of the "Save the DataGridControlVB.vb file, and then build the DataGrid control" section.
  10. Click Open, and then click OK. The DataGridControlVB control is added to the Toolbox.
  11. In the Toolbox, double-click the DataGridControlVB control to add the DataGridControlVB1 control to the Form1 form.
  12. Click Form1.
  13. On the View menu, click Properties Window to view the Properties window for the Form1 form.
  14. Set the Size property to 450, 200.
  15. In the Design view of the Form1 form, click the DataGridControlVB1 control.
  16. On the View menu, click Properties Window to view the Properties window for the DataGridControlVB1 control.
  17. Set the Size property to 420, 115.

back to the top

Bind the custom DataGrid control to the related data

  1. In Solution Explorer, right-click Form1.vb, and then click View Code.
  2. 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
  3. Locate the following code:

    Public Class Form1
       Inherits System.Windows.Forms.Form
  4. 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
  5. Bind the DataGrid control to the related data. To do this, add the following code after the "Windows Form Designer generated code" area.

    Note In the following code, replace <ServerName> 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 = "SELECT * FROM discounts"
    
          MyConnString = "server=<ServerName>;Integrated Security=SSPI;database=pubs"
          MyConn = New SqlConnection(MyConnString)
          MyDataAdapter = New SqlDataAdapter(SqlString, MyConn)
          MyDataSet = New DataSet
    
          Try
             MyDataAdapter.Fill(MyDataSet, "discounts")
             ' 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 = "discounts"
    
             ' Add the columns that you want to sum to an array list. Use the following format:
             ' "<ColumnNumber>,summing expression"
             Dim Summary As New ArrayList
             Summary.Add("2,sum(lowqty)")
             Summary.Add("3,sum(highqty)")
             ' 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("Database exception: " & DatabaseException.Message)
          Catch OtherException As Exception
             MessageBox.Show(OtherException.Message)
          Finally
             MyDataAdapter.Dispose()
             MyConn.Dispose()
          End Try
       End Sub

back to the top

Complete code listing

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

#Region " Windows Form Designer generated code "

   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("NewTable")
      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

#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("ID", System.Type.GetType("System.Boolean"))
      MyDataTable.Columns("ID").DefaultValue = False
      MyDataTable.Columns("ID").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() = "System.String") Then
            MyDataRow(MyIterator) = ""
         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(","c)
         MyCell.ColumnNumber = Convert.ToInt32(MyArray(0))
         Me(MyCell) = MyDataTable.Compute(MyArray(1), "ID is null").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(",")
            MyCell.ColumnNumber = Convert.ToInt32(MyArray(0))
            Me(MyCell) = MyDataTable.Compute(MyArray(1), "ID is null").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 = {"↑"c, "↓"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 = "ID Asc," + ColName + " desc"
               Ascending = False
               Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText = _
                  Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText + " ↑"
               SortedColNum = ColNum
            Else
               MyDataView.Sort = "ID Asc," + ColName + " asc"
               Ascending = True
               Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText = _
                  Me.TableStyles(0).GridColumnStyles(ColNum).HeaderText + " ↓"
               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 <ServerName> 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

#Region " Windows Form Designer generated code "

   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 = ""
      Me.DataGridControlVB1.DataSourceTable = Nothing
      Me.DataGridControlVB1.HeaderForeColor = System.Drawing.SystemColors.ControlText
      Me.DataGridControlVB1.Location = New System.Drawing.Point(0, 0)
      Me.DataGridControlVB1.Name = "DataGridControlVB1"
      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 = "Form1"
      Me.Text = "Form1"
      CType(Me.DataGridControlVB1, System.ComponentModel.ISupportInitialize).EndInit()
      Me.ResumeLayout(False)

   End Sub

#End Region

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

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

      Try
         MyDataAdapter.Fill(MyDataSet, "discounts")
         ' 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 = "discounts"

         ' Add the columns that you want to sum to an array list in the following format:
         ' "<ColumnNumber>,summing expression"
         Dim Summary As New ArrayList
         Summary.Add("2,sum(lowqty)")
         Summary.Add("3,sum(highqty)")
         ' 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("Database exception: " & 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

REFERENCES

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

313154 How to create a summary row for a DataGrid in ASP.NET by using Visual Basic .NET


For more information about DataGrid controls, about data update events, and about the DataTable.Compute method, visit the following Microsoft Developer Network (MSDN) Web sites:

back to the top

Keywords: kbvs2005doesnotapply kbvs2005swept kbvs2005applies kbwindowsforms kbdatabinding kbdataadapter kbclientserver kbbrush kbuser kbevent kbsample kbcode kbhowtomaster KB836672