General Issues with Data Access Code

General Issues with Data Access Code

This section looks at some of the changes that are made to your data access when it is upgraded from Visual Basic 6 to Visual Basic .NET. It also discusses solutions to problems you may encounter when upgrading data access code.

DAO and RDO Module Methods

Both DAO and RDO expose module methods. As we discussed in Chapter 13, module methods are properties and functions of the COM library itself that are made globally available. They can be used without creating an instance of any class. For example, the following DAO code opens a database using the OpenDatabase module method:

Dim db As Database Set db = OpenDatabase("c:\temp\NWind.mdb")

When Visual Basic 6 executes this code, it recognizes that OpenDatabase is a method of the DAO COM library rather than of a particular class within the library. Visual Basic .NET does not support module methods; every method must be qualified with a class.

Because module methods are actually methods of the COM library s underlying AppID class, the Upgrade Wizard locates the AppID class within the COM component s type library. It then creates a global variable of this class and places it in a module called UpgradeSupport.vb. If this module doesn t already exist, the wizard creates it for you. The name of the variable it creates is DAODBEngine_definst for DAO, RDOrdoEngine_definst for RDO. When the previous example is upgraded, the wizard creates an UpgradeSupport.vb module with the following code:

Module UpgradeSupport    Friend DAODBEngine_definst As New DAO.DBEngine End Module

The code that uses the OpenDatabase method is upgraded to be a method of this default instance:

Dim db As DAO.Database db = DAODBEngine_definst.OpenDatabase("c:\temp\NWind.mdb")

Default instances are a good solution for module methods. If you re continuing to write DAO and RDO code, you should be aware of this difference and use the global variable to access the module methods. For example, you could add the following code to the upgraded project that uses the DAO BeginTrans and CommitTrans methods:

DAODBEngine_definst.BeginTrans() DAODBEngine_definst.CommitTrans()

Similarly, you can add the following DAO methods and properties:

BeginTrans	CommitTrans	CompactDatabase
CreateDatabase	CreateWorkspace	Idle
OpenConnection	Properties	RegisterDatabase
RepairDatabase	Rollback	SetOption
SystemDB	Version	Workspaces

You can also add the following RDO methods and properties:

rdoCreateEnvironment

rdoRegisterDataSource

If you are writing a new Visual Basic .NET project that uses module methods, you will need to add a global default instance variable to your application to access these methods.

ADO Version 2.7

ADO comes in a number of versions: 2.1, 2.2, 2.5, 2.6, and now version 2.7. You can use any of these versions in your application. The different versions are merely different type libraries that point to the same DLL: Msado15.dll. Each time you install a new version of Microsoft Data Access Components (MDAC), it updates the underlying Msado15.dll and installs a new type library for the latest version. ADO 2.7 is installed with Visual Basic .NET. When you upgrade your project, the Upgrade Wizard automatically changes any references to earlier versions of ADO to ADO 2.7. These changes don t cause any problems with upgraded code, since ADO 2.7 is backward compatible with previous versions.

Errors in Events

Most data access applications use events in one way or another. For example, if your project uses the ADO Data control, you may have an event handler for the ADODC_MoveComplete event. Another common usage is to declare an ADO.Connection variable using the WithEvents keyword and to define one or more event handlers for the variable. In Visual Basic .NET, if a COM object raises an event, and an untrapped error occurs in the event, the error will be silently ignored. Let s look at an example the Visual Basic 6 project prjADOEvent (included on the companion CD). This project has a TextBox bound to an ADO Data control that accesses the Employees table in the Northwind database. When you run this project from the Visual Basic 6 IDE and click the Raise Error button, an error occurs in the ADODC1_MoveComplete event, as shown in Figure 14-2.

Figure 14-2

Error occurring in the Visual Basic 6 project prjADOEvent.

Try upgrading the application to Visual Basic .NET. Now when you click the Raise Error button, no error occurs. Did Visual Basic .NET somehow fix the error? Unfortunately not. It is simply ignoring the error, as it does all errors occurring in events raised by COM objects. The solution is to put error trapping code in the event. In this example, the following Visual Basic 6 code for the MoveComplete event:

Private Sub Adodc1_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, _    ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, _    ByVal pRecordset As ADODB.Recordset)    Dim x As Integer, y As Integer If m_RaiseError Then    x = x / y End If End Sub

is upgraded to

Private Sub Adodc1_MoveComplete(ByVal adReason As _    ADODB.EventReasonEnum, _    ByVal pError As ADODB.Error, ByRef adStatus As _    ADODB.EventStatusEnum, _    ByVal pRecordset As ADODB.Recordset) Handles Adodc1.MoveComplete    Dim x, y As Short    If m_RaiseError Then       x = x / y    End If End Sub

Let s change this event to include Try...Catch error trapping:

Private Sub Adodc1_MoveComplete(ByVal adReason As _    ADODB.EventReasonEnum, _ ByVal pError As ADODB.Error, ByRef adStatus As _    ADODB.EventStatusEnum, _ ByVal pRecordset As ADODB.Recordset) Handles Adodc1.MoveComplete    Try        Dim x, y As Short        If m_RaiseError Then            x = x / y        End If    Catch ex As Exception        MsgBox(ex.Message)    End Try End Sub

Now Visual Basic .NET catches and displays the error, as shown in Figure 14-3. You should add error trapping code to all DAO, RDO, and ADO events that may raise errors.

Figure 14-3

Error caught and displayed by Visual Basic .NET.

RDO Connection

A common technique when programming with RDO in Visual Basic 6 is to let the user configure the database connection at run time, rather than requiring it to be set up at design time. To do this, you write code that opens an rdoConnection with a blank Connect argument, as in the following code:

Dim cn As rdoConnection Dim en As rdoEnvironment Set en = rdoEnvironments(0) Set cn = en.OpenConnection("")

When this code runs, it brings up the Select Data Source dialog box, as shown in Figure 14-4.

Figure 14-4

Select Data Source dialog box.

The code upgrades to the following:

Dim cn As RDO.rdoConnection Dim en As RDO.rdoEnvironment en = RDOrdoEngine_definst.rdoEnvironments(0) cn = en.OpenConnection("")

Whether this upgraded code works or not in Visual Basic .NET depends on where it is located. If it is located in a Button_Click event, it will work. However, if it is in Form_Load, it will fail with the error shown in Figure 14-5.

Figure 14-5

RDO connection error in Visual Basic .NET.

The reason that it fails in Form_Load is that the dialog box tries to retrieve a handle for the parent window. In Visual Basic 6 that handle exists, but in Visual Basic .NET it hasn t been created yet. The solution is to move the code from Form_Load to another event, such as a Button_Click event.

Null, vbNullString, and vbNullChar

As we discussed in Chapter 11, the Null value works differently in Visual Basic .NET than it does in Visual Basic 6. The use of Null can sometimes lead to subtle, hard-to-find errors. You may find one such error with vbNullString. It is a common practice in Visual Basic 6 to use vbNullString to reset a Recordset filter, as in the following code:

Dim rs As New ADODB.Recordset rs.Filter = vbNullString

This code sets the Recordset filter to an empty string. After upgrading, it becomes the following:

Dim rs As New ADODB.Recordset() rs.Filter = vbNullString

When run, this code causes a COM exception in Visual Basic .NET because vbNullString has the value Nothing in Visual Basic .NET, whereas in Visual Basic 6 it has the empty string value "". The Recordset filter is expecting a string value rather than a Null, and hence the error. The solution is to change the code to use an empty string:

Dim rs As New ADODB.Recordset() rs.Filter = ""

This code works perfectly.