Previous | Index | Next 

[HOWTO] Deal with Null, Empty, and missing values

VB Migration Partner offers extensive, but not perfect, emulation of Empty and Null values. If these values are assigned to variables or passed to methods defined inside the current project, they are converted to the special Empty6 and Null6 values, respectively, and are correctly recognized by functions such as IsEmpty6 and IsNull6.

By default, the Empty6 constant defaults to Nothing, a value that works well in both cases. For example, the following piece of VB6:

        Function GetTables(ByVal cn as Connection) As Recordset
            Set GetTables = cn.OpenSchema(adSchemaTables, _
                Array(Empty, Empty, Empty, "table"))
        End Function

is converted to the following VB.NET code, which works correctly because the ADODB ConnectSchema method requires an array that contains either a table name or Nothing:

        Function GetTables(ByVal cn as ADODB.Connection) As ADODB.Recordset
            GetTables = cn.OpenSchema(adSchemaTables, _
                Array6(Empty6, Empty6, Empty6, "table"))
        End Function

In some cases, however, you might need to consider Empty and Nothing as separate values, as in this VB6 code snippet:

        Function TestValue(ByVal value As Variant) As Integer
            If value Is Nothing Then
                TestValue = 0
            ElseIf IsEmpty(value) Then
                TestValue = 1
            Else
                TestValue = 2 
            End If
        End Function

VB Migration Partner translates the code to VB.NET as follows:

        Function TestValue(ByVal value As Object) As Short
            If value Is Nothing Then
                Return 0
            ElseIf IsEmpty6(value) Then
                Return 1
            ElseIf 
                Return 2
            End If
        End Function

Unfortunately, this code doesn’t work as expected, because an Empty value can’t be distinguished from the Nothing value. In such a scenario you need to redefine the Empty6 value in the VBLibrary:

        ' add this pragma to Sub Main or startup form’s Load handler
        '## InsertStatement Empty6 = "{[(empty value)]}"

The value that you assign to the Empty6 constant affects the IsEmpty6 function but your change might have other side effects elsewhere in the application, therefore you should pay close attention to the new value you pick for the constant. In many scenarios, selecting the “” (empty string) is a good choice.

Null values are translated to the Null6 property defined in the VBLibrary. This property defaults to DBNull.Value, which is nearly always the correct translation for Null. However, as it happens with the Empty6 value, you can assign it any value you see fit, by either editing the VB.NET or by means of an InsertStatement pragma:

        ' add this pragma to Sub Main or startup form’s Load handler
        '## InsertStatement Null6 = ""

If you have used a NullSupport pragma, VB Migration Partner maps a few string functions – namely Chr, CurDir, Environ, Error, Hex, LCase, LTrim, Mid, Oct, Right, RTrim, Space, and UCase – into a special version with a trailing “6” in its name (e.g. LTrim6). These special versions differ from the standard versions in one detail: if they receive a Null argument they return a Null value to the application. Therefore, by default, they can return DBNull.Value, but this value might break your code. For example, consider the following VB6 code

        Sub Test(value As Variant)
            Dim s As String
            s = "Value is " & LTrim(value)
            '...
        End Sub

When translated to VB.NET this code fails because the & operator doesn’t support the DBNull.Value.

To let developers work around this issue, the VBLibrary defines the VB6Config.ReturnedNullValue value, which is the value that these methods return to the caller when they receive a null argument. Here’s how you can modify the previous code:

        Sub Test(value As Variant)
            '## InsertStatement VB6Config.ReturnedNullValue = ""   ' consider Null as ""
            Dim s As String
            s = "Value is " & LTrim(value)
            '## InsertStatement VB6Config.ReturnedNullValue = DBNull.Value
            '...
        End Sub

Empty and Null values can also appear as default values for Optional parameters, as in this code:

        Sub Test(Optional ByVal value As Variant = Empty, _
            Optional ByVal value2 As Variant = Null)
            '...
        End Sub

VB Migration Partner can’t replace these occurrences with the Empty6 and Null6 fields, however, because VB.NET requires that default values in method definitions be constants, whereas Empty6 and Null6 are defined as fields so that they can be modified by the client application. This is how the previous method is translated to VB.NET

        Sub Test(Optional ByVal value As Variant = MissingEmpty6, _
            Optional ByVal value2 As Variant = MissingNull6)
            ' Fix Empty and Null values in optional parameters
            FixOptionalParameter6(value, value2)
            '...
        End Sub

Please keep in mind that VB Migration Partner is unable to correctly translate Empty and Null values that appear in constant definitions. For example, the following VB6 code:

        Const MYNULLVALUE As Variant = Null

is translated to:

        Const MYNULLVALUE As Object = Null6

but this statement causes a compilation error under VB.NET because Null6 isn’t a constant. In such cases you should revise your VB6 code to get rid of the Const statement or use a constant value for Null, for example Nothing or "" (the empty string).

Finally, even if most developers don’t realize it, VB6 Variant parameters can contain a special “missing” value. You can’t directly assign such a value, because it is assigned by the VB6 compiler when the caller omits an optional Variant parameter that has no default value, as in this example:

        Sub Main()
            Test 1
        End Sub

        Sub Test(Optional value As Variant, Optional value2 As Variant)
            ' missing Variant arguments can be tested with IsMissing
            Debug.Print IsMissing(value)      ' displays False
            Debug.Print IsMissing(value2)     ' displays True
        End Sub

This is VB Migration Partner converts the above code:

        Sub Main()
            Test(1)
        End Sub

        Sub Test(Optional ByRef value As Variant = MissingValue6, _
                 Optional ByRef value2 As Variant = MissingValue6)
            FixOptionalParameter6(value, value2)
            ' missing Variant arguments can be tested with IsMissing
            Debug.WriteLine(IsMissing6(value))      ' displays False
            Debug.WriteLine(IsMissing6(value2))     ' displays True
        End Sub

After being processed by the FixOptionalParameter6 method, a “missing” parameter is assigned the special value System.Reflection.Missing.Value, which is recognized by the COM Interop infrastructure and works well when passing the parameter to a COM object.

You can redefine this value by assigning the special Missing6 field, for example by means of an InsertStatement pragma

        Sub Main()
            '## InsertStatement Missing6 = Nothing
        End Sub

 

Previous | Index | Next