DateTime nullable exception as a parameter

Question

I have a Search Form that can search by a few different fields. The problem field is birthDate. On the form it is a string. In the SQL 2005 db it is a DateTime that can be null.

The code below is sequential as far as declaring the variable on the form and then setting it. Then the call to the BLL and then the call to the DAL.

On this line -->

dgvSearchResults.DataSource =ConnectBLL.BLL.Person.Search(_firstName,_middleName,_lastName,_sSN, (DateTime)_birthDate,_applicationID,_applicationPersonID,_fuzzy);

I am getting a "Nullable object must have value" error.

I assume because I didn't give a Date param and then tried to Cast to a non-nullable DateTime. Correct?

What can I do about it? There has to be a way around it. This exact same BLL & DAL work on the web app version.

EDIT 1

A few things to note. I was under the impression that DateTime? was the same as Nullable<DateTime> Correct?

Also, I don't see how I can put in an arbitrary value for a field like BirthDate. I suppose I could build in a bunch of filters in my business layer but that seems tho "Hack" way. Maybe not. Also, it bothers me that this same BLL and DAL with this same DB allow nulls in the BirthDate field in the WebApp version of this program. Any ideas there? The DB has null in it for those entries with out a date.

Search.cs

public DateTime? _birthDate;
_birthDate = null;
if (datBirthDate.Text != string.Empty)
    _birthDate = Convert.ToDateTime(datBirthDate.Text);
dgvSearchResults.DataSource=ConnectBLL.BLL.Person.Search(_firstName,_middleName,_lastName,_sSN, (DateTime)_birthDate,_applicationID,_applicationPersonID,_fuzzy);

ConnectBLL --> Person.vb

Public Shared Function Search(ByVal firstName As String, ByVal middleName As String, ByVal lastName As String, ByVal SSN As String, ByVal birthDate As Date, ByVal applicationId As Integer, ByVal applicationPersonId As String, ByVal fuzzy As Boolean) As Data.DataTable
Try
 Dim tbl As Data.DataTable = DAL.Connect.SearchPerson(firstName, middleName, lastName, SSN, birthDate, applicationId, applicationPersonId, fuzzy)

 If tbl.Rows.Count > 0 Then
  tbl.DefaultView.Sort = "Last Name ASC, First Name ASC, Middle Name ASC"
 End If
 Return tbl
Catch
 Throw
End Try

End Function

ConnectDAL --> Connect.vb

Public Shared Function SearchPerson(ByVal _FirstName As String, ByVal _MiddleName As String, ByVal _LastName As String, ByVal _SSN As String, ByVal _BirthDate As Date, ByVal _ApplicationID As Integer, ByVal _ApplicationPersonID As String, ByVal _Fuzzy As Boolean) As Data.DataTable
Dim test As New clsSqlResponseTime
Dim dt As New Data.DataTable
Try
 Using cnn As New SqlClient.SqlConnection(ConnectROConnectionString)
  Dim cmd As New SqlClient.SqlCommand()
  cmd.Connection = cnn
  If _Fuzzy Then
   cmd.CommandText = strSearchPersonFuzzyStoredProcedure
  Else
   cmd.CommandText = strSearchPersonStoredProcedure
  End If
  cmd.CommandType = CommandType.StoredProcedure
  If Not IsNothing(_FirstName) Then cmd.Parameters.Add(New SqlClient.SqlParameter("@FirstName", _FirstName))
  If Not IsNothing(_MiddleName) Then cmd.Parameters.Add(New SqlClient.SqlParameter("@MiddleName", _MiddleName))
  If Not IsNothing(_LastName) Then cmd.Parameters.Add(New SqlClient.SqlParameter("@LastName", _LastName))
  If Not IsNothing(_SSN) Then cmd.Parameters.Add(New SqlClient.SqlParameter("@SSN", _SSN))
  If Not (_BirthDate = Date.MinValue) Then cmd.Parameters.Add(New SqlClient.SqlParameter("@BirthDate", _BirthDate))
  If Not IsNothing(_ApplicationPersonID) Then cmd.Parameters.Add(New SqlClient.SqlParameter("@ApplicationID", _ApplicationID))
  If Not IsNothing(_ApplicationPersonID) Then cmd.Parameters.Add(New SqlClient.SqlParameter("@ApplicationPersonID", _ApplicationPersonID))

  Dim da As New SqlClient.SqlDataAdapter(cmd)
  da.Fill(dt)
 End Using
Catch
 Throw
End Try
test.Log("SearchPerson", "Connect")
Return dt

End Function

Answer 1

You can do something like this:

dgvSearchResults.DataSource=ConnectBLL.BLL.Person.Search(_firstName,_middleName,_lastName,_sSN, _birthDate.HasValue ? _birthDate.Value : new DateTime() ,_applicationID,_applicationPersonID,_fuzzy);

Basically, if it's null, just default it to whatever the default of DateTime is (I believe it's 1/1/0001).

Answer 2

You look like you are attempting to cast a possible Null value to DateTime in Search.cs on this line.

dgvSearchResults.DataSource=ConnectBLL.BLL.Person.Search(_firstName,_middleName,_lastName,_sSN, (DateTime)_birthDate,_applicationID,_applicationPersonID,_fuzzy);

That would cause this error. If the string is empty you will cast null to DateTime which would blow up. I suggest your Person.Search method take Nullable<DateTime> as the parameter and handle the null case there or set it to DateTime.MinValue and treat that as null in Person.Search.

Answer 3

Instead of setting _birthDate to null, why not set it with some DateTime value? That way it can't be null being passed to the BLL call. Either DateTime.MinValue or DateTime.Now can work.

Answer 4

It appears to me that you're not using the birthDate value at all until the third function, where it's being added to a Parameters collection. In that third function it's already being tested against Date.MinValue before adding it. Simply change the first function to this:

public DateTime _birthDate;
if (string.IsNullOrEmpty(datBirthDate.Text))
    _birthDate = DateTime.MinValue;
else
    _birthDate = Convert.ToDateTime(datBirthDate.Text);
dgvSearchResults.DataSource=ConnectBLL.BLL.Person.Search(_firstName,_middleName,_lastName,_sSN,_birthDate,_applicationID,_applicationPersonID,_fuzzy);

That's the easiest way out, but not necessarily the most correct. I would change all three functions to use a Nullable DateTime like so:

Function 1

public DateTime? _birthDate = null;
if (!string.IsNullOrEmpty(datBirthDate.Text))
    _birthDate = Convert.ToDateTime(datBirthDate.Text);
dgvSearchResults.DataSource=ConnectBLL.BLL.Person.Search(_firstName,_middleName,_lastName,_sSN,_birthDate,_applicationID,_applicationPersonID,_fuzzy);

Function 2 and 3 definitions should look like:

Public Shared Function Search(ByVal firstName As String, ByVal middleName As String, ByVal lastName As String, ByVal SSN As String, ByVal birthDate As Nullable(Of Date), ByVal applicationId As Integer, ByVal applicationPersonId As String, ByVal fuzzy As Boolean) As Data.DataTable

Public Shared Function SearchPerson(ByVal _FirstName As String, ByVal _MiddleName As String, ByVal _LastName As String, ByVal _SSN As String, ByVal _BirthDate As Nullable(Of Date), ByVal _ApplicationID As Integer, ByVal _ApplicationPersonID As String, ByVal _Fuzzy As Boolean) As Data.DataTable

And the line in Function 3 that uses the birthDate:

If (_BirthDate.HasValue) Then cmd.Parameters.Add(New SqlClient.SqlParameter("@BirthDate", _BirthDate.Value))