Walkthrough: Handling Errors that Occur During Data Entry in the Windows Forms DataGridView Control
Handling errors from the underlying data store is a required feature for a data-entry application. The Windows Forms DataGridView control makes this easy by exposing the DataError event, which is raised when the data store detects a constraint violation or a broken business rule.
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 duplicate CustomerID value is detected in a new row or an edited existing row, the DataError event will occur, which will be handled by displaying a MessageBox that describes the exception.
To copy the code in this topic as a single listing, see How to: Handle Errors That Occur During Data Entry in the Windows Forms DataGridView Control.
To handle data-entry errors in the DataGridView control
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) End Sub ... <STAThread()> _ Shared Sub Main() Application.EnableVisualStyles() Application.Run(New Form1()) End Sub End Class
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.
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.
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, including the ' schema information that contains the CustomerID column ' constraint. Dim adapter As New SqlDataAdapter(selectCommand, connectionString) Dim data As New DataTable() data.Locale = System.Globalization.CultureInfo.InvariantCulture adapter.Fill(data) adapter.FillSchema(data, SchemaType.Source) Return data End Function
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
If the context for the error is a commit operation, display the error in a MessageBox.
Private Sub dataGridView1_DataError(ByVal sender As Object, _ ByVal e As DataGridViewDataErrorEventArgs) _ Handles dataGridView1.DataError ' If the data source raises an exception when a cell value is ' commited, display an error message. If e.Exception IsNot Nothing AndAlso _ e.Context = DataGridViewDataErrorContexts.Commit Then MessageBox.Show("CustomerID value must be unique.") End If End Sub
You can now test the form to make sure it behaves as expected.
To test the form
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.
Validate user input to the DataGridView control. For more information, see Walkthrough: Validating Data 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 Default Cell Styles for the Windows Forms DataGridView Control.