Click here to Skip to main content
14,921,612 members
Articles / Database Development / SQL Server
Article
Posted 17 Aug 2009

Stats

123.8K views
12K downloads
46 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)

Share

About the Author

vic_ch2000
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 1:10
MemberMember 1144181710-May-16 1:10 
QuestionCan't insert the calendar column value to SQL server Pin
Mozaharul Haque19-Mar-14 22:36
MemberMozaharul Haque19-Mar-14 22:36 
AnswerCorrection Pin
Member 1003024528-Jan-14 18:50
MemberMember 1003024528-Jan-14 18:50 
GeneralExcellent Article Pin
Vishnuvardhan Reddy M15-Dec-13 18:33
MemberVishnuvardhan Reddy M15-Dec-13 18:33 
QuestionCustom formating Pin
Member 85931649-Jul-13 4:12
MemberMember 85931649-Jul-13 4:12 
GeneralThank you very much !! Pin
Mazen el Senih28-Jul-12 1:18
professionalMazen el Senih28-Jul-12 1:18 
QuestionReallly good article! Pin
vishme27-Mar-12 0:12
Membervishme27-Mar-12 0:12 
Questiondoubt! Pin
choosechrist26-Feb-12 21:14
Memberchoosechrist26-Feb-12 21:14 
BugRow delete problem Pin
Member 817129618-Nov-11 6:38
MemberMember 817129618-Nov-11 6:38 
QuestionSome bug... Pin
domivan24-Aug-11 4:37
Memberdomivan24-Aug-11 4:37 
QuestionRe: Some bug... Pin
djax5-Mar-13 0:40
Memberdjax5-Mar-13 0:40 
GeneralBrilliant! Thanks so much Pin
Daniel Neal12-May-11 0:46
MemberDaniel Neal12-May-11 0:46 
Generalediting problem. Pin
Mohsin Hawra14-Jul-10 22:22
MemberMohsin Hawra14-Jul-10 22:22 
GeneralRe: editing problem. Pin
Razze11-Oct-10 2:07
MemberRazze11-Oct-10 2:07 
AnswerRe: editing problem. Pin
cje1235-Dec-10 16:41
Membercje1235-Dec-10 16:41 
GeneralRe: editing problem. Pin
wouterdebo12-Jun-12 3:33
Memberwouterdebo12-Jun-12 3:33 
GeneralGreat job Pin
khalil Youssef30-Jun-10 21:14
Memberkhalil Youssef30-Jun-10 21:14 
GeneralGreat Job Pin
Mohammad Rafi Bin Abd Alim16-Jun-10 20:11
MemberMohammad Rafi Bin Abd Alim16-Jun-10 20:11 
QuestionAllowing editing of the 'Time' portion of the DateTime [modified] Pin
Kizza00711-May-10 22:00
MemberKizza00711-May-10 22:00 
AnswerRe: Allowing editing of the 'Time' portion of the DateTime Pin
Kizza00711-May-10 23:54
MemberKizza00711-May-10 23:54 
GeneralRe: Allowing editing of the 'Time' portion of the DateTime Pin
Kim Bilida16-Jul-10 5:58
MemberKim Bilida16-Jul-10 5:58 
GeneralGreat! Pin
zeiddev20-Mar-10 12:05
Memberzeiddev20-Mar-10 12:05 
Generalthis.Value VS this.ToShortDateString() Pin
Drew Loika1-Feb-10 11:38
MemberDrew Loika1-Feb-10 11:38 
GeneralRe: this.Value VS this.ToShortDateString() Pin
vic_ch20001-Feb-10 18:23
Membervic_ch20001-Feb-10 18:23 
Generalthank you Pin
Samir the Boy13-Jan-10 0:06
MemberSamir the Boy13-Jan-10 0: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.