[RESOLVED] Checking for a Null date

I use this code in my VBA applications to test if a date is Null:

Code:

---

Dim varItem As Variant
Dim dtmMaxDate as Date
varItem = DMax("RecDate", "Asset Prices")
If Not IsNull(varItem) Then
    dtmMaxDate = CDate(varItem)
End If

---

Is there a similar technique in VB.Net?

For example, this code raises an error when MaxOfRecDate is Null:

Code:

---

Dim dtmDate As Date
strSQL = "SELECT Max([Asset Prices].RecDate) AS MaxOfRecDate " & _
    "FROM [Asset Prices] " & _
    "WHERE ([Asset Prices].AssetID=" & lngAssetID & ")"
cmd.CommandText = strSQL
dtmDate = Convert.ToDateTime(cmd.ExecuteScalar())

---

My workaround has been to check if the count is 0 ("SELECT Count([Asset Prices].RecDate) AS CountOfRecDate ...") and then if it is not to do the second query to get the date value:

Code:

---

strSQL = "SELECT Count([Asset Prices].RecDate) AS CountOfRecDate " & _
    "FROM [Asset Prices] " & _
    "WHERE ([Asset Prices].AssetID=" & lngAssetID & ")"
cmd.CommandText = strSQL
intResult = Convert.ToInt32(cmd.ExecuteScalar)
If intResult = 0 Then
    dtmDate = #12:00:00 AM#
Else
    strSQL = "SELECT Max([Asset Prices].RecDate) AS MaxOfRecDate " & _
        "FROM [Asset Prices] " & _
        "WHERE ([Asset Prices].AssetID=" & lngAssetID & ")"
        cmd.CommandText = strSQL
        dtmDate = Convert.ToDateTime(cmd.ExecuteScalar())
End If

---

Is there a way to avoid having to use 2 queries to check for a Null date?

Shooting from the hip here (so it's untested...)... but...

Code:

---

Dim dtmDate As Date? '<-- note the change here ? means "of nullable"
strSQL = "SELECT Max([Asset Prices].RecDate) AS MaxOfRecDate " & _
    "FROM [Asset Prices] " & _
    "WHERE ([Asset Prices].AssetID=" & lngAssetID & ")"
cmd.CommandText = strSQL
dtmDate = cmd.ExecuteScalar() '<--- another change
dtmDate.HasValue 'Or it might be dtmDate.IsNull or something like that ... there is a method that will tell you if the object is null or not

---

should work.

-tg

disclaimer - this is untested, and is provided as-is and not covered by any warranty of any kind.

It's actually a little more difficult, I think. techgnome may be right, but here's what I'm thinking.

VBA is a little lax with its typing, so it can allow its date types to be null/nothing. VB .NET has much stricter type requirements. DateTime is a Structure, which means it can't actually be assigned Nothing. techgnome's example uses a newish feature called "Nullable types" to get something like it. MS made a class, Nullable(Of T), that can wrap a Structure like DateTime and provide behavior as if it were able to be assigned Nothing. The ? at the end of the type name tells the compiler you'd like a Nullable(Of Date).

But.

ADO .NET, the APIs that provide access to databases in .NET, was developed before Nullable(Of T) existed. So it had to approach the problem a different way. There is a special type named DBNull that exists solely to represent "this value is null" in ADO .NET. There's a couple of other ways null might get out.

In your case, you're calling ExecuteScalar(). The documentation for ExecuteScalar() says:

Quote:

---

If the value in the database is null, the query returns DBNull.Value.

---

So there's your answer: if the date's null, you can tell by checking against DBNull.Value:

Code:

---

Dim dtmDate As Date
strSQL = "SELECT MAX...."
cmd.CommandText = strSQL

Dim result As Object = cmd.ExecuteScalar()

If result.Equals(DBNull.Value) Then
    ' It was null!
Else
    dtmDate = Convert.ToDateTime(result)
End If

---

It's possible Nullable(Of T) has some syntax sugar to make techgnome's answer work, but this should also work.

Hi techgnome -

I have the "Option Strict" compiler directive set so I have to convert the cmd.ExecuteScalar to something. I tried using your suggestion of declaring the dtmDate a Nullable type, but the Convert.ToDate raises a conversion error.

But your code got me thinking some more about it. Here is a variation that works:

Code:

---

Dim strDate As String
strSQL = "SELECT Max([Asset Prices].RecDate) AS MaxOfRecDate " & _
    "FROM [Asset Prices] " & _
    "WHERE ([Asset Prices].AssetID=" & lngAssetID & ")"
cmd.CommandText = strSQL
strDate = Convert.ToString(cmd.ExecuteScalar())
If strDate = "" Then
    dtmDate = #12:00:00 AM#
Else
    dtmDate = CDate(strDate)
End If

---

Hi Sitten Spynne -

Thanks for your suggestion. The key to your suggestion was to declare the "result" as an Object type variable. I would not have thought of that. What I had tried (unsuccessfully) was to declare a VariantType variable (trying to mimic my VBA code that used the Variant type varItem variable) and use it to receive the results of the cmd.ExecuteScalar. Like I said, this didn't work.

Object is sort of like Variant. The truth is a little more complicated and I don't know all the nuance of Variant. But the hint was in the documentation.

https://msdn.microsoft.com/en-us/lib...v=vs.110).aspx

Code:

---

Function ExecuteScalar() As Object

---

Generally, if your variable type isn't matching the return type of a function call, you're doing something wrong.

(The technical details:

VB .NET is object-oriented. That means everything you interact with in code is an object of some sort. Objects can "inherit" from each other, this is a way to share and extend behaviors. If two objects inherit from each other, you can perform conversions between them in a fairly safe manner. For example, a Catfish type might inherit from a Fish type. That means it's safe to convert from Catfish to Fish in all circumstances.

Something that ties the type system together is that every type ultimately inherits from Object. That means it's always safe to convert any type to Object. It also means a variable of type Object can hold a value of any type. That's why ExecuteScalar() returns a value of type Object. You know, on your end, the result is probably an Integer, Long, Date, etc. But the only thing those types have in common is Object, so that's what ExecuteScalar() has to return.

The alternative is how some other APIs work, like the IDataReader types used to read more complex queries. They have a GetValue() method that returns an Object, but also more specific ones like GetInt32() and GetDateTime().)

Sitten Spynne -

Really appreciate your helpful explanation! I learned a lot from this seemly trivial question. Just goes to show that it never hurts to ask...