-
Jun 28th, 2017, 03:09 PM
#1
Thread Starter
Hyperactive Member
[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?
Last edited by Mark@SF; Jun 28th, 2017 at 03:15 PM.
-
Jun 28th, 2017, 03:18 PM
#2
Re: Checking 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.
-
Jun 28th, 2017, 03:40 PM
#3
Re: Checking for a Null date
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:
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.
This answer is wrong. You should be using TableAdapter and Dictionaries instead.
-
Jun 28th, 2017, 03:43 PM
#4
Thread Starter
Hyperactive Member
Re: Checking for a Null date
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
Last edited by Mark@SF; Jun 28th, 2017 at 03:59 PM.
-
Jun 28th, 2017, 03:56 PM
#5
Thread Starter
Hyperactive Member
Re: Checking for a Null date
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.
-
Jun 28th, 2017, 04:05 PM
#6
Re: Checking for a Null date
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().)
This answer is wrong. You should be using TableAdapter and Dictionaries instead.
-
Jun 28th, 2017, 04:23 PM
#7
Thread Starter
Hyperactive Member
Re: Checking for a Null date
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...
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|