Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

vb.net - Conversion from type 'DBNull' to type 'Date' is not valid

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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...