Click here to Skip to main content
15,568,455 members
Articles / Database Development / SQL Server
Article
Posted 17 Aug 2009

Stats

133.4K views
12.3K downloads
48 bookmarked

Nullable datetime column in .NET DataGrid with DateTimePicker

Rate me:
Please Sign up or sign in to vote.
5.00/5 (14 votes)
17 Aug 2009CPOL2 min read
A nullable datetime column in .NET DataGrid with DateTimePicker.

Image 1

Introduction

I have always known a lot of people who have problems with the standard .NET 2.0 DataGridView and the nullable DateTime column (created with Oracle PL/SQL column definition):

SQL
dtReceived DATE

or a MS SQL Server DateTime column with NULL values in Transact SQL:

SQL
dtReceived DATETIME NULL

Google Search normally gives this Microsoft sample for calendar columns: How to: Host Controls in Windows Forms DataGridView Cells. This is a good start point, but it isn't enough. You can never correctly fill DateTime DataGridView cells with NULL. I found many variants for the simple DateTimePicker class, but work samples absent for nullable DateTime columns in a DataGridView.

Solution

I spent a lot of time on the Internet searching, and found a nice article about a nullable DateTimePicker: DateTimePicker DBNull. I then made several corrections and as 'magic touch', and it works for DataGridViews now.

Using the code

These are the steps we need:

  1. Rename the class DateTimePicker to DatePicker (the same name for different classes is a bad practice, in my opinion):
  2. C#
    public class DatePicker : System.Windows.Forms.DateTimePicker
    {..
  3. Add the following code snippet to the DatePicker class:
  4. C#
    public string ToShortDateString() 
    { 
        if (!realDate) 
            return String.Empty; 
        else 
        { 
            DateTime dt = (DateTime)Value; 
            return dt.ToShortDateString(); 
        }
    }
  5. Correct CalendarEditingControl.cs from the Microsoft Sample for usage in our DatePicker:
  6. C#
    class CalendarEditingControl : DatePicker, IDataGridViewEditingControl { ...
  7. Correct CalendarCell.cs from the Microsoft Sample for DBNull:
  8. C#
    public override void InitializeEditingControl(int rowIndex, object 
    initialFormattedValue, DataGridViewCellStyle dataGridViewCellStyle) 
    { 
        // Set the value of the editing control to the current cell value.
        base.InitializeEditingControl(rowIndex, initialFormattedValue, 
                                                dataGridViewCellStyle); 
        CalendarEditingControl ctl = 
        DataGridView.EditingControl as CalendarEditingControl;
    
        // ++ vic 14-aug-2009
         object val = null;
         try 
         {
              val = this.Value;
         }
         catch (Exception ex)
         { 
              // Argument ot of range (value doesn't exists in collection)
              return;
         }
         
         if (val != System.DBNull.Value)
              ctl.Value = (DateTime)val;
    }

That's all, folks :)

You should press the DEL key to set a NULL filled DateTime cell.

The source code is attached. You can directly use the files from my project in your projects like in the Microsoft Samples (DatePicker.cs, CalendarCell.cs, CalendarColumn.cs, CalendarEditingControl.cs):

C#
private void Form1_Load(object sender, EventArgs e)
{
     CalendarColumn col = new CalendarColumn();
     this.dataGridView1.Columns.Add(col);
     this.dataGridView1.RowCount = 5;
     foreach (DataGridViewRow row in this.dataGridView1.Rows)
     {
          row.Cells[0].Value = DateTime.Now;
     }
}

or from the Microsoft Visual Studio Data Designer for DataGrid (right mouse click on DataGridView, and choose 'Edit columns' from the context menu):

Image 2

I have attached a real sample with database (Visual Studio 2008 / SQL Express database).

History

  • 14-Aug-2009: Initial version posted.
  • 16-Aug-2009: Database sample added.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
Russian Federation Russian Federation
Microsoft certified professional developer (MCPD)

Comments and Discussions

 
QuestionCreating New Row on Cell Click Pin
Member 1144181710-May-16 2:10
Member 1144181710-May-16 2:10 
QuestionCan't insert the calendar column value to SQL server Pin
Mozaharul Haque19-Mar-14 23:36
Mozaharul Haque19-Mar-14 23:36 
AnswerCorrection Pin
Member 1003024528-Jan-14 19:50
Member 1003024528-Jan-14 19:50 
GeneralExcellent Article Pin
Vishnuvardhan Reddy M15-Dec-13 19:33
Vishnuvardhan Reddy M15-Dec-13 19:33 
QuestionCustom formating Pin
Member 85931649-Jul-13 5:12
Member 85931649-Jul-13 5:12 
GeneralThank you very much !! Pin
Mazen el Senih28-Jul-12 2:18
professionalMazen el Senih28-Jul-12 2:18 
QuestionReallly good article! Pin
vishme27-Mar-12 1:12
vishme27-Mar-12 1:12 
Questiondoubt! Pin
choosechrist26-Feb-12 22:14
choosechrist26-Feb-12 22:14 
BugRow delete problem Pin
Member 817129618-Nov-11 7:38
Member 817129618-Nov-11 7:38 
QuestionSome bug... Pin
domivan24-Aug-11 5:37
domivan24-Aug-11 5:37 
QuestionRe: Some bug... Pin
djax5-Mar-13 1:40
djax5-Mar-13 1:40 
GeneralBrilliant! Thanks so much Pin
Daniel Neal12-May-11 1:46
Daniel Neal12-May-11 1:46 
Generalediting problem. Pin
Mohsin Hawra14-Jul-10 23:22
Mohsin Hawra14-Jul-10 23:22 
GeneralRe: editing problem. Pin
Razze11-Oct-10 3:07
Razze11-Oct-10 3:07 
AnswerRe: editing problem. Pin
cje1235-Dec-10 17:41
cje1235-Dec-10 17:41 
GeneralRe: editing problem. Pin
wouterdebo12-Jun-12 4:33
wouterdebo12-Jun-12 4:33 
GeneralGreat job Pin
khalil Youssef30-Jun-10 22:14
khalil Youssef30-Jun-10 22:14 
GeneralGreat Job Pin
Mohammad Rafi Bin Abd Alim16-Jun-10 21:11
Mohammad Rafi Bin Abd Alim16-Jun-10 21:11 
QuestionAllowing editing of the 'Time' portion of the DateTime [modified] Pin
Kizza00711-May-10 23:00
Kizza00711-May-10 23:00 
AnswerRe: Allowing editing of the 'Time' portion of the DateTime Pin
Kizza00712-May-10 0:54
Kizza00712-May-10 0:54 
GeneralRe: Allowing editing of the 'Time' portion of the DateTime Pin
Kim Bilida16-Jul-10 6:58
Kim Bilida16-Jul-10 6:58 
GeneralGreat! Pin
zeiddev20-Mar-10 13:05
zeiddev20-Mar-10 13:05 
Generalthis.Value VS this.ToShortDateString() Pin
Drew Loika1-Feb-10 12:38
Drew Loika1-Feb-10 12:38 
GeneralRe: this.Value VS this.ToShortDateString() Pin
vic_ch20001-Feb-10 19:23
vic_ch20001-Feb-10 19:23 
Generalthank you Pin
Samir the Boy13-Jan-10 1:06
Samir the Boy13-Jan-10 1:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.