Categories: MSDN / DotNet / Java / Scripts / Linux / PHP Ask - La ask - La Answer

Min() / Max() date cast error from type DBNull

When I run the following code I get:
Cast from type 'DBNull' to type 'Date' is not valid.

The datatype for the field is smalldatetime.
I can run it fine from SQL Server

Dim strSQL As String
strSQL = "SELECT MIN(ESDate) AS MinDate, MAX(EFDate) AS MaxDate FROM P3ActWBS WHERE "
strSQL &= "WBSNumber = '" & strWBSN & "'"
Dim cmd2 As New SqlCommand(strSQL, sCon1)
Dim DR2 As SqlDataReader = cmd2.ExecuteReader()
DR2.Read()
If DR2.HasRows = True Then

Dim strMinDate As Date = DR2(0)
Dim strMaxDate As Date = DR2(1)
DR2.Close()
DR2 = Nothing
[732 byte] By [bubberz] at [2007-11-11 6:49:39]
# 1 Re: Min() / Max() date cast error from type DBNull
If your query contains functions such as COUNT, MAX, MIN, etc, it may return a row with NULL values for the functions if there are no rows that satisfy the WHERE clause of the query.

Your query is returning a row with NULL values for the MIN and MAX functions; so the HasRows function evalutate to true. You will have to examine each DataReader column value and evaluate if it is NULL before attempting to assign it to a variable.

Steve :)
shstubbs at 2007-11-11 21:49:56 >
# 2 Re: Min() / Max() date cast error from type DBNull
For example...

Dim strMinDate As Date
If Not IsDBNull(DR2(0)) Then
strMinDate = DR2(0)
End If
Phil Weber at 2007-11-11 21:51:07 >