Click here to Skip to main content
Click here to Skip to main content

Nullable datetime column in .NET DataGrid with DateTimePicker

, 17 Aug 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
A nullable datetime column in .NET DataGrid with DateTimePicker.

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):

dtReceived DATE

or a MS SQL Server DateTime column with NULL values in Transact 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. public class DatePicker : System.Windows.Forms.DateTimePicker
    {..
  3. Add the following code snippet to the DatePicker class:
  4. 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. class CalendarEditingControl : DatePicker, IDataGridViewEditingControl { ...
  7. Correct CalendarCell.cs from the Microsoft Sample for DBNull:
  8. 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 Smile | :)

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):

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):

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)

Share

About the Author

vic_ch2000
Web Developer
Russian Federation Russian Federation
Microsoft certified professional developer (MCPD)

Comments and Discussions

 
QuestionCan't insert the calendar column value to SQL server PinmemberMozaharul Haque19-Mar-14 23:36 
AnswerCorrection PinmemberMember 1003024528-Jan-14 19:50 
GeneralExcellent Article PinmemberVishnuvardhan Reddy M15-Dec-13 19:33 
QuestionCustom formating PinmemberMember 85931649-Jul-13 5:12 
GeneralThank you very much !! PinmemberMazen el Senih28-Jul-12 2:18 
QuestionReallly good article! Pinmembervishme27-Mar-12 1:12 
Questiondoubt! Pinmemberchoosechrist26-Feb-12 22:14 
BugRow delete problem PinmemberMember 817129618-Nov-11 7:38 
Firstly, thanks for this code - it's very helpful.

When clicking in one of these calendar cells, then clicking on the row header, then clicking DEL key to delete the row, the row delete behavior is overriden by the DatePicker OnKeyDown event.

If you put another override OnKeyDown in the CalendarEditingControl class,
you can catch a row delete event and perform it properly. But now, I'm having some trouble hooking it back into my DataGridView UserDeletingRow event (where I'm confirming row delete and performing db operations), since the row delete is now firing programmatically.

Just posting this here in case it helps and if anyone can think of a DataGridView event I can use to catch the delete before it happens. I can get into RowsRemoved event but by then it's too late.

Here's my override code for the CalendarEditingControl:
protected override void OnKeyDown(System.Windows.Forms.KeyEventArgs e)
{
    if (dataGridView.Rows[rowIndex].Selected) dataGridView.Rows.Remove(dataGridView.Rows[rowIndex]);
    else base.OnKeyDown(e);
}

QuestionSome bug... Pinmemberdomivan24-Aug-11 5:37 
QuestionRe: Some bug... Pinmemberdjax5-Mar-13 1:40 
GeneralBrilliant! Thanks so much PinmemberDaniel Neal12-May-11 1:46 
Generalediting problem. PinmemberMohsin Hawra14-Jul-10 23:22 
GeneralRe: editing problem. PinmemberRazze11-Oct-10 3:07 
AnswerRe: editing problem. Pinmembercje1235-Dec-10 17:41 
GeneralRe: editing problem. Pinmemberwouterdebo12-Jun-12 4:33 
GeneralGreat job Pinmemberkhalil Youssef30-Jun-10 22:14 
GeneralGreat Job PinmemberMohammad Rafi Bin Abd Alim16-Jun-10 21:11 
QuestionAllowing editing of the 'Time' portion of the DateTime [modified] PinmemberKMalone11-May-10 23:00 
AnswerRe: Allowing editing of the 'Time' portion of the DateTime PinmemberKMalone12-May-10 0:54 
GeneralRe: Allowing editing of the 'Time' portion of the DateTime PinmemberKim Bilida16-Jul-10 6:58 
GeneralGreat! PinmemberMember 411002520-Mar-10 13:05 
Generalthis.Value VS this.ToShortDateString() PinmemberDrew Loika1-Feb-10 12:38 
GeneralRe: this.Value VS this.ToShortDateString() Pinmembervic_ch20001-Feb-10 19:23 
Generalthank you PinmemberSamir the Boy13-Jan-10 1:06 
GeneralDBNull vs null PinmemberDrew Loika17-Dec-09 14:09 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150414.1 | Last Updated 17 Aug 2009
Article Copyright 2009 by vic_ch2000
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid