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

Question

I am getting this exception from the following VB.NET code for only certain months:

System.InvalidCastException: Conversion from type 'DBNull' to type 'Date' is not valid.

It happens on the line:

If CDate(dRow("CompleteDate")).ToString("d") = arrWeekYear(i, 1).ToString("d") Then

If I understand this correctly, then the problem is I am trying to compare some NULL values for CompleteDate to a non-NULL value.

How do I fix this? For some dates, this code above runs perfectly. However for some of the more recent records, it does not. But I am able to get output from the T-SQL query with same date range and there are no errors; it runs quickly also. And I examined this T-SQL query for both "Date_Completed" and "Review_Date" NULL values, but either way, "CompleteDate" was always = NON-Null value. So I do not understand how this is happening.

Here is my VB.NET query:

            commandstring = "SELECT Batch_Records.Part_Number, Batch_Records.Lot_Number, Batch_Records.Date_Received, " & _
                "IsNull([Date_Completed], [Review_Date]) AS [CompleteDate], Batch_Records.Error, " & _
                "Batch_Records.[Group], Batch_Records.MFG, Batch_Records.MFG2, Batch_Records.QC, Batch_Records.QC2, " & _
                "QC_CODES.CODE_DESC, DATEADD(DD, 1 - DATEPART(DW, Batch_Records.Date_Received), Batch_Records.Date_Received) AS SundayDate " & _
                "FROM EXCEL.Batch_Records LEFT JOIN EXCEL.QC_CODES ON Batch_Records.Part_Number = QC_CODES.CODE_ID " & _
                "WHERE (Batch_Records.[Group]" & TheGroup & " AND Batch_Records.Date_Received > '" & FirstWeek & "' AND Batch_Records.Date_Received < dateadd(Day, 1, '" & LastWeek & "'))" & _
                   "ORDER BY Batch_Records.Date_Received"

When I add this line above the error-causing line above, my report times out for these months. So what to do?

If Not dRow("CompleteDate") Is System.DBNull.Value Then

Answer 1

check for null with dRow.IsNull("CompleteDate") before you ask for the value.

Or if this is 'supposed to be' impossible, change your query to never return rows where it can be null.