Gracefully Handling Nulls

BrianWren · May 19th, 2005, 03:09 PM

In MS Access VBA, there is a terrific function, Nv(). It takes two arguments. The first argument is tested for whether it is Null or not.
If it is [u]not</u> Null, the first argument is returned.
If it [u]is</u> Null, the second argument is returned.
So then, the following:

Code:

    Dim x As Variant
    Dim v As Variant

    x = Null
    v = Nz(x, "")    ' v now is an empty string
    v = Nz(x, 5)     ' v now is an integer, 5.

    x = 1
    v = Nz(x, "")    ' v now equals 1

    x = "Hey..."
    v = Nz(x, "")    ' v now equals "Hey..."

I liked this so much, I added that functionality, with the same exact syntax, to a VB6 library of functions that I included with my projects.
I would much prefer to find that .NET has covered this, than to once again write my own...

Can it be that MS [u]still</u> has not added this functionality to VB? Oracle has a function that will do the same thing in SQL (NVL()), Access has itâit seems that more than one company has found this to be a useful process...

I am presuming that ADO.NET still produces Nulls when the contents of the DB for a given field in a given row is carrying a null in the data.

planoie · May 19th, 2005, 07:58 PM

Brian,

You're absolutely right. This would be great to have in .NET, however I've yet to run across it. I have a helper function for dealing with this in my systems. I just spent some time browsing thru the Microsoft.VisualBasic namespace and still didn't find anything that did that.

-Peter

BrianWren · May 20th, 2005, 09:57 AM

I just this morning came acros the ability to decorate (if that's the right term) a function declaration to prevent seeing any of its code while single-stepping. If a routine has

Code:

    <System.Diagnostics.DebuggerStepThrough()> Private Sub . . .

in front of it, it will be skipped over, just as internal functions like SomeString.Replace() would be.

Armed with that, I can make my Nz() function, and not have to continually step through its already-tested code.

My VB6 code is

Code:

Public Function Nz(Value As Variant, Optional ValueIfNull As Variant) As Variant

    On Error GoTo Er

    If IsNull(Value) Then
            If IsMissing(ValueIfNull) Then Nz = 0 _
                                      Else Nz = ValueIfNull
    Else
        Nz = Value
    End If

    Exit Function

Er: Err.Raise Err.Number, "Function Nz()", Err.Description

End Function

BrianWren · May 25th, 2005, 02:06 PM

Hereâs what I wound up with:

Code:

<System.Diagnostics.DebuggerStepThrough()> _
Public Function Nz(ByVal Value, Optional ByVal ValueIfNull = 0)

    If Value Is DBNull.Value Then Return ValueIfNull _
                             Else Return Value

End Function

I wish I didnât [u]have</u> to specify a value for an Optional argument, but I do. (What are you gonna do?)