VB.Net DateTimePicker NULL value

Question

I am using vb.net to load data from sql server database. One table has datatime column. I use bindingsource to bind controls to columns. This is how I bind datetimepicker to data column:

 returnDateDateTimePicker.DataBindings.Add(New Binding("value", bsRegister, "returnDate"))

This date time picker has checkbox showed in it.

When I open the form, if the column has datetime value not null or empty the data will be displayed properly and the checkbox will checked.

Now when I want to set the return date as null to be saved to the database it does not save it as null!

I tried working around with it as follows:

Private Sub ReturnDateDateTimePicker_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ReturnDateDateTimePicker.MouseUp
    Try
        If ReturnDateDateTimePicker.Checked = True Then
            ReturnDateDateTimePicker.CustomFormat = "dd-MMM-yyyy hh:mm tt"
        Else
            ReturnDateDateTimePicker.CustomFormat = "    -- 'Select Date' --  "
        End If
    Catch ex As Exception
        MsgBox(Err.Description)
    End Try
End Sub

When I uncheck the datetimepicker text will be changed to '-- Select Date --'.

I thought this would solve the problem but returnDate always has a value! What is wrong with my code and whats the solution?

try this: [Stackoverflow:Set DateTimePicker value to be null](http://stackoverflow.com/questions/5947726/set-datetimepicker-value-to-be-null) — Fabio, Apr 27 '14 at 07:10

score 0 · Answer 1 · edited Jun 19 '19 at 05:11

I just solved the same issue, it is how to make resigned date for active employee has null value (because they are still working in the company).

I made a little program with VS 2017 it is VB.Net language, the Boss asked me to prepare Employee list program complete with their Join Date and Resigned Date in the database.

It was a problem for me when I input active employee data because I can't fill the resigned date (they are still working) and VB not allow the datetimepicker has null value.

Please refer to my code to see the solution; I only add an object to catch null value and send it to the database as regined date.

Private Sub btnsave_Click(sender As Object, e As EventArgs) _
        Handles btnsave.Click

        Dim exresigndate As String = ""

        If rbactive.Checked = True Then
            status = rbactive.Text
            exresigndate = "Null"
        ElseIf rbresigned.Checked = True Then
            status = rbresigned.Text
            exresigndate = "@ResignDate"
        End If


        Try
            Call SearchEmpID()
            If Not DR.HasRows Then
                Call connection()
                Dim CMD As SqlCommand
                Dim SQL As String

                SQL = "INSERT INTO Employee VALUES (@EmpID, @EmpName, @DeptName, @EmpStatus, @JoinDate, " & exresigndate & ", @Address, @Photo)"
                CMD = New SqlCommand(SQL, CONN)
                CMD.Parameters.AddWithValue("@EmpID", txtid.Text)
                CMD.Parameters.AddWithValue("@EmpName", txtname.Text)
                CMD.Parameters.AddWithValue("@DeptName", cmbdept.Text)
                CMD.Parameters.AddWithValue("@EmpStatus", status)
                CMD.Parameters.AddWithValue("@JoinDate", Format(dtpjoin.Value, "MM-dd-yyyy"))
                If rbresigned.Checked = True Then
                    CMD.Parameters.AddWithValue("@ResignDate", Format(dtpresigned.Value, "MM-dd-yyyy"))
                End If
                CMD.Parameters.AddWithValue("@Address", txtaddress.Text)
                Dim MemoryStream As New MemoryStream
                PictureBox1.BackgroundImage.Save(MemoryStream,
                    PictureBox1.BackgroundImage.RawFormat)
                Dim Dphoto As Byte() = MemoryStream.GetBuffer
                Dim Images As New SqlParameter("@Photo", SqlDbType.Image)
                Images.Value = Dphoto
                CMD.Parameters.Add(Images)
                CMD.ExecuteNonQuery()
            Else
                Call koneksi()
                Dim CMD As SqlCommand
                Dim SQL As String
                SQL = "UPDATE Employee SET EmpName='" & txtname.Text & "',DeptName='" & cmbdept.Text & "',EmpStatus='" & status & "',JoinDate='" & Format(dtpjoin.Value, "MM-dd-yyyy") & "', ResignDate='" & Format(dtpresigned.Value, "MM-dd-yyyy") & "', Address='" & txtaddress.Text & "' ,Photo='" & SqlDbType.Image & "' where EmpID='" & txtid.Text & "'"
                CMD = New SqlCommand(SQL, CONN)
                CMD.ExecuteNonQuery()
                MsgBox("Saved")
            End If
            TextBox1.Clear()
            PictureBox1.Image = Nothing
            CMD.Dispose()
            CONN.Close()
            Call Awal()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

I have two dateTimePickers , one is for join date and other is for resigned date. each datetimepicker will be activated according to what's their employment status whether Active or Resigned and they will be enabled or disabled by below code:

 If rbactive.Checked = True Then
 status = rbactive.Text
 exresigndate = "Null"
 ElseIf rbresigned.Checked = True Then
 status = rbresigned.Text
 exresigndate = "@ResignDate"
 End If

*rb=radiobutton above of the radiobutton code I described the exresigndate as String and give null value.

Dim exresigndate As String = ""

so I can call it in following code.

then I put exresigndate into insert syntak to fill the ResignDate field.

SQL = "INSERT INTO Employee VALUES (@EmpID, @EmpName, @DeptName, @EmpStatus, @JoinDate, " & exresigndate & ", @Address, @Photo)"

After that I give condition to call exrisgndate(null value) for active employee and ResignDate value as dtreigned.value for resigned employee.

For further explanation take a look at this input form.

VB.Net DateTimePicker NULL value

1 Answers1