Converting a Data-Oriented Application from Visual Basic 6 to Visual Basic 2005, Part 1

 

Ken Getz

August 2005

Summary: Ken Getz shows you how to migrate your data-oriented application from Visual Basic 6.0 to Visual Basic 2005. (23 printed pages)

Contents

Introduction
Convert or Rewrite?
Conversion Tools
Getting Started
Investigating the Existing Solution
Preparing to Convert the Application
Investigating the Issues
Converting the Application
Conclusion

Introduction

Most Visual Basic 6 applications involve data in some way or another, and most Visual Basic 6 developers are comfortable working with ADO in order to solve their data-handling tasks. It's likely that if you're currently an expert Visual Basic 6 developer, you're interested in moving your skills to the world of .NET, using Visual Basic 2005 as your development language. This skill transfer isn't nearly as simple as you might like, and, to be honest, you've got real work to do.

Need to migrate to .NET? The aim of this three-article series is to make this transition a little less stressful. By walking you through the conversion and modifications involved in moving a simple Visual Basic 6 data-oriented application into Visual Basic 2005, you'll get started on the tasks of moving your own applications, and your skills, to Visual Studio 2005 and Visual Basic 2005.

Who is the target audience for this series? If you're an intermediate to experienced Visual Basic 6 developer interested in moving to .NET but haven't yet spent much time with Visual Studio 2003 or Visual Studio 2005, you stand to gain the most from following the steps in these articles. If you're an experienced .NET developer, it's likely that there are other resources that will be more useful for you. On the other hand, if you're new to Visual Basic .NET, ADO.NET, and .NET in general, you're likely to find a number of useful techniques here.

This series will walk you through converting an existing Visual Basic 6 application that was originally written as part of a Visual Basic 6 course for AppDev, and the application is used here with AppDev's explicit agreement. In this first article, you'll investigate the application, see how it works, and walk through the steps of converting it so that it executes within Visual Studio 2005. In the second article, you'll convert the application so that it uses ADO.NET, the preferred data access mechanism in the .NET environment. Finally, in the third article, you'll recreate the application using Visual Studio 2005's data-binding capabilities, writing as little code as possible—that is, you'll emulate the steps you might go through if you were writing a similar application "from scratch" using Visual Studio 2005.

If you follow through all three articles, you should be able to start converting your existing applications from Visual Basic 6 to Visual Basic 2005. You'll understand the basics of ADO.NET, and you'll have interacted with the new data-binding scenarios supported in the latest version of Visual Studio. The goal of these articles is for you to do the work yourself—that is, you'll start with the prepared Visual Basic 6 application, and you'll walk through the steps of performing the conversion, or creating the new application, yourself.

Of course, it's important to mention that the sample application shown here is impossibly simple. It uses many ADO features, and shows off many useful steps in the conversion and rewriting process, but any real Visual Basic 6/ADO project will necessarily be more complex. In addition, in order to keep the application simple and understandable, it may include some development styles and features that are not accepted best practices—that's not the goal here. Although the application attempts to use the best stylistic practices that it can, the primary goal when writing this application was to keep things simple.

Convert or Rewrite?

If you search on the Web, you'll find many articles, blog entries, and discussions on newsgroups about whether it's worth converting existing Visual Basic 6 applications to .NET. This article can't even begin to offer the one right answer to the question of whether to convert or rewrite, but in general, Visual Basic 6 to .NET conversion works in some cases, and not others. There's no simple solution, and no one correct answer. MSDN contains several articles proclaiming the benefits of converting from Visual Basic 6 to Visual Basic .NET, for example), but you won't easily find a document that tells you, in simple terms, when to upgrade and when to rewrite existing applications. This article walks you through the process of converting a three-tier application, and with minor modifications the application works perfectly well within Visual Studio 2005. Other applications won't fare so well. One popular alternative is to migrate portions of the applications and use COM interoperability in order to communicate between the Visual Basic 6 and .NET portions of the application. In any case, this article will not take a stand on this issue, and will simply convert an existing application and then demonstrate rewriting it. For more information on migrating existing skills and Visual Basic 6 applications, visit the Visual Basic Developer Center or VBRun: The Visual Basic 6.0 Resource Center.

Conversion Tools

In order to make the conversion process as simple as possible, Microsoft provides two tools that you'll use in following the steps in this article. The first tool, the Visual Basic 6.0 Code Advisor, requires you to download an add-in for Visual Basic 6. This tool, although written for Visual Basic 2003, works fine for Visual Basic 2005. It examines your existing Visual Basic 6 application and provides suggestions that can help make the migration process go smoother. You'll use this tool before upgrading the sample application. In addition, Visual Studio 2005 includes a wizard that attempts to convert existing Visual Basic 6 applications into Visual Basic 2005—you'll run this wizard as you load the project into Visual Studio 2005.

Getting Started

Before you begin the process of walking through the steps in this article, take the time now to download the Visual Basic 6.0 Code Advisor, and install it on your test machine. In addition, you'll need to procure and install Visual Studio 2005. Of course, you'll also need to have a copy of Visual Basic 6.0 with SP6 installed on your test computer. The sample data comes from an Access .MDB file, although nothing in the sample application or its conversion process would be affected if the data were stored in SQL Server, except for the necessary change to the connection string.

Investigating the Existing Solution

The sample you'll work with as part of this demonstration conversion process tracks simple information about some customers with familiar names, as shown in Figure 1. The application uses simple three-tier architecture, although all the tiers exist within the same project for simplicity. Figure 2 shows the single sample form, along with the Project window, from the demonstration project.

Figure 1. The sample application tracks contact information about these customers.

Figure 2. The sample form and the Project window from the sample project show the important parts of the project.

Running the sample application displays the single form, allowing you to perform standard database operations on the sample data (insert, update, delete, and select rows), using the middle-tier DataLayer class to provider access to the data, and the CustomerHandler class to manipulate customers. Figure 3 shows the sample form in action. (Note that the existing connection string expects to find the database VB6Demo.mdb in the same folder as the executable program.)

Figure 3. Use the sample form to navigate through, insert new rows, and delete or modify existing rows.

The sample project includes the DataLayer class, which acts as a generic data tier—this class contains the code necessary to open connections and provide ADO RecordSet instances. The business layer within the application can call the GetRecordset method to retrieve an ADO Recordset object, and the CloseRecordset method to release the Recordset and its connection. The class contains the following code (as in all code examples shown here, unless the error handling code is pertinent to the discussion, it has been removed from the article to keep things as simple as possible):

' From DataLayer.vb
Private Function OpenConnection( _
 ConnectionString As String) As ADODB.Connection
  ' Open the connection.
    
  Dim cnn As ADODB.Connection
   
  Set cnn = New ADODB.Connection
  cnn.ConnectionString = ConnectionString
  cnn.Open
  Set OpenConnection = cnn
End Function

Public Function GetRecordset( _
 ConnectionString As String, SQL As String) As ADODB.Recordset
    
  Dim cnn As ADODB.Connection
  Dim rst As ADODB.Recordset
    
  Set cnn = OpenConnection(ConnectionString)
  If Not cnn Is Nothing Then
    If cnn.State = adStateOpen Then
      ' Build updateable keyset.
      Set rst = New ADODB.Recordset
            
      rst.CursorType = adOpenKeyset
      rst.LockType = adLockOptimistic
      rst.Source = SQL
      Set rst.ActiveConnection = cnn
      rst.Open Options:=adCmdText
    End If
  End If
  Set GetRecordset = rst
End Function

Public Sub CloseRecordset(rst As ADODB.Recordset)
  Dim cnn As ADODB.Connection
    
  If Not rst Is Nothing Then
    Set cnn = rst.ActiveConnection
    If Not cnn Is Nothing Then
      If cnn.State = adStateOpen Then
        cnn.Close
      End If
    End If
    If rst.State = adStateOpen Then
      rst.Close
    End If
  End If
  Set rst = Nothing
End Sub

One of the goals of a three-tier application is to construct the user interface such that it needn't communicate directly with the data layer, and this application attempts to emulate that behavior. In this case, the middle-tier (CustomerHandler.vb) contains all the code called by the user interface. In this example, the middle-tier class contains the following code:

' From CustomerHandler.vb
Private Function GetConnectionString() As String
  GetConnectionString = _
   "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=" & App.Path & "\VB6Demo.MDB"
End Function

Public Function GetCustomers() As ADODB.Recordset
  Dim dl As DataLayer
  Set dl = New DataLayer
  Set GetCustomers = dl.GetRecordset( _
   GetConnectionString(), _
   "SELECT * FROM tblCustomer ORDER BY LastName")
  Set dl = Nothing
End Function

Public Function GetStates() As ADODB.Recordset
  Dim dl As DataLayer
  Set dl = New DataLayer
  Set GetStates = dl.GetRecordset( _
   GetConnectionString(), _
   "SELECT State FROM tblStates")
  Set dl = Nothing
End Function

Public Function CloseRecordset(rst As ADODB.Recordset)
  Dim dl As DataLayer
  Set dl = New DataLayer
  dl.CloseRecordset rst
End Function

The CustomerHandler class contains the procedures that return the recordsets containing customer or state information, along with a method to close a recordset. It's debatable whether the procedure that retrieves the connection string should be in this class or in the data layer, but that's a decision you can make for yourself without affecting this demonstration of the upgrade process.

The module named basUtility provides some simple utility procedures:

• HandleUnexpectedError displays an alert with an error number and message:

  Public Sub HandleUnexpectedError( _
  lngError As Long, strDescription As String)
  MsgBox "Error: " & strDescription & _
  " (" & lngError & ")"
  End Sub

• AddQuotes surrounds a string with apostrophes and doubles any apostrophes within the string, making it possible to perform a search on any delimited string:

  Public Function AddQuotes(strValue As String) As String
  ' If a string to be quoted contains
  ' an apostrophe, then simply replace
  ' it with two. VBA will see the two
  ' apostrophes as a single apostrophe,
  ' and all will be well.
  AddQuotes = _
  "'" & Replace(strValue, "'", "''") & "'"
  End Function

• FindString attempts to find a string within a ComboBox or ListBox control:

  Public Sub FindString( _
  ctl As Control, strFind As String)
  ' Find strFind in ctl, which must be either
  ' a list box or a combo box.
  On Error Resume Next
  
  

' Attempt to set the value of the control ' to be the specified text. ctl.Text = strFind If Err.Number <> 0 Then ctl.ListIndex = -1 End If End Sub

• FixBoolean converts a Boolean value from an ADO Recordset so it can be displayed in a CheckBox control:

  Public Function FixBoolean(fld As ADODB.Field) _
  As CheckBoxConstants
  ' Convert a Yes/No/Null field value
  ' into a checkbox value.

If CBool(fld.Value) Then FixBoolean = vbChecked Else FixBoolean = vbUnchecked End If End Function

• The NullToText and TextToNull functions convert empty strings to and from Null values. Visual Basic 6 controls have no way to indicate a null value, yet an empty string is different from a null value in a database. These procedures handle that conversion:

  Public Function NullToText(fld As ADODB.Field) _
  As String
  ' Append an empty string, forcing a conversion
  ' of a Null value to a string, leaving normal
  ' strings as is.
  NullToText = fld.Value & ""
  '
  ' Or, you can use:
  ' If IsNull(fld.Value) Then
  '   NullToText = ""
  ' Else
  '   NullToText = fld.Value
  ' End If
  End Function

Public Function TextToNull(strValue As String) As Variant ' Convert a text string to Null, if ' the string has no characters. ADO appears ' to require this, at least with the Jet ' provider. If Len(strValue) = 0 Then TextToNull = Null Else TextToNull = strValue End If End Function

Finally, the demonstration form, frmADO, contains the code handling the user interface. The form contains these declarations, including class-level variables to track the CustomerHandler instance that manages the data, and another to refer to the Recordset containing the customer data:

' Keep track of the customer handler, and
' the customer recordset:
Private customer As CustomerHandler
Private customerRecordset As ADODB.Recordset

' The name of the field to use for searching.
Private Const conFindField = "LastName"

' Constants indicating the current data state.
Private Enum DataState
  Loading = 0
  Adding = 1
  Editing = 2
  NoRows = 3
  Normal = 4
End Enum
Private currentDataState As DataState

The form's Load event handler retrieves the customer information, initializes the form, and displays the data:

Private Sub Form_Load()
  Set customer = New CustomerHandler
    
  ' Open the connection and the
  ' recordset, and then initialize
  ' the form.
  Set customerRecordset = customer.GetCustomers
  If Not customerRecordset Is Nothing Then
    Call InitForm
    
    ' Display the form.
    Call ShowData
  Else
    MsgBox "Unable to load the requested data."
    Unload Me
  End If
End Sub

The InitForm procedure simply loads the list of states:

Private Sub InitForm()
  ' Put any code here that you want
  ' to run once for your form.
  
  ' Load the combo box with state values.
  Call LoadStates
End Sub

Private Sub LoadStates()
  ' Load cboStates from tblStates.
  Dim states As ADODB.Recordset
  Set states = customer.GetStates()
  
  Do Until states.EOF
    cboState.AddItem states("State")
    states.MoveNext
  Loop
End Sub

The ShowData procedure moves data to the form from the underlying Recordset, keeping track of the current row. Note that this example uses a control array (txtArray) to display all the text data from the Recordset—control arrays aren't directly supported in .NET, so this is an area you'll need to investigate. The HandleButtonState and GotoFirstControl procedures simply handle managing the user interface—you can investigate those directly in the source code:

Private Sub ShowData()
  ' Set this flag so event procedures
  ' don't do anything during this
  ' process.
  currentDataState = DataState.Loading
  
  If customerRecordset.RecordCount = 0 Then
    Call AddNewRow
  Else
    txtData(0).Text = NullToText(customerRecordset("CustomerID"))
    txtData(1).Text = NullToText(customerRecordset("FirstName"))
    txtData(2).Text = NullToText(customerRecordset("LastName"))
    txtData(3).Text = NullToText(customerRecordset("Address"))
    txtData(4).Text = NullToText(customerRecordset("City"))
    txtData(5).Text = NullToText(customerRecordset("ZipCode"))
    
    Call FindString(cboState, _
     NullToText(customerRecordset("State")))
    chkActive.Value = FixBoolean(customerRecordset("Active"))

    Call HandleButtonState
    currentDataState = DataState.Normal
  End If
  Call GotoFirstControl
End Sub

The navigation buttons on the form change the current row within the form's underlying Recordset. For example, clicking the button that moves the current row to the first row of the Recordset executes this code:

Private Sub cmdFirst_Click()
  customerRecordset.MoveFirst
  Call ShowData
End Sub

Clicking the button that adds a new row calls the AddNewRow procedure, which simply clears the form, and sets up the button state appropriately:

Private Sub AddNewRow()
  ' Add a new row.
  
  ' Clear the form, and manage the navigation
  ' buttons. Set the focus to the
  ' correct text box.
  Call ClearForm
  Call HandleButtonState
  Call GotoFirstControl
End Sub

The form treats adding and editing much the same—in either case, the buttons change their enabled state. While in the editing or adding mode, you can click Save or Cancel to perform the requested action. Whether you've added a new row or modified an existing row, clicking the Save button calls the SaveData procedure, which handles additions and modifications, using the class-level currentDataState variable (which is kept current by all the event handlers) to determine which action to take. Note that this code takes advantage of the fact that an uninitialized Variant contains the special value Empty—Visual Basic 2005 doesn't support Variant values, so this mechanism will need to change upon conversion.

Private Function SaveData() As Boolean
  Dim currentBookmark As Variant
  Dim isOK As Boolean
    
  isOK = True
    
  ' Retrieving a bookmark would trigger an
  ' error if there are no rows, so just
  ' disregard the error. If there are no rows,
  ' currentBookmark will just contain Empty.
  On Error Resume Next
  currentBookmark = customerRecordset.Bookmark
    
  On Error GoTo ErrorHandler
  If currentDataState = DataState.Adding Then
    customerRecordset.AddNew
  End If
    
  ' Whether adding or editing,
  ' you need to save fields and update
  ' the recordset.
  Call SaveFields
  customerRecordset.Update
  
  If currentDataState = DataState.Adding Then
    ' Display the newly added key.
    Call GetID
  End If
    
  ' Reset buttons.
  currentDataState = DataState.Normal
  Call HandleButtonState
    
ExitHere:
  If Not isOK Then
    ' If something went wrong, then go back
    ' to the original row.
    If Not IsEmpty(currentBookmark) Then
      customerRecordset.Bookmark = currentBookmark
    End If
  End If
  SaveData = isOK
  Exit Function

ErrorHandler:
  isOK = False
  Select Case Err.Number
    Case Else
      Call HandleUnexpectedError(Err.Number, Err.Description)
  End Select
  Call GotoFirstControl
  Resume ExitHere
End Function

The GetID procedure simply encapsulates the action of retrieving the new CustomerID value:

Private Sub GetID()
  ' Get the newly added customer ID.
  txtData(0).Text = customerRecordset("CustomerID")
End Sub

The SaveFields procedure does the work of saving each individual value back into the appropriate database field:

Private Sub SaveFields()
  ' The ADO/Jet provider will not allow
  ' empty strings in fields that don't
  ' explicitly allow empty strings (as
  ' opposed to Nulls). Convert empty
  ' text boxes back to Null, for storage
  ' in the table.
  
  customerRecordset("FirstName") = TextToNull(txtData(1).Text)
  customerRecordset("LastName") = TextToNull(txtData(2).Text)
  customerRecordset("Address") = TextToNull(txtData(3).Text)
  customerRecordset("City") = TextToNull(txtData(4).Text)
  customerRecordset("ZipCode") = TextToNull(txtData(5).Text)
  
  customerRecordset("State") = TextToNull(cboState.Text)
  customerRecordset("Active") = CBool(chkActive.Value)
End Sub

Clicking Cancel while in the middle of an edit or an addition simply puts the original data back into the form:

Private Sub cmdCancel_Click()
  Call ShowData
End Sub

The sample form contains other procedures, but for the most part, they deal only with the user interface—feel free to examine the sample to follow through all the user interface features. Take some time at this point to try out the sample application in Visual Basic 6, to get familiar with its operation and behavior.

Preparing to Convert the Application

When you convert an application, you need to take into account not only the language changes (if you're completely new to Visual Basic .NET, you might start here to begin investigating the differences between the languages), but also the changes in how controls and other environment features have changed. In order to make it easier for you to manage the transition, you can use the Visual Basic 6.0 Code Advisor to look for places in your application that will likely fail upon conversion.

Your first goal, after investigating the sample application, is to determine what the problems will be upon conversion, and fix as many as you can from within Visual Basic 6.0—that's the goal of the Visual Basic 6.0 Code Advisor. If you haven't installed the tool yet, do that now. Once you have it installed, follow these steps to perform the investigation of the sample project:

1. With the sample project loaded in Visual Basic 6.0, select Add-Ins | Code Advisor, and ensure that the search scope is the Active Project, as shown in Figure 4.

   

   Figure 4. Ensure that you have selected to scan the entire project.

2. From the same menu, select Add FixIts to begin the scanning process.

3. Once the scanning process has completed, you can view the results: select Add-Ins | Code Advisor | View FixIt Report, and you'll see the report shown in Figure 5. This report contains a list of all the issues found during the scan, and also includes a list of all the rules that the Code Advisor used as it made its scan.

   

   Figure 5. The FixIt report shows all the issues you'll need to deal with before attempting the conversion to Visual Basic 2005.

Now it's time to examine each of these issues in detail. Of course, a different application would have a different set of issues, and to be honest, this application was written very cleanly in the first place—as an example for a training course, it had very little "fluff" to begin with. Most likely, running the code advisor on your own applications will result in a much longer list of grievances.

Investigating the Issues

Before attempting to load the application in Visual Studio 2005, you'll need to stop and investigate each of the issues listed in the Issues report. As you'll see, several of the issues listed in the report, at least for this particular application, are vestigial, and require no action on your part. The following paragraphs describe solutions to each of the issues raised in the report.

The first issue, "txtData.LinkTimeout has no Visual Basic .NET equivalent and will not be upgraded", doesn't really apply to this application. You can skip this issue with no ill effects. If you want to generate a report with no issues, however, you'll need to set the LinkTimeOut property for the Street text box (txtData(3)) to 50 (instead of its current value, 35). It's unclear how this value ever got set to a non-default value, but whether you fix it or not, this change won't affect the conversion.

To fix the four issues, "cmdXXX.UseMaskColor has no Visual Basic .NET equivalent and will not be upgraded", you can set the UseMaskColor of the four buttons (cmdFirst, cmdPrevious, cmdNext, cmdLast) to False. Unfortunately, this change affects the appearance of the bitmaps on the buttons—you'll need to resolve this behavior once you perform the conversion.

To solve the next two issues, "'Value' is not a property of the generic 'Control' object in Visual Basic .NET. To access 'Value' declare 'ctl' using its actual type instead of 'Control'" (and the same issue, except using the ListIndex property) actually requires some care. The ClearForm procedure contains code that uses a generic Control-type variable to loop through all the controls on the form. For TextBox, CheckBox, and ComboBox controls, the code takes special steps:

Dim ctl As Control

For Each ctl In Me.Controls
  If TypeOf ctl Is TextBox Then
    ctl.Text = ""
  ElseIf TypeOf ctl Is CheckBox Then
    ctl.Value = vbChecked
  ElseIf TypeOf ctl Is ComboBox Then
    ctl.ListIndex = -1
  ' TODO:
  ' Add more control types, if necessary.
  End If
Next ctl

The problem here is that although Visual Basic 6.0 allows late binding, Visual Basic 2005 does not. That is, although the Control type provides a Text property, specific classes that inherit from Control (ListBox, TextBox, and so on) provide the various other properties, such as Value and ListIndex. Visual Basic 6.0 allows you to write code that references a class' property using a generic type, but this is more difficult to do (and isn't the default behavior) in Visual Basic 2005. To solve the problem, modify the For Each block so that it looks like this:

For Each ctl In Me.Controls
    If TypeOf ctl Is TextBox Then
        ctl.Text = ""
    ElseIf TypeOf ctl Is CheckBox Then
        Dim chk As CheckBox
        Set chk = ctl
        chk.Value = vbChecked
    ElseIf TypeOf ctl Is ComboBox Then
        Dim cbo As ComboBox
        Set cbo = ctl
        cbo.ListIndex = -1
    ' TODO:
    ' Add more control types, if necessary.
    End If
Next ctl

Although it's more effort this way, the code no longer uses late binding, and the conversion to Visual Basic 2005 will go more smoothly.

Solving the next two problems ("Declare 'currentBookmark' with an early-bound data type") is easy. At this point, the code uses what was once common wisdom—using a Variant to contain an ADO bookmark value. The wisdom may have been apocryphal, and the reasoning is certainly forgotten (at least by this author). In any case, the correct storage for a bookmark is in a Double value, so the solution is simple: replace both declarations for the currentBookmark variable:

' In frmADO.SaveData, replace this:
Dim currentBookmark As Variant
'with this:
Dim currentBookmark As Double

' In frmADO.FindRow, replace this:
Dim currentBookmark As Variant
'with this:
Dim currentBookmark As Double

The next issue, "'ListIndex' is not a property of the generic 'Control' object in Visual Basic .NET. To access 'ListIndex', declare 'ctl' using its actual type instead of 'Control'" is the same issue you've seen already. This time, the basUtility.FindString procedure uses a generic Control type because the intent is that it should work with either a ListBox or a ComboBox control. It's up to the caller to supply the correct type of control, and Visual Basic 6.0's late binding takes care of the details:

Public Sub FindString( _
 ctl As Control, strFind As String)
  ' Find strFind in ctl, which must be either
  ' a list box or a combo box.
  On Error Resume Next
  
  ' Attempt to set the value of the control
  ' to be the specified text.
  ctl.Text = strFind
  If Err.Number <> 0 Then
    ctl.ListIndex = -1
  End If
End Sub

To fix the problem, modify the procedure so that it looks like this, using strongly typed controls rather than the generic one:

Public Sub FindString( _
 ctl As Control, strFind As String)
  ' Find strFind in ctl, which must be either
  ' a list box or a combo box.
  On Error Resume Next
  
  ' Attempt to set the value of the control
  ' to be the specified text.
  ctl.Text = strFind
  If Err.Number <> 0 Then
    If TypeOf ctl Is ListBox Then
      Dim lst As ListBox
      Set lst = ctl
      lst.ListIndex = -1
    ElseIf TypeOf ctl Is ComboBox Then
      Dim cbo As ComboBox
      Set cbo = ctl
      cbo.ListIndex = -1
    End If
  End If
End Sub

The next error, "Declare 'TextToNull' with an early-bound data type", really can't be fixed. If you investigate the basUtility.TextToNull procedure, you'll see that the code needs to return either a Null value or a string. In Visual Basic 6.0, the only way to do this is to return a Variant value. So, at this point, your only option is to disregard the warning, and leave the code as-is:

Public Function TextToNull(strValue As String) As Variant
  ' Convert a text string to Null, if
  ' the string has no characters. ADO appears
  ' to require this, at least with the Jet
  ' provider.
  If Len(strValue) = 0 Then
    TextToNull = Null
  Else
    TextToNull = strValue
  End If
End Function

The final issue, "Declare 'CloseRecordset' with an early-bound data type", is easy to fix—it's simply an oversight in the original code. The CustomerHandler.CloseRecordset method doesn't return a specific type (which means it's returning a Variant). But the procedure isn't really returning a value at all, so it can be converted to a Sub. To fix the problem, modify the CustomerHandler.CloseRecordset procedure so that it looks like this:

Public Sub CloseRecordset(rst As ADODB.Recordset) As 
    Dim dl As DataLayer
    Set dl = New DataLayer
    dl.CloseRecordset rst
End Sub

When you're done making all the changes, select Add-Ins | Code Advisor | Add FixIts again, and this time, the report should show just the single unchanged item. (Before you leave the advisor's report this time, take a moment to investigate the rules listed at the bottom of the report—this gives you some idea of the kinds of issues you might be facing when converting other applications.)

Finally, run the application one last time and verify that the only behavioral change is that the navigation buttons don't look as nice as they did when Visual Basic 6.0 hid their background colors. Otherwise, the application continues to work just the same as it did, and it's ready to be converted.

Converting the Application

The time has come—you've done the preparation, and the sample application is ready to be converted to Visual Basic 2005. Follow these steps to perform the conversion:

1. From the Windows Start menu, open Visual Studio 2005.
2. In Visual Studio 2005, select File | Open | Project/Solution, and navigate to the location where you stored your Visual Basic 6.0 project. Select ADOForm.vbp, and click Open.
3. On the Welcome to the Visual Basic Upgrade Wizard page, click OK to continue.
4. On the Choose a Project Type page, only the EXE project type is available, so click Next to continue.
5. On the Specify a Location for Your New Project page, enter the path to the location where you'd like your new project, or accept the default. Either way, your original project isn't changed. Click Next to continue.
6. On the Ready to Upgrade page, click Next, allowing the upgrade Wizard to do its job. Get a cup of coffee while it works (that is, don't expect an immediate conversion).
7. Once Visual Studio has completed its conversion, it loads the newly converted project. Be brave: Press F5 to run the project, and you'll find that it fails. It doesn't fail because of a conversion issue, but instead because the code expects to find the database, VB6Demo.mdb, in the same folder as the executable. To solve this problem, copy the same database into the bin folder beneath the project folder you just created. Try running the application again and you'll see that it works just as it did within Visual Basic 6.0.

If you investigate the upgrade report (in the Solution Explorer window, double-click _UpgradeReport.htm), you'll find that most of the modules upgraded without errors or warnings. The user interface form, ADO.vb, contains a number of errors, mostly dealing with layout and property issues. The issue that appears the most, however, is labeled "Couldn't resolve default property of object TextToNull()". If you investigate the code, you'll find that this function, which used to return a Variant, now returns an Object type. Visual Basic 2005 isn't able to determine which property of the returned Object you intend to use when you call TextToNull, so the compiler issues this warning. At this point, however, the code appears to work—you've satisfied the original goal of this article. (You should, for learning purposes, investigate the converted code to see what Visual Studio 2005 did as it loaded your existing project. Some of the changes are obvious, like converting Variant to Object types, but others make clever use of Visual Basic 2005 to emulate Visual Basic 6 behavior in the .NET environment.)

The most important question is "How does the new application communicate with the existing database, without rewriting any of the ADO code?" The answer is simple: COM interoperability makes it possible for a .NET application to continue using existing COM infrastructure as you convert your applications. You were using COM interop between Visual Basic 6 and ADO originally, and your application still works the same way. To confirm the COM reference, in the Solution Explorer window, right-click the ADOForm project (not the top node, the ADOForm solution). From the context menu, select Properties, and in the Properties page, select the References tab. You'll find the Microsoft ActiveX Data Objects reference there, and the code uses this reference to provide ADO support.

If I may inject my own opinions here, I'd like to suggest that although you can get a converted Visual Basic 6 application to work in Visual Studio 2005, as you've done here, you should consider making future changes, and additions to the applications, using newly created objects and code written specifically in and for Visual Studio 2005. That is, I prefer to isolate upgraded forms and code, and over time, replace them with newly created objects. In the second installment of this series, you'll recreate the sample form, this time using Windows Forms controls and tools, and using ADO.NET to communicate with the data source. In the third installment, you'll revisit the new form, this time using the built-in data-binding tools provided by Visual Studio 2005.

Conclusion

This simple application illustrates only a few of the issues involved in upgrading, and this article discusses only a few of the specific upgrade topics. You'll want to investigate each comment in the converted source, looking for UPGRADE_WARNING in the text of the comment. You might want to focus your study on some particular issues, including:

• What happens to objects when you set them to 'Nothing'? Visual Basic .NET handles this differently than does Visual Basic 6. Watch for this upgrade warning: "Object <xx> may not be destroyed until it is garbage collected."
• TypeOf has slightly different behavior in Visual Basic 2005. Following the upgrade warning link to read about this change, but it doesn't affect this particular application.
• The value Null has changed its meaning. Watch for the "Use of Null/IsNull() detected" warning and follow the warning link to learn more about this issue.

The next installment of this article series will demonstrate how to create the same sample application, using ADO.NET and Windows Forms features directly.

About the Author

Ken Getz is a senior consultant with MCW Technologies. He is coauthor of ASP .NET Developers Jumpstart (Addison-Wesley, 2002), Access Developer's Handbook (Sybex, 2001), and VBA Developer's Handbook, 2nd Edition (Sybex, 2001).

© Microsoft Corporation. All rights reserved.