Saving Null values into DateTime Fileds in SQL Database is not as straight forward as it seems in C# and Windows Forms. If you want to save a Null value into Database you have to write a custom code otherwise you encounter various interesting errors.
To solve a problem in one of my projects I used the following code:
Step 1:
Because I wanted that Data is saved when DataGridView Row Validating Event fires I wrote the folowing code in DataGridView_RowValidating event.
// Save Data to Tables on RowChange
private void vozilaDataGridView_RowValidating(object sender, DataGridViewCellCancelEventArgs e)
{
// If field TxtDatumNakupa is blank
if (TxtDatumNakupa.Text == "")
{
try
{
// Define Nullable DateTime myDate
DateTime? myDate = new DateTime();
// Set it to Null
myDate = null;
// Update the filed in DataSet with null value and toolStripStatusLblVoziloID.Text is the ID where Update occurs in DB
vrednost_VozilaTableAdapter.UpdateQueryDatumNakupaUpdate(null, Convert.ToInt32(toolStripStatusLblVoziloID.Text));
}
catch (FormatException)
{
MessageBox.Show("Error writing to DB!");
}
}
}
Step 2:
Since the TextBox that is bound to DateTime also needs to be validated for correct input I used a TextBox Validating Event to check for correct DateTime format
private void TxtDatumNakupa_Validating(object sender, CancelEventArgs e)
{
// Define date
DateTime date;
// If its not same format as date and txtDatumNakupa.text is also not empty
if (!DateTime.TryParse(TxtDatumNakupa.Text, out date) && TxtDatumNakupa.Text != "")
{
MessageBox.Show("Date in such format does not exist!", "Row Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
e.Cancel = true;
}
}
Step 3:
Because TextBox is DataBound and it does not let click out of a TextBox if it is empty, I found that in Design View where DataBinding is done to TextBox you have to add DataSourceUpdateMode.OnPropertyChanged, "" so it lets you pass by with an empty field.
this.TxtDatumPogodbe.DataBindings.Add(new System.Windows.Forms.Binding("Text", this.vrednost_VozilaBindingSource, "Datum_Pogodbe", true, System.Windows.Forms.DataSourceUpdateMode.OnPropertyChanged, ""));
That is it! When I tested the example I was able to normaly save Null value into SQL.
* Note that Nullable DateTime myDate does not need to be declared here I left it inside in case you want to save also a DateTime below and modify example a bit.
ae0d2a77-4656-469d-8d4f-60d2e6850b77|0|.0