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
Create a Windows Control Library project
- Start Visual Studio .NET.
- On the File menu, point to New, and then click Project. The New Project dialog box appears.
- Under Project Types, click Visual Basic Projects.
- Under Templates, click Windows Control Library.
- In the Name box, type DataGridControl, and then click OK. By default, a user control that is named UserControl1 is created.
- On the View menu, click Solution Explorer.
- In Solution Explorer, right-click UserControl1.vb, and then click Rename.
- Rename the UserControl1.vb file as DataGridControlVB.vb.
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 "Windows Form Designer generated code" 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("NewTable") CurrentDataGridCellLocation = New DataGridCell SummaryCols = New ArrayList
Locate the following code:
#End Region
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
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
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:
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
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
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
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
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:
In the Form1.vb file, locate the following code:
End Class
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
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
Save the DataGridControlVB.vb file, and then build the DataGrid control
- On the File menu, click Save DataGridControlVB.vb As. The Save File As dialog box appears.
- 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.
- Click Yes. The Advanced Save Options dialog box appears.
- In the Encoding box, select Unicode (UTF-8 with signature) - Codepage 65001, and then click OK.
- On the Build menu, click Build DataGridControl to build the DataGridControl.dll assembly.
Create a Windows Application project that uses the DataGrid control
- In Solution Explorer, right-click the DataGridControl solution, point to Add, and then click New Project. The Add New Project dialog box appears.
- Under Project Types, click Visual Basic Projects.
- Under Templates, click Windows Application.
- In the Name box, type TestApplication, and then click OK. By default, a Windows Form that is named Form1 is created.
- In Solution Explorer, right-click TestApplication, and then click Set as StartUp Project.
- On the View menu, click Toolbox.
- 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.
- On the .NET Framework Components tab, click Browse. The Open dialog box appears.
- 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.
- Click Open, and then click OK. The DataGridControlVB control is added to the Toolbox.
- In the Toolbox, double-click the DataGridControlVB control to add the DataGridControlVB1 control to the Form1 form.
- Click Form1.
- On the View menu, click Properties Window to view the Properties window for the Form1 form.
- Set the Size property to 450, 200.
- In the Design view of the Form1 form, click the DataGridControlVB1 control.
- On the View menu, click Properties Window to view the Properties window for the DataGridControlVB1 control.
- Set the Size property to 420, 115.
- In Solution Explorer, right-click Form1.vb, and then click View Code.
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
Locate the following code:
Public Class Form1 Inherits System.Windows.Forms.Form
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
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
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
Build and then run your application
- On the Build menu, click Build Solution.
- 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.
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:
Keywords: kbvs2005doesnotapply kbvs2005swept kbvs2005applies kbwindowsforms kbdatabinding kbdataadapter kbclientserver kbbrush kbuser kbevent kbsample kbcode kbhowtomaster KB836672