Null datetime

I'm working on a project right now that is a user interface to a SQL database. Pretty standard stuff, I think. In creating my user forms, I've run into some issues where I want the user to be able to do something that I I've been able to do on other forms and that they're likely going to want to do, but I can't figure out how to do it! But with patience and a lot of searching, I made it work. This first post, and actually the reason for this blog, is all because of null dates.

How many forms do you encounter that have a date field but you want or need the option to leave it blank? My database table has a datetime column and my form has a datetimepicker on it. Well, you can't just "blank out" a datetimepicker. I didn't want to confuse the user with minimum dates, fake dates, or even the grayed out date in the datetimepicker when the checkbox version is used. I didn't want to use a plain textbox because I like the datetimepicker calendar. What I wanted was the datetimepicker box to be blank when the user first opens the form and if he/she doesn't enter anything then it should stay blank. And if the user puts a date in and then wants it gone, there should be a way to get rid of it. I didn't realize this would be so difficult, but I made it work. Basically, I make the datetimepicker custom format "blank" when I need it to be blank and I updated the database with a null value for the datetime field when the user "clears out" the datetime field, using a menu option. Here are the specifics:

1. Make the datetimepicker box blank.

If it's a new form that doesn't show any data, I initialize the datetimepicker with a blank custom format:

MyDateTimePicker.CustomFormat = " "      
MyDateTimePicker.Format = DateTimePickerFormat.Custom

If the form is displaying data from the database, then I find out if the value is null (nothing) and if it is, I give it the blank custom format:

If TypeOf (Me.MyTableBindingSource.Current("DateTime")) Is DBNull Then
MyDateTimePicker.CustomFormat = " "      
MyDateTimePicker.Format = DateTimePickerFormat.Custom
End If

2. Use the ValueChanged event for the datetimepicker box to change the format when a user enters something in the box.

Private Sub MyDateTimePicker_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyDateTimePicker.ValueChanged

MyDateTimePicker.CustomFormat = "yyyy/MM/dd HH:mm:ss"

End Sub

3. Allow the user to enter a value and then clear it. For this I used a context menu strip. If the user right clicks on the box, a "Clear Date" menu item appears and they use this to clear the date. But first I needed to create a SQL query for the dataset where I only update the datetime. The SheetID is the primary key in the table so I know I'm choosing the right one to update.

UPDATE (MyTable) SET (DateTime) = null FROM MyTable WHERE (SheetID = @SheetID)

4. The event for the Clear Date context menu strip item updates the table with a null value for the date, calls another query to fill the sheet and then applies the same blank custom format to the datetimepicker box so that it appears blank.

Private Sub ClearDateToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClearDateToolStripMenuItem.Click

Me.MyTableTableAdapter.UpdateNullDateTime(SheetID)

MyDateTimePicker.CustomFormat = " "
MyDateTimePicker.Format = DateTimePickerFormat.Custom

End Sub

5. One other feature I implemented was to allow the user to generate new sheets from the currently displayed sheet. For example, say you have a sheet that contains information for a product ID and you want the more sheets created for different product IDs but with the same information. I accomplished this by inserting new rows into the database for each of the new sheets, using a SQL insert query for the tableadapter with variables for all of the information I wanted to capture. But I found that when the current sheet had a null datetime field the value was entered into the new sheets as 01/01/1900, not null. The way I got around this was to read the value for the datetime directly from the datatable for the currently displayed form and if it was null, I used "nothing" instead of the datetime variable in the insert query. By the way, an error occurred when I tried to just make the variable "copydate" equal to "nothing" so that's why I have to call the update query in an "if" statement.

In this example code the variables copyinfo, copyinfo2, and copydate are set to information on the form that I want to insert into the new record:

If TypeOf (Me.MyTableBindingSource.Current("DateTime")) Is DBNull Then

me.MyTableTableAdapter.insertCopyofSheet(newID, copyinfo, nothing, copyinfo2)

else

me.MyTableTableAdapter.insertCopyofSheet(newID, copyinfo, copydate, copyinfo2)

End If