Previous | Index | Next 

[PRB] Assigning the ActiveConnection and Source properties of an ADODB.Recordset object may cause a runtime error

The ActiveConnection property of the ADODB.Recordset object has a dual nature: it can be assigned either a Connection object or a connection string. Interestingly, if you assign a string to the ActiveConnection property and read the property back, it doesn’t return the string just assigned; instead, it returns the Connection object that ADO has created for you.

The corresponding .NET object – that is, the .NET wrapper around the ADODB.Recordset object in the adodb assembly – reflects this dual nature by exposing the ActiveConnection property (which can be assigned and returns a Connection object) and the let_ActiveConnection method (that you can use to assign a connection string).

Likewise, the Source property can be assigned either a reference to another Recordset or a string containing a valid SELECT SQL query. As it happens for the ActiveConnection property, the .NET wrapper of the ADODB.Recordset object exposes both the Source property (to be used to assign an object) and the let_Source method (that you can use to assign a string containing an SQL query).

Now, consider the following VB6 code:

    Sub Test(ByVal rs As Recordset, ByVal conn As Connection, ByVal sql As Variant)
        rs.ActiveConnection = conn
        rs.Source = sql
    End Sub

This is how the code is converted to VB.NET. Notice that VB Migration Partner mistakenly appends the name of the default member of the Connection object:

    Sub Test(ByVal rs As ADODB.Recordset, ByVal conn As Connection, ByVal sql As Object)
        rs.ActiveConnection = conn.ConnectionString
        rs.Source = sql
    End Sub

The code compiles nicely, but it throws two distinct runtime errors, because the type of the values being assigned isn’t compatible with the type of the target property. If you are are assigning a Connection object to the ActiveConnection you can generate the correct code by using a Set keyword in the assignment, as in:

    Set rs.ActiveConnection = conn

which prevents VB Migration Partner from appending the name of the default ConnectionString property. In some cases, however, you can’t use this approach because you might not know the type of the value being assigned, as in this example:

    Sub Test(ByVal rs As Recordset, ByVal conn As Variant, ByVal sql As Variant)
        rs.ActiveConnection = conn
        rs.Source = sql
    End Sub

To solve the issues with these properties you can use the SetRecordsetActiveConnection6 and SetRecordsetSource6 helper methods defined in the VBMigrationPartner_Support module. In the above example, you might rewrite the VB6 code as follows:

    Sub Test(ByVal rs As Recordset, ByVal conn As Variant, ByVal sql As Variant)
        SetRecordsetActiveConnection6 rs, conn
        SetRecordsetSource6 rs, sql
    End Sub

This code is converted virtually identical to VB.NET, except for minor type and syntax adjustments. The .NET versions of the SetRecordsetActiveConnection6 and SetRecordsetSource6 helper methods (defined in the control support library) detect the type of value being assigned and behave consequently.

 

Previous | Index | Next