Results 1 to 7 of 7

Thread: [RESOLVED] Checking for a Null date

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Resolved [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.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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.
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,578

    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.

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    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.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    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.

  6. #6
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,578

    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.

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    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
  •  



Click Here to Expand Forum to Full Width