Walkthrough: Validating Data in the Windows Forms DataGridView Control

Article
11/16/2012

When you display data entry functionality to users, you frequently have to validate the data entered into your form. The DataGridView class provides a convenient way to perform validation before data is committed to the data store. You can validate data by handling the CellValidating event, which is raised by the DataGridView when the current cell changes.

In this walkthrough, you will retrieve rows from the Customers table in the Northwind sample database and display them in a DataGridView control. When a user edits a cell in the CompanyName column and tries to leave the cell, the CellValidating event handler will examine new company name string to make sure it is not empty; if the new value is an empty string, the DataGridView will prevent the user's cursor from leaving the cell until a non-empty string is entered.

To copy the code in this topic as a single listing, see How to: Validate Data in the Windows Forms DataGridView Control.

Prerequisites

In order to complete this walkthrough, you will need:

Access to a server that has the Northwind SQL Server sample database.

Creating the Form

To validate data entered in a DataGridView

Create a class that derives from Form and contains a DataGridView control and a BindingSource component.

The following code example provides basic initialization and includes a Main method.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms

Public Class Form1
    Inherits System.Windows.Forms.Form

    Private WithEvents dataGridView1 As New DataGridView()
    Private bindingSource1 As New BindingSource()

    Public Sub New()

        ' Initialize the form.
        Me.dataGridView1.Dock = DockStyle.Fill
        Me.Controls.Add(dataGridView1)
        Me.Text = "DataGridView validation demo (disallows empty CompanyName)"

    End Sub


...


    <STAThread()> _
    Shared Sub Main()
        Application.EnableVisualStyles()
        Application.Run(New Form1())
    End Sub

End Class

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

public class Form1 : System.Windows.Forms.Form
{
    private DataGridView dataGridView1 = new DataGridView();
    private BindingSource bindingSource1 = new BindingSource();

    public Form1()
    {
        // Initialize the form.
        this.dataGridView1.Dock = DockStyle.Fill;
        this.Controls.Add(dataGridView1);
        this.Load += new EventHandler(Form1_Load);
        this.Text = "DataGridView validation demo (disallows empty CompanyName)";
    }


...


    [STAThread]
    static void Main()
    {
        Application.EnableVisualStyles();
        Application.Run(new Form1());
    }

}

Implement a method in your form's class definition for handling the details of connecting to the database.

This code example uses a GetData method that returns a populated DataTable object. Be sure that you set the connectionString variable to a value that is appropriate for your database.

Security Note:
Storing sensitive information, such as a password, within the connection string can affect the security of your application. Using Windows Authentication, also known as integrated security, is a more secure way to control access to a database. For more information, see Protecting Connection Information (ADO.NET).

Private Shared Function GetData(ByVal selectCommand As String) As DataTable

    Dim connectionString As String = _
        "Integrated Security=SSPI;Persist Security Info=False;" + _
        "Initial Catalog=Northwind;Data Source=localhost;Packet Size=4096" 

    ' Connect to the database and fill a data table. 
    Dim adapter As New SqlDataAdapter(selectCommand, connectionString)
    Dim data As New DataTable()
    data.Locale = System.Globalization.CultureInfo.InvariantCulture
    adapter.Fill(data)

    Return data

End Function

private static DataTable GetData(string selectCommand)
{
    string connectionString =
        "Integrated Security=SSPI;Persist Security Info=False;" +
        "Initial Catalog=Northwind;Data Source=localhost;Packet Size=4096";

    // Connect to the database and fill a data table.
    SqlDataAdapter adapter =
        new SqlDataAdapter(selectCommand, connectionString);
    DataTable data = new DataTable();
    data.Locale = System.Globalization.CultureInfo.InvariantCulture;
    adapter.Fill(data);

    return data;
}

Implement a handler for your form's Load event that initializes the DataGridView and BindingSource and sets up the data binding.

Private Sub Form1_Load(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Me.Load

    ' Initialize the BindingSource and bind the DataGridView to it.
    bindingSource1.DataSource = GetData("select * from Customers")
    Me.dataGridView1.DataSource = bindingSource1
    Me.dataGridView1.AutoResizeColumns( _
        DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader)

End Sub

private void Form1_Load(System.Object sender, System.EventArgs e)
{
    // Attach DataGridView events to the corresponding event handlers. 
    this.dataGridView1.CellValidating += new
        DataGridViewCellValidatingEventHandler(dataGridView1_CellValidating);
    this.dataGridView1.CellEndEdit += new
        DataGridViewCellEventHandler(dataGridView1_CellEndEdit);

    // Initialize the BindingSource and bind the DataGridView to it.
    bindingSource1.DataSource = GetData("select * from Customers");
    this.dataGridView1.DataSource = bindingSource1;
    this.dataGridView1.AutoResizeColumns(
        DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
}

Implement handlers for the DataGridView control's CellValidating and CellEndEdit events.

The CellValidating event handler is where you determine whether the value of a cell in the CompanyName column is empty. If the cell value fails validation, set the Cancel property of the System.Windows.Forms.DataGridViewCellValidatingEventArgs class to true. This causes the DataGridView control to prevent the cursor from leaving the cell. Set the ErrorText property on the row to an explanatory string. This displays an error icon with a ToolTip that contains the error text. In the CellEndEdit event handler, set the ErrorText property on the row to the empty string. The CellEndEdit event occurs only when the cell exits edit mode, which it cannot do if it fails validation.

Private Sub dataGridView1_CellValidating(ByVal sender As Object, _
    ByVal e As DataGridViewCellValidatingEventArgs) _
    Handles dataGridView1.CellValidating

    Dim headerText As String = _
        dataGridView1.Columns(e.ColumnIndex).HeaderText

    ' Abort validation if cell is not in the CompanyName column. 
    If Not headerText.Equals("CompanyName") Then Return 

    ' Confirm that the cell is not empty. 
    If (String.IsNullOrEmpty(e.FormattedValue.ToString())) Then
        dataGridView1.Rows(e.RowIndex).ErrorText = _
            "Company Name must not be empty"
        e.Cancel = True 
    End If 
End Sub 

Private Sub dataGridView1_CellEndEdit(ByVal sender As Object, _
    ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _
    Handles dataGridView1.CellEndEdit

    ' Clear the row error in case the user presses ESC.   
    dataGridView1.Rows(e.RowIndex).ErrorText = String.Empty

End Sub

private void dataGridView1_CellValidating(object sender,
    DataGridViewCellValidatingEventArgs e)
{
    string headerText = 
        dataGridView1.Columns[e.ColumnIndex].HeaderText;

    // Abort validation if cell is not in the CompanyName column. 
    if (!headerText.Equals("CompanyName")) return;

    // Confirm that the cell is not empty. 
    if (string.IsNullOrEmpty(e.FormattedValue.ToString()))
    {
        dataGridView1.Rows[e.RowIndex].ErrorText =
            "Company Name must not be empty";
        e.Cancel = true;
    }
}

void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
    // Clear the row error in case the user presses ESC.   
    dataGridView1.Rows[e.RowIndex].ErrorText = String.Empty;
}

Testing the Application

You can now test the form to make sure it behaves as expected.

To test the form

Compile and run the application.

You will see a DataGridView filled with data from the Customers table. When you double-click a cell in the CompanyName column, you can edit the value. If you delete all the characters and hit the TAB key to exit the cell, the DataGridView prevents you from exiting. When you type a non-empty string into the cell, the DataGridView control lets you exit the cell.

Next Steps

This application gives you a basic understanding of the DataGridView control's capabilities. You can customize the appearance and behavior of the DataGridView control in several ways:

Change border and header styles. For more information, see How to: Change the Border and Gridline Styles in the Windows Forms DataGridView Control.
Enable or restrict user input to the DataGridView control. For more information, see How to: Prevent Row Addition and Deletion in the Windows Forms DataGridView Control, and How to: Make Columns Read-Only in the Windows Forms DataGridView Control.
Check user input for database-related errors. For more information, see Walkthrough: Handling Errors that Occur During Data Entry in the Windows Forms DataGridView Control.
Handle very large data sets using virtual mode. For more information, see Walkthrough: Implementing Virtual Mode in the Windows Forms DataGridView Control.
Customize the appearance of cells. For more information, see How to: Customize the Appearance of Cells in the Windows Forms DataGridView Control and How to: Set Font and Color Styles in the Windows Forms DataGridView Control.

Share via

Walkthrough: Validating Data in the Windows Forms DataGridView Control

Prerequisites

Creating the Form

To validate data entered in a DataGridView

Testing the Application

To test the form

Next Steps

See Also

Tasks

Concepts

Reference

Other Resources

Additional resources