Evaluating dates against NULL values

What is the best way of evaluating dates against NULL values, as the following code generates an invalid cast exception, when there is a NULL value forFailureDate?

If blnStarStockAnd dteDay = row("FailureDate")Or dteDay = GetMaxTradeDate
(row("CompanyKey"), row("CompanyCode"))Then
' Do Something
EndIf

Thanks

[970 byte] By [JobLot] at [2007-12-16]

«« can't find xp_sqlagent_proxy_account procedure
»» h get * /recursive

# 1

Use row.IsNull(column) to check if it's NULL.

DanielRieck at 2007-9-9 >

top of Msdn Tech,Visual Basic,Visual Basic General...

# 2

Hi,

You can use DBNull class for comparing nullvalues when comparing with data in a datarow.

If (blnStarStock And (Not (row("FailureDate") Is DBNull.Value))
And dteDay = row("FailureDate")
Or dteDay = GetMaxTradeDate(
row("CompanyKey"), row ("CompanyCode"))) Then

End If

Regards,
Vikram

Vikram at 2007-9-9 >

# 3

If (blnStarStock And (Not (row("FailureDate") Is DBNull.Value)) _
And dteDay = row("FailureDate") _
Or dteDay = GetMaxTradeDate(row("CompanyKey"), row("CompanyCode"))) Then

End If

This still results in error: Operator '=' is not defined for the type 'Date' and type 'DBNull'

JobLot at 2007-9-9 >

# 4

because the expr And dteDay = row("FailureDate") is getting evaluated. You need to short circuit the expr using AndAlso operator as follows

If ((Not row.IsNull("FailureDate") And blnStarStock) AndAlso dteDay = row("FailureDate")) _
Or dteDay = GetMaxTradeDate(row("CompanyKey"), row("CompanyCode")) Then

End If

The way AndAlso works is

If expr1 evaluates to False, expr2 is not evaluated, and result is False (the operator is said to have short-circuited the expression). If expr1 evaluates to True and expr2 evaluates to False, result is False. If both expr1 and expr2 evaluate to True, result is True

PankajBanga at 2007-9-9 >

# 5

Having another problem

Dim dte As Date
dte = cmd.ExecuteScalar

This results in error when ExecuteScalar returns a null value. How can i handle this?

JobLot at 2007-9-9 >

# 6

Think a little laterally. Assign the the result of ExecuteScalar to a variable of a type that will accept a null value or a date, like Object. You can then test that variable to see whether it is null.

jmcilhinney at 2007-9-9 >

# 7

thanks

JobLot at 2007-9-9 >

# 8

The alternative would be to use a DataReader instead and check the value before assigning it to any variable.

jmcilhinney at 2007-9-9 >

Visual Basic Hot Topic

Visual Basic New Topic

Visual Basic

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech