DataGridView Tips and Tricks

The DataGridView is a very powerful windows form control for providing a user interface to tabular data. It has the initial "look and feel" of an Excel spreadsheet. You can bind a DataTable to the control so the user can easily add/delete/modify data in a database.

Note: This control is not related to the web-based DataGrid 2.0 control

Automatic Mode

Let's say that you have a Microsoft Access database that you plan to use in your program. From Visual Studio, all you'd have to do is add the MDB file to the project and it will automatically launch the Data Source Conversion Wizard. At the conclusion of this wizard, you'll have a strongly-typed dataset, a TableAdapter, and DataTable added to your project.

Next, you'll need to add a DataGridView to a form. From inside the Form Designer, you should see a very small triangle at the upper right-hand corner of the DataGridView control. Click on this triangle to select the data source that was created for you when you ran the Data Source Conversion Wizard.

Afterwards, you should have a fully-functional program that will allow you to view the data in the DataTable. However, you'll need to add a button and one line of code to be able to save changes back to the database.

Below is an example of the automatically generated form Load Event and the manually-created Update button event handler for saving the data:

Private Sub fmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
 Handles MyBase.Load
    'TODO: This line of code loads data into the 'NorthWindDataSet.Products' table.
    ' You can move, or remove it, as needed.
    Me.ProductsTableAdapter.Fill(Me.NorthWindDataSet.Products)
End Sub   

'
' Save the changes you've made via the DataGridView back to the
' underlying database
'
Private Sub bnUpdate_Click(ByVal sender As System.Object, ByVal e As _
 System.EventArgs) Handles bnUpdate.Click
    ProductsTableAdapter.Update(NorthWindDataSet.Products)
End Sub

Column Tweaks

By default, the automatically generated columns may need some tweaking. For example, all columns are aligned to the left and have have no formatting. You can perform most of these tweaks by either using the designer or via code.

Let's look at how you could perform some simple tweaks in a column that contains a Money field.

' create new style object
Dim style As New DataGridViewCellStyle

' set the alignment to the right (for numbers)
style.Alignment = DataGridViewContentAlignment.MiddleRight

' add a wee bit of padding to the right side
Dim p As New Windows.Forms.Padding
p.Right = 5
style.Padding = p

' set the monetary string format
style.Format = "$#.00"

' apply the style to the column
 DataGridView1.Columns("UnitPrice").DefaultCellStyle = style

Error Control

The major technique for detected errors in a DataGridView is by the RowValidating event. This allows you to use a "business rule" to validate the user input. For example, if the UnitsOnHand column can never be a negative number,you could write a validating rule that looks to see if the value is negative prior to committing the changes to that row.

You can use the ErrorProvider class to display the problems detected during validation. When an input error occurs, there will be a little red exclamation point at that cell location. If the user hovers over the red "eye ball" (it kinda looks like a lower-case "i") they will see the reason why the input was rejected.

Note: You should the RowValidating "e.Cancel" method with some caution, as it will "lock down" the DataGridView control, and not let the user move to any other row (or switch to any other control) until after the error is fixed.

The RowValidating event and ErrorProvider may not be able to detect all errors. For example, if a user types in alpha characters (like "abc") into a numeric field, a data conversion error occurs before Row Validation. In this case we need a "global" error checker to detect data conversion errors.

Note: See the technique below for using custom column types to prevent (rather than detect) data conversion errors.

Let's see how you could implement the RowValidating event and ErrorProvider control:

'
' Check to see if the user input meets all of the business rules before
' we move on to another row.
'
Private Sub DataGridView1_RowValidating(ByVal sender As Object, ByVal e As _
 System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles _
 DataGridView1.RowValidating
    ' Assume that text input is complete when you attempt to navigate
    ' away from the row
    DataGridView1.EndEdit()

    ' Apply some business rule.   In this case we're just checking to
    ' see if the UnitPrice is a positive number
    If DataGridView1.CurrentRow.Cells("UnitPrice").Value < 0.0 Then
        ' The text in the "eye ball"
        DataGridView1.CurrentRow.Cells("UnitPrice").ErrorText = _
         "Yikes, you can't have a negative Unit Price"
        ' this will prevent them from moving to another row until 
        ' they fix the problem
        e.Cancel = True
    Else
        ' this is required since after you fix the error, you want the
        ' error message to go away
        DataGridView1.CurrentRow.Cells("UnitPrice").ErrorText = ""
    End If
End Sub
    
'
' Catch any "global" Data Errors here
'
Private Sub dgv_DataError(ByVal sender As Object, ByVal e As _
 System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles dgvAssign.DataError
    MsgBox(e.Exception.Message, MsgBoxStyle.Information, "Data Input Error")
End Sub

Column Types

Out of the box, the DataGridView supports the following column types:

• DataGridViewTextBoxColumn
• DataGridViewCheckBoxColumn
• DataGridViewImageColumn
• DataGridViewComboBoxColumn
• DataGridViewLinkColumn

Note: Only the first 3 column types are automatically generated.

The error checking discussed in the previous section may not detect "data integrity errors" (those errors where the input violates a database foreign key, unique, or primary key constraint). Luckily, the DataGridViewComboBoxColumn control was designed to help solve this problem.

You can use a DataGridViewComboBoxColumn to act a as "look up table" to show a user friendly version of the field, rather than just a numeric equivalent. Not only does this look better, it also prevents the user from selecting a value that would violate the foreign key constraint (meaning, you can't select a numeric value that doesn't exist in the related table)

Take a look at the following example that replaces a numeric "CategoryID" value with the associated user-friendly "Category" string from another table (called "Categories").

Dim index As Integer

' find the location of the column
index = DataGridView1.Columns.IndexOf(DataGridView1.Columns("CategoryID"))

' remove the existing column
DataGridView1.Columns.RemoveAt(index)

' create a new combobox column that we use as a "lookup"
Dim dgvc As New DataGridViewComboBoxColumn
dgvc.Name = "Category"                          ' the column name (and heading)
dgvc.DataPropertyName = "CategoryID"    ' the matching column from the "base" table
dgvc.ValueMember = "CategoryID"           ' the numeric value to look up in the "child" table
dgvc.DisplayMember = "CategoryName"   ' the column value you want displayed
dgvc.DataSource = CategoriesTableAdapter.GetData()  ' the child table

' some more tweaking
dgvc.SortMode = DataGridViewColumnSortMode.Automatic

' insert the new column at the same location
DataGridView1.Columns.Insert(index, dgvc))

Now the user can select the user-friendly Category strings and also is prevented from selecting a value that's not a valid CategoryID

Custom DataGridView columns

If the 5 built-in column types is not sufficient, it is fairly easy to add a custom column type yourself. For example, you could create a new column type that supports the Masked Edit feature found in the MaskedTextBox control. This new custom column type would be very useful when trying to prevent input errors.

Let's see what's required to implement a custom column control. The source code for the custom DataGridViewMaskedEditColumn is found below. The example below shows how you could replace an existing automatically-generated "UnitsInStock" column with a new custom column that supports the Masked Edit feature.

Dim index As Integer

' find the location of the column
index = DataGridView1.Columns.IndexOf(DataGridView1.Columns("UnitsInStock"))

' remove the existing column
DataGridView1.Columns.RemoveAt(index)

' create a new custom column
Dim dgvMaskedEdit As New DataGridViewMaskedEditColumn
dgvMaskedEdit.Mask = "####"      ' this mask will allow only numbers (max of 4)
dgvMaskedEdit.ValidatingType = GetType(Integer)
dgvMaskedEdit.Name = "UnitsInStock"
dgvMaskedEdit.DataPropertyName = "UnitsInStock"

' some more tweaking
dgvMaskedEdit.SortMode = DataGridViewColumnSortMode.Automatic

' insert the new column at the same location
DataGridView1.Columns.Insert(index, dgvMaskedEdit)

Now the user will only be able to type numbers into this column. This technique provides an enhancement to the DataGridView control that further affirms the notion that you should always attempt to prevent user input errors rather than just detect them.

Documentation Links

• DataGridView Class
• ErrorProvider Class

DataGridViewMaskedEditColumn control

This custom DataGridView column control is contained in 3 classes... A Column control that inherits from DataGridViewColumn, a Cell control that inherits from DataGridViewTextBoxCell, and an EditingControl that implements the IDataGridViewEditingControl interface

The Masked Edit Column class is fairly straight forward; it only overrides one property (to ensure that the correct cell type it used). It also implements the properties that will be required later on by the MaskedTextBox control, namely the Mask, PromptChar, and ValidatingType properties.

Public Class DataGridViewMaskedEditColumn
    Inherits DataGridViewColumn

    Private pPromptChar As Char = "_"c
    Private pValidatingType As Type = GetType(String)
    Private pMask As String = ""

    Public Sub New()
        MyBase.New(New DataGridViewMaskedEditCell())
    End Sub

    Public Overrides Property CellTemplate() As DataGridViewCell
        Get
            Return MyBase.CellTemplate
        End Get
        Set(ByVal value As DataGridViewCell)
            ' Ensure that the cell used for the template is a MaskedEditCell
            If Not (value Is Nothing) And Not value.GetType().IsAssignableFrom( _
             GetType(DataGridViewMaskedEditCell)) Then
                Throw New InvalidCastException("Must be a DataGridViewMaskedEditCell")
            End If
            MyBase.CellTemplate = value
        End Set
    End Property

    '
    ' New properties required by the MaskedTextBox control
    '
    Public Property Mask() As String
        Get
            Return pMask
        End Get
        Set(ByVal value As String)
            pMask = value
        End Set
    End Property

    Public Property PromptChar() As Char
        Get
            Return pPromptChar
        End Get
        Set(ByVal value As Char)
            pPromptChar = value
        End Set
    End Property

    Public Property ValidatingType() As Type
        Get
            Return pValidatingType
        End Get
        Set(ByVal value As Type)
            pValidatingType = value
        End Set
    End Property
End Class

The next step is the Masked Edit Cell class. It overrides the InitializeEditingControl method to allow the Mask, PromptChar, and ValidatingType properties from the column class to be passed onto the Cell class. The remaining overrides are to adjust to the fact that the MaskedTextBox control supports multiple "validating types".

Public Class DataGridViewMaskedEditCell
    Inherits DataGridViewTextBoxCell

    Dim pColumn As DataGridViewMaskedEditColumn

    Public Sub New()

    End Sub

    Public Overrides Sub InitializeEditingControl(ByVal rowIndex As Integer, ByVal _
     initialFormattedValue As Object, ByVal dataGridViewCellStyle As DataGridViewCellStyle)
        MyBase.InitializeEditingControl(rowIndex, initialFormattedValue, dataGridViewCellStyle)

        pColumn = CType(Me.OwningColumn, DataGridViewMaskedEditColumn)
        Dim ctl As MaskedEditEditingControl = CType(DataGridView.EditingControl, _
         MaskedEditEditingControl)

        ' copy over the properties of the column
        If Not IsNothing(Me.Value) Then
            ctl.Text = Me.Value.ToString
        Else
            ctl.Text = ""
        End If
        ctl.ValidatingType = pColumn.ValidatingType
        ctl.Mask = pColumn.Mask
        ctl.PromptChar = pColumn.PromptChar
    End Sub

    Public Overrides ReadOnly Property EditType() As Type
        Get
            ' Return the type of the editing control that MaskedEditEditingControl uses.
            Return GetType(MaskedEditEditingControl)
        End Get
    End Property


    Public Overrides ReadOnly Property ValueType() As Type
        Get
            ' Return the type of the value that MaskedEditEditingControl contains.
            Return pColumn.ValidatingType
        End Get
    End Property


    Public Overrides ReadOnly Property DefaultNewRowValue() As Object
        Get
            Return ""
        End Get
    End Property

End Class

Lastly, is the Editing Control. It inherits from the MaskedTextBox control (to get the user interface we want) and implements the Editing Control interface so that the control works inside the DataGridView. Most of the code required to implement the methods and properties is fairly straight forward.

Class MaskedEditEditingControl
    Inherits MaskedTextBox
    Implements IDataGridViewEditingControl

    Private dataGridViewControl As DataGridView
    Private valueIsChanged As Boolean = False
    Private rowIndexNum As Integer

    Public Sub New()

    End Sub

    Public Property EditingControlFormattedValue() As Object Implements _
     IDataGridViewEditingControl.EditingControlFormattedValue
        Get
            Return Me.valueIsChanged.ToString
        End Get
        Set(ByVal value As Object)
            If TypeOf value Is [String] Then
                Me.Text = value.ToString
            End If
        End Set
    End Property

    Public Function GetEditingControlFormattedValue(ByVal context As _
     DataGridViewDataErrorContexts) As Object Implements _
     IDataGridViewEditingControl.GetEditingControlFormattedValue
        Return Me.Text
    End Function

     Public Sub ApplyCellStyleToEditingControl(ByVal dataGridViewCellStyle As _
     DataGridViewCellStyle) Implements _
     IDataGridViewEditingControl.ApplyCellStyleToEditingControl
        Me.Font = dataGridViewCellStyle.Font
        Me.ForeColor = dataGridViewCellStyle.ForeColor
        Me.BackColor = dataGridViewCellStyle.BackColor
    End Sub

     Public Property EditingControlRowIndex() As Integer Implements _
     IDataGridViewEditingControl.EditingControlRowIndex
        Get
            Return rowIndexNum
        End Get
        Set(ByVal value As Integer)
            rowIndexNum = value
        End Set
    End Property

    Public Function EditingControlWantsInputKey(ByVal key As Keys, ByVal _
     dataGridViewWantsInputKey As Boolean) As Boolean Implements _
     IDataGridViewEditingControl.EditingControlWantsInputKey
        Return True
    End Function

    Public Sub PrepareEditingControlForEdit(ByVal selectAll As Boolean) Implements _
     IDataGridViewEditingControl.PrepareEditingControlForEdit
        ' No preparation needs to be done.
    End Sub

    Public ReadOnly Property RepositionEditingControlOnValueChange() As Boolean _
     Implements IDataGridViewEditingControl.RepositionEditingControlOnValueChange
        Get
            Return False
        End Get
    End Property

    Public Property EditingControlDataGridView() As DataGridView Implements _
     IDataGridViewEditingControl.EditingControlDataGridView
        Get
            Return dataGridViewControl
        End Get
        Set(ByVal value As DataGridView)
            dataGridViewControl = value
        End Set
    End Property

    Public Property EditingControlValueChanged() As Boolean Implements _
     IDataGridViewEditingControl.EditingControlValueChanged
        Get
            Return valueIsChanged
        End Get
        Set(ByVal value As Boolean)
            valueIsChanged = value
        End Set
    End Property

    Public ReadOnly Property EditingPanelCursor() As Cursor Implements _
     IDataGridViewEditingControl.EditingPanelCursor
        Get
            Return MyBase.Cursor
        End Get
    End Property

    Protected Overrides Sub OnTextChanged(ByVal eventargs As EventArgs)
        ' Notify the DataGridView that the contents of the cell have changed.
        valueIsChanged = True
        Me.EditingControlDataGridView.NotifyCurrentCellDirty(True)
        MyBase.OnTextChanged(eventargs)
    End Sub
End Class

Documentation Links

• DataGridViewColumn Class
• DataGridViewTextBoxCell Class
• MaskedTextBox Class
• IDataGridViewEditingControl Interface

Downloads/Links

Download the VB.Net Source code example used in this article: DataGridView.zip