101 Ways to Manipulate the DataGridView Control

One of the common controls that you will usually use in Windows Forms programming is the DataGridView control. The DataGridView control is a new control in Windows Forms 2.0 and it replaces the DataGrid control in Windows Forms 1.0. It is a very powerful and versatile control that is suitable for displaying tabular data from various data sources. The DataGridView control is so flexible — exposing so many properties, methods, and events — that it can be quite intimidating for beginners.

The aim of this article is to get you jumpstarted with DataGridView control so that you can be productive right away. I have tried to cover most of the common tasks, but this is by no means exhaustive coverage of the capabilities of the DataGridView control.

In this article, I assume that you have a Windows application project and have a DataGridView control contained within the default Form1, as shown in Figure 1.

Binding to an Array
You can bind an array to a DataGridView control. Consider the following code snippet:

        Dim arr() As String = _           {"Product 1", "Product 24", "Product 356"}         DataGridView1.DataSource = arr

Here, arr() is a string array and contains three elements. It is bound to the DataGridView control using the DataSource property. Figure 2shows the result of the data binding.

Figure 1. To get started, create a new Windows application project and put a DataGridView control on Form1.

?

Figure 2. The result of binding a string array to a DataGridView control is shown.

The result may be surprising to you, as you may expect the DataGridView control to display each individual element in the array. Instead, the length of each element is displayed. The reason behind this behavior is because the DataGridView control looks for the first public property of the object to which it is binding. In this case, the first public property of a string array is actually the length of each element contained within it. Hence the results above.

To correctly bind a string array to a DataGridView control, you can wrap the string type with a class so that it exposes a public property that returns the content of each element, like this:

Public Class CStringItem    Private _str As String    Public Sub New(ByVal str As String)        _str = str    End Sub    Public Property Name() As String        Get            Return _str        End Get        Set(ByVal value As String)            _str = value        End Set    End PropertyEnd Class

The following code snippet declares arr()to be an array of CStringItem type and initializes it with three items. It is then bound to the DataGridView control:

        '---modified array---        Dim arr() As CStringItem = { _           New CStringItem("Product 1"), _           New CStringItem("Product 2"), _           New CStringItem("Product 3")}        DataGridView1.DataSource = arr

Public Class CStudent    Private _name As StringPrivate _ID As String    Public Sub New(ByVal id As String, ByVal name As String)        _ID = id        _name = name    End Sub       Public Property Name() As String        Get            Return _name        End Get        Set(ByVal value As String)            _name = value        End Set    End Property    Public Property ID() As String        Get            Return _ID        End Get        Set(ByVal value As String)            _ID = value        End Set    End PropertyEnd Class

Data-binding the DataGridView control to an array of the CStudent objects yields the result shown in Figure 4.

'---binding to custom object---Dim students() As CStudent = _   {New CStudent("123-456-789", "John"), _    New CStudent("456-123-789", "Mary")}DataGridView1.DataSource = students

Binding Using Typed Dataset

One very common use of the DataGridView control is binding to a table in a database. To illustrate this, I’ll add a Typed DataSet to the current project. In Visual Studio 2005, right-click on the project name in Solution Explorer and select Add | New Item?. Select the DataSet template (use the default name of DataSet1.xsd) and click Add.

Launch Server Explorer (View | Server Explorer) and navigate to the Northwind sample database (assuming you have it installed on SQL Server/SQL Server Express). Drag and drop the Customers table onto the design surface of DataSet1.xsd. Figure 5 shows the creation of the typed dataset.

You have a choice of what to do for your next step. You can either bind the DataGridView control directly to the table adapter of the Customers table, like this:

        '---create an instance of the table adapter---        Dim adapter As New CustomersTableAdapter        '---data bind to the DataGridView control---        DataGridView1.DataSource = adapter.GetData

Alternatively, you can use a BindingSource control:

        '---create an instance of the table adapter---        Dim adapter As New CustomersTableAdapter        '---create an instance of the bindingsource control---        Dim bindingSrc As New BindingSource        '---set the datasource for the bindingsource control---        bindingSrc.DataSource = adapter.GetData        '---data bind to the DataGridView control---        DataGridView1.DataSource = bindingSrc

Note that before the code above could work, you’d need to import the namespace as follows (DVG is the name of my project):

Imports DGV.DataSet1TableAdapters

        Dim connStr As String = _            "Data Source=.SQLEXPRESS;Initial Catalog=Northwind;" & _            "Integrated Security=True"        Dim sql As String = "SELECT * FROM Customers"        Dim conn As SqlConnection = New SqlConnection(connStr)        Dim comm As SqlCommand = New SqlCommand(sql, conn)        Dim dataadapter As SqlDataAdapter = New SqlDataAdapter(comm)        Dim ds As DataSet = New DataSet()        '---open the connection and fill the dataset---        conn.Open()        '---fill the dataset---        dataadapter.Fill(ds, "Customers_table")        '---close the connection---        conn.Close()        '---bind to the DataGridView control---        DataGridView1.DataSource = ds        '---set the table in the dataset to display---        DataGridView1.DataMember = "Customers_table"

Detecting Which Cell Is Clicked
If you want to retrieve the value of the cell in the DataGridView control clicked by the user, service the CellEnter event:

    '---when the user clicks on the datagridview control---    Private Sub DataGridView1_CellEnter( _       ByVal sender As Object, _       ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _       Handles DataGridView1.CellEnter        '---prints the content of the cell---        Console.WriteLine( _           DataGridView1.Rows(e.RowIndex).Cells(e.ColumnIndex).Value)    End Sub

The RowIndex and ColumnIndex properties will contain the row and column numbers, respectively, of the cell currently selected.

Filtering Using DataView
You can perform filtering on the data displayed in the DataGridView control using a DataView object:

        '---create an instance of the table adapter---        Dim adapter As New CustomersTableAdapter        '---create an instance of the dataview class---        Dim dv As New DataView(adapter.GetData)        With dv            .AllowNew = False            .AllowDelete = True            .Sort = "ContactTitle ASC, Address ASC"            .RowFilter = "CustomerID LIKE 'B*'"        End With        DataGridView1.DataSource = dv

The above code snippet shows that the DataView object allows users to add new rows (via the AllowNew property) in the DataGridView control and also allows rows to be deleted (via the AllowDelete property). In addition, you can sort the rows by specifying the field(s) and their corresponding sort order (via the Sort property). Finally, you filter the records by specifying an SQL-like expression using the RowFilter property.

The above code snippet sorts the rows according to the ContactTitle field (in ascending order), followed by the Address field (also in ascending order). If you want to sort the Address field in descending order, set it as follows:

            .Sort = "ContactTitle ASC, Address DESC"

        '---sort based on the first column---        DataGridView1.Sort(DataGridView1.Columns(0), _           System.ComponentModel.ListSortDirection.Descending)

Here, you are sorting the rows based on the first column, in descending order (see Figure 8).

Apart from programmatically sorting the columns, the user can also click on the sorting glyph (the triangular icon displayed next to the column header) to sort the rows either in ascending or descending order. To prevent a row from being sorted, set the SortMode to NotSortable:

        '---set the sort mode for a column---        DataGridView1.Columns(1).SortMode = _           DataGridViewColumnSortMode.NotSortable

Adding Columns Programmatically
So far I have shown you how to data bind a DataGridView control to a database (through a dataset or BindingSource control). Sometimes it is necessary to programmatically create the various fields in the DataGridView control and let the user populate it (or you can also add the new rows using code). To create columns in the DataGridView control, use the Add()method from the Columns property in the DataGridView control. The following code snippet creates four fields in the DataGridView control:

        '---adding columns---        DataGridView1.Columns.Add("ID", "Product ID")        DataGridView1.Columns.Add("Name", "Product Name")        DataGridView1.Columns.Add("Description", "Description")        DataGridView1.Columns.Add("Price", "Price")

        '---add an empty row---        DataGridView1.Rows.Add()

To add a row and populate it with content, create an instance of the DataGridViewRow class and then use the CreateCells()method to create a row template:

        For i As Integer = 0 To 9            '---create a row---            Dim item As New DataGridViewRow            item.CreateCells(DataGridView1)            With item                .Cells(0).Value = i                .Cells(1).Value = "Product " & i                .Cells(2).Value = "Description of Product " & i                .Cells(3).Value = "99.99"            End With            '---add the row---            DataGridView1.Rows.Add(item)        Next

Alternatively, you can also add a new row by creating an array containing the values for the various fields in the row:

        For i As Integer = 0 To 9            DataGridView1.Rows.Add(New String() _               {i, _                "Product " & _                i, _                "Description of Product " & i, _                "99.99"})        Next

        '---adding columns---        DataGridView1.Columns.Add("ID", "Product ID")        DataGridView1.Columns.Add("Name", "Product Name")        DataGridView1.Columns.Add("Description", "Description")        DataGridView1.Columns.Add("Price", "Price")        '---add a combobox column---        Dim comboBoxCol As New DataGridViewComboBoxColumn        '---set the header text---        comboBoxCol.HeaderText = "Types"        '---add items to it---        comboBoxCol.Items.Add("Type A")        comboBoxCol.Items.Add("Type B")        comboBoxCol.Items.Add("Type C")        DataGridView1.Columns.Add(comboBoxCol)

The latter approach is more flexible as it allows the user to add new items into the drop-down list during runtime (see the next section for details). If the ComboBox control is bound to a data source, you will not be able to add new items into it during runtime.

Adding Items into a DataGridViewComboBoxColumn Control
The previous section showed how to display a ComboBox control within a cell in the DataGridView control. There are times when you need to allow the users to insert additional items into the ComboBox control. In this case, you need to do some work.

First, service the EditingControlShowing event of the DataGridView control. This event is fired when the user tries to edit the ComboBox control:

    Private Sub DataGridView1_EditingControlShowing( _       ByVal sender As Object, _       ByVal e As System.Windows.Forms. _          DataGridViewEditingControlShowingEventArgs) _       Handles DataGridView1.EditingControlShowing        Dim comboBoxColumn As DataGridViewComboBoxColumn = _           DataGridView1.Columns(4)        If (DataGridView1.CurrentCellAddress.X = _           comboBoxColumn.DisplayIndex) Then            Dim cb As ComboBox = e.Control            If (cb IsNot Nothing) Then                cb.DropDownStyle = ComboBoxStyle.DropDown            End If        End If    End Sub

Here, you essentially check to see if the cell being edited is the one containing the ComboBox. If it is, the drop-down style of the ComboBox control is set to DropDown so that the user can type into it.

Next, service the CellValidating event of the DataGridView control. This event is fired when the user is done with the typing and leaves the cell:

    Private Sub DataGridView1_CellValidating( _       ByVal sender As Object, _       ByVal e As System.Windows.Forms. _       DataGridViewCellValidatingEventArgs) _       Handles DataGridView1.CellValidating        Dim comboBoxColumn As DataGridViewComboBoxColumn = _           DataGridView1.Columns(4)        If (e.ColumnIndex = comboBoxColumn.DisplayIndex) Then            If (Not comboBoxColumn.Items.Contains( _               e.FormattedValue)) Then                comboBoxColumn.Items.Add(e.FormattedValue)            End If        End If    End Sub

Here, you will perform the usual checks and then add the newly typed item into the ComboBox. Figure 12shows that originally the ComboBox contains three items. The user can now type in a new item (e.g. “Type D”) and then it will be added into the list. Note that for subsequent rows (as well as the rows above it) the ComboBox control will now contain four items.

Locking Rows/Columns
You can prevent users from modifying a particular row by setting the ReadOnly property for that particular row to True:

        '---first row is readonly---        DataGridView1.Rows(0).ReadOnly = True

Likewise, you can also lock cells belonging to a particular column:

        '---first column is readonly---        DataGridView1.Columns(0).ReadOnly = True

Note that while setting the ReadOnly property to True prevents the user from editing the values in the cell, this does not affect his ability to delete rows.

If you want, you can lock the entire DataGridView control:

        '---entire grid is readonly---        DataGridView1.ReadOnly = True

Hiding Columns
During runtime, you can hide a particular column by setting its Visible property to False:

'---hides the second column---DataGridView1.Columns(1).Visible = False

To validate that the data entered is of the correct type, you need to service two events. First, service the CellValidating event, which is fired when the user has finished modifying the value of a cell and leaves the cell:

    Private Sub DataGridView1_CellValidating( _       ByVal sender As Object, _       ByVal e As System.Windows.Forms. _       DataGridViewCellValidatingEventArgs) _       Handles DataGridView1.CellValidating        '---Price field---        If e.ColumnIndex = 3 Then            If Not IsNumeric(e.FormattedValue) Then                DataGridView1.Rows(e.RowIndex).ErrorText = _                   "Price must be a numeric value."                e.Cancel = True            End If        End If    End Sub

In this event, you will perform the necessary validation. For example, in the above example, you will check that the fourth column (column index 3?the Price field) contains only numeric values. If not, an error message is displayed on the leftmost column of the DataGridView control (set via the ErrorText property). Figure 14shows this in action.

The error must be corrected before the user can switch to the other cells. To ensure that the error message goes away when the correct data is entered, service the CellEndEdit event, as follows:

    Private Sub DataGridView1_CellEndEdit( _       ByVal sender As Object, _       ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _       Handles DataGridView1.CellEndEdit        '---clear the error message---        DataGridView1.Rows(e.RowIndex).ErrorText = String.Empty    End Sub

Restricting Inputs
The previous section shows how you can validate the value for a cell after the user has finished typing it. However, in some situations this is not sufficient. A better way would be to prevent illegal characters from being entered in the first place. Using the same example from the previous section, you should prevent users from entering non-numeric characters in the Price field. For normal TextBox controls, this problem could be solved by servicing the KeyPress event. However, applying this technique to the DataGridView control requires some additional work.

First, service the EditingControlShowing event. This event is fired when the user tries to edit the content of a cell:

    Private Sub DataGridView1_EditingControlShowing( _       ByVal sender As Object, _       ByVal e As System.Windows.Forms. _       DataGridViewEditingControlShowingEventArgs) _       Handles DataGridView1.EditingControlShowing        '---restrict inputs on the fourth field---        If Me.DataGridView1.CurrentCell.ColumnIndex = 3 And _           Not e.Control Is Nothing Then            Dim tb As TextBox = CType(e.Control, TextBox)            '---add an event handler to the TextBox control---            AddHandler tb.KeyPress, AddressOf TextBox_KeyPress        End If    End Sub

Here, you will add a KeyPress event handler to the TextBox control that you want to restrict. This KeyPress event handler will be invoked when the user types into the cell and it is defined as follows:

    Private Sub TextBox_KeyPress( _       ByVal sender As System.Object, _       ByVal e As System.Windows.Forms.KeyPressEventArgs)        '---if textbox is empty and user pressed a decimal char---        If CType(sender, TextBox).Text = String.Empty And _           e.KeyChar = Chr(46) Then            e.Handled = True            Return        End If        '---if textbox already has a decimal point---        If CType(sender, TextBox).Text.Contains(Chr(46)) And _           e.KeyChar = Chr(46) Then            e.Handled = True            Return        End If        '---if the key pressed is not a valid decimal number---        If (Not (Char.IsDigit(e.KeyChar) Or _           Char.IsControl(e.KeyChar) Or _           (e.KeyChar = Chr(46)))) Then            e.Handled = True        End If    End Sub

The above code will restrict the user inputs to numeric digits (including “.”)only. No other typed characters will appear in the cell.

Deleting Rows
To programmatically delete a row in the DataGridView control, you can use the Remove()method. The following code snippet removes all the selected rows in the DataGridView control:

        For Each row As DataGridViewRow In DataGridView1.SelectedRows            DataGridView1.Rows.Remove(row)        Next

The user can also delete rows by first selecting the rows and then pressing the Delete key. By default, the deletion is done automatically without any prompting. But you may want to confirm the deletion with the user before deleting them. You can do so via the UserDeletingRow event:

    Private Sub DataGridView1_UserDeletingRow( _       ByVal sender As Object, _       ByVal e As System.Windows.Forms. _       DataGridViewRowCancelEventArgs) _       Handles DataGridView1.UserDeletingRow        If (Not e.Row.IsNewRow) Then            Dim response As DialogResult = _            MessageBox.Show( _            "Are you sure you want to delete this row?", _            "Delete row?", _            MessageBoxButtons.YesNo, _            MessageBoxIcon.Question, _            MessageBoxDefaultButton.Button2)            If (response = DialogResult.No) Then                e.Cancel = True            End If        End If    End Sub

This will prompt the user to confirm that they want to perform a row deletion (see Figure 15).

Figure 15. The UserDeletingRow event prompts the user before actual deletion takes place.

Saving Changes
When you data-bind a DataGridView control to a data source (from a database), all the changes made to the DataGridView control are not automatically updated on the database. For this, you need to manually push all the changes back to the database.

Using the earlier example of binding the DataGridView control to a dataset, the following code loads a dataset with the Customers table:

    Dim connStr As String = _        "Data Source=.SQLEXPRESS;Initial Catalog=Northwind;" & _        "Integrated Security=True"    Dim sql As String = "SELECT * FROM Customers"    Dim conn As SqlConnection = New SqlConnection(connStr)    Dim comm As SqlCommand = New SqlCommand(Sql, conn)    Dim dataadapter As SqlDataAdapter = New SqlDataAdapter(comm)    Dim ds As DataSet = New DataSet()       '---open the connection and fill the dataset---        conn.Open()        dataadapter.Fill(ds, "Customers_table")        conn.Close()        DataGridView1.DataSource = ds        DataGridView1.DataMember = "Customers_table"

To save the changes made on the DataGridView control back to the database, use the following code:

        Dim sqlCmdBuilder As New SqlCommandBuilder(dataadapter)        sqlCmdBuilder.GetUpdateCommand()        dataadapter.Update(ds.Tables("Customers_table"))

The SqlCommandBuilder object will automatically formulate the SQL statement (through the GetUpdateCommand()method) to reflect the changes made to the DataGridView control and then let the SqlDataAdapter object update the table in the database.

If you are binding the DataGridView control to a typed dataset, like this:

    Dim adapter As New CustomersTableAdapter    Dim bindingSrc As New BindingSource        bindingSrc.DataSource = adapter.GetData        DataGridView1.DataSource = bindingSrc

Then, you can simply update the changes to the database using the following code:

        bindingSrc.EndEdit()        adapter.Update(bindingSrc.DataSource)

Lastly, if you are saving the changes somewhere else (such as a string), you can simply loop through all the rows and columns in the DataGridView control:

        Dim output As String = String.Empty        For Each row As DataGridViewRow In DataGridView1.Rows            For Each cell As DataGridViewCell In row.Cells                output += cell.Value & ":"            Next            output += vbCrLf        Next        MsgBox(output)

In this article, you have seen how to perform common tasks associated with the DataGridView control. As you can see, the DataGridView is very versatile. Hopefully this will serve as a helpful reference when you start using the DataGridView to display data from databases or any other data source.