Conversion from empty Date to type 'Date' is not valid

My Function works fine until I return an empty or NULL result, then i get the error: Conversion from string "" to type 'Date' is not valid.

VB.NET:

Public Function GetNextWaitListed(ByVal ClassName As String, ByVal ClassDate As Date, ByVal ClassTime As String) As String
    Dim connStr As String = ConfigurationManager.AppSettings.Get("TechTrainingConn")
    Dim conn As New Data.OleDb.OleDbConnection(connStr)
    Try
        conn.Open()
        Dim sql As String = "SELECT Min(SubmitTime) FROM [EnrollmentsTbl]" & _
                       " WHERE [ClassName] = """ & ClassName & """" & _
                       " AND [ClassDate] = #" & ClassDate & "#" & _
                       " AND [ClassTime] = """ & ClassTime & """" & _
                       " AND [Waitlisted] = True" & _
                       " AND [Completed] = False" & _
                       " AND [Enrolled] = True" 
        Dim comm As New Data.OleDb.OleDbCommand(sql, conn)
        Dim result As Date = comm.ExecuteScalar()

        If Not String.IsNullOrEmpty(result) Then
            Return result
        End If

    Catch ex As Exception
        Response.Write(ex)
    Finally
        conn.Close()
    End Try

End Function

I have tried the following but am a little confused since I returning a date

If Not String.IsNullOrEmpty(result) Then
   Dim oDate As DateTime = Convert.ToDateTime(result)
   Return oDate
End If

1 answer

  • answered 2018-01-13 18:53 BarclayVision

    The best solution for me was to return into object and if object was empty do the else with dummy date

    Public Function GetNextWaitListed(ByVal ClassName As String, ByVal ClassDate As Date, ByVal ClassTime As String) As String
        Dim connStr As String = ConfigurationManager.AppSettings.Get("TechTrainingConn")
        Dim conn As New Data.OleDb.OleDbConnection(connStr)
    
        Try
            conn.Open()
            Dim sql As String = "SELECT Min(SubmitTime) FROM [EnrollmentsTbl]" & _
                           " WHERE [ClassName] = """ & ClassName & """" & _
                           " AND [ClassDate] = #" & ClassDate & "#" & _
                           " AND [ClassTime] = """ & ClassTime & """" & _
                           " AND [Waitlisted] = True" & _
                           " AND [Completed] = False" & _
                           " AND [Enrolled] = True" 
            Dim comm As New Data.OleDb.OleDbCommand(sql, conn)
            Dim Obj As Object = comm.ExecuteScalar()
                If (Obj IsNot Nothing) AndAlso (Obj IsNot DBNull.Value) Then
                    Dim matches As String = Obj.ToString
                    Dim result As Date = Convert.ToDateTime(matches)
                    Return result
                Else
                    Dim result As Date = Convert.ToDateTime("01/01/1900")
                    Return result
                End If
    
        Catch ex As Exception
            Response.Write(ex)
            If Not conn Is Nothing Then
                conn.Close()
            End If
        Finally
            conn.Close()
        End Try
    
    End Function