Windows forms DataGridView dates and numeric columns (C#)
Introduction
Working with dates and numeric data types in a DataGridView pose challenges when validating user input does not match the underlying data type for a DataGridViewCell. Learn several different methods to handle validating and using custom DataGridView columns to prevent invalid data from being entered and by using visual cues to alert the users what is incorrect when entering invalid data.
Learn how to work with dates, times and numeric data types using custom columns and/or proper assertions/validation. Code samples have intentionally kept to basic levels for common usage rather than getting into complex scenarios which are harder to a) learn from b) break down to simple usage.
Problems
A DataGridView has data loaded from a database using conventional methods to read data e.g. using a connection and command to load table data into a DataSet or DataTable or using Entity Framework or Entity Framework Core into a List. One of the properties may be a numeric or Date. A DataGridView is used to present data with editing permitted. If the user enters a value for a date or number an error is raised. If there has been no thought to this a standard dialog is displayed with a cryptic message is displayed that the average user has no clue what to do and also considered a poor design from the developer.
Solutions
There are two paths, the first is to handle the problem mention above with events and assertion. The first path is to use events and assertions. Shown below the user used a character combined with a numeric value while the underlying data type is an int.
There are two ways to assert for a wrong data type, in CellValidating event or in DataError event, In this case DataError event will be used. First step is to determine the exception type, in this case FormatException. Once this is known error text can be set for the row (cell error text can be set also but using the row error text is less obtrusive). Since a visual is shown e.ThrowException is set to false.
private void DataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e)
{
if ((e.Exception) is FormatException)
{
var view = (DataGridView)sender;
view.Rows[e.RowIndex].ErrorText =
$"Invalid value for '{view.CurrentCell.OwningColumn.HeaderText}'";
e.ThrowException = false;
}
else
{
/*
* for development - figure out how this is to be handled
* then assert for it as done with FormatException
*/
Console.WriteLine(e.Exception.Message);
e.Cancel = true;
}
}
To clear an error which is done by the user either pressing ESC key, changing to the proper type for the cell clear the error text in CellEndEdit.
private void DataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
var view = (DataGridView)sender;
view.Rows[e.RowIndex].ErrorText = "";
}
Using events as presented provide minimal method to handle invalid values. This can be expanded on by deeper assertion e.g. the user entered a proper data but not formatted properly which using DateTime.TryParseExact can be used or for numeric types TryParse.
The second path would be to use custom columns for presentation and editing of data. For instance, a column which uses a DateTimePicker to enter or alter dates in this case.
Similarly a time column can be created or for that matter just about any type desired using columns presented here included with the article's source code.
Going back to not using a custom column, there is the case also for using custom columns and events e.g. using a calendar column for dates while using events for a numeric column.
Rather than wait for invalid data to be entered, subscribe to EditingControlShowing event.
- Determine if there is a current row in the event data has not been loaded or all data has been removed.
- Assert the column type e.g. is it for a date, time or simple number.
- Handle ensuring user input is proper and handle if not directly in the event or in the case of a number and the control is a DataGridViewTextBoxEditingControl subscribe to KeyPress event.
- Always remove a subscription to an event first as shown to ensure over time there are not multiple events for the column.
private void DataGridView1_EditingControlShowing(object sender,
DataGridViewEditingControlShowingEventArgs e)
{
if (dataGridView1.CurrentRow != null)
{
_currentIndex = dataGridView1.CurrentRow.Index;
}
if (e.Control is CalendarEditingControl calendar)
{
/*
* there is only one here so this assertion is not needed but if there
* were more calendar columns then assertion is needed
*/
if (dataGridView1.CurrentCell.OwningColumn.Name == "StartDateColumn")
{
if (dataGridView1.CurrentRow != null)
{
var value = _bindingListContacts.StartDate(_currentIndex);
Console.WriteLine($"EditingControlShowing {value:d}");
}
}
}
if (e.Control is TimeEditingControl)
{
if (dataGridView1.CurrentRow != null)
{
var value = _bindingListContacts.StartTime(_currentIndex);
Console.WriteLine($"{value:T}");
}
}
if (e.Control is DataGridViewTextBoxEditingControl )
{
if (dataGridView1.CurrentCell.OwningColumn.Name == "RoomIdentifierColumn")
{
e.Control.KeyPress -= RoomNumberNumericOnly_KeyPress;
if (e.Control is TextBox tb)
{
tb.KeyPress += RoomNumberNumericOnly_KeyPress;
}
}
}
}
In the case of a numeric the KeyPress event prohibits non-numeric values.
private void RoomNumberNumericOnly_KeyPress(object sender, KeyPressEventArgs e)
{
if (!char.IsControl(e.KeyChar) && !char.IsDigit(e.KeyChar))
{
e.Handled = true;
}
}
Custom columns
The following article has a Numeric TextBox column.
Summary
Various methods have been presented in their basic format to prevent or alert users of invalid data from being entered into cells in a DataGridView. Examples have been written at a simple level as more complex examples are usually difficult to learn from especially with event driven assertions.
Source code
There are two projects which provide examples for what has been presented. In both projects data is loaded using Microsoft Entity Framework Core 3x using asynchronous reads, To allow Entity Framework Core to be notified of changes the model implements INotifyPropertyChanged Interface coupled with either a standard BindingList or a custom BindingList (included in the source code). Without INotifyPropertyChanged Interface when going to save changes the ChangeTracker will not be aware of changes.
using System;
using System.ComponentModel;
using System.Runtime.CompilerServices;
namespace Example1.Models
{
/// <summary>
/// Represents a fictitious hotel room not well structured,
/// the structure conforms to demonstrates custom DataGridView
/// columns.
/// </summary>
public partial class Room : INotifyPropertyChanged
{
private int? _identifier;
private DateTime? _startDate;
private DateTime? _startTime;
public int RoomIdentifier { get; set; }
public int? Identifier
{
get => _identifier;
set
{
_identifier = value;
OnPropertyChanged();
}
}
public DateTime? StartDate
{
get => _startDate;
set
{
_startDate = value;
_startTime = value;
OnPropertyChanged();
}
}
public DateTime? StartTime
{
get => _startTime;
set
{
_startTime = value;
_startDate = value;
OnPropertyChanged();
}
}
public override string ToString() => RoomIdentifier.ToString();
public event PropertyChangedEventHandler PropertyChanged;
protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
{
PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
}
}
}
To keep code clean in several cases language extension methods are used for the BindingList and the DataGridView to work with underlying model data.
Although these code samples use Entity Framework Core, the custom DataGridView columns don't know or care where the data came from which means a DataTable may be the data source for a DataGridView, a DataSet, TableAdapter or generic list may be used that are strong typed to work with the custom columns can be used.
Clone/download
The following GitHub repository contains all source code and database scripts to compile and run projects included in a Visual Studio solution.