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

Article
02/03/2012

Ken Getz

Paul D. Sheriff

January 2006

Summary: Ken Getz and Paul Sheriff show you how to migrate your data-oriented application from Visual Basic 6.0 to Visual Basic 2005 in Part 3 of this series. (19 printed pages)

Download the associated code sample.

Introduction
Converting From Microsoft Access to SQL Server 2005 Express Edition
Set Up Primary Keys and Relationships
Create the Form
Adding a Cancel Button
Adding a Find Button
Handling Form State
About the Author

Introduction

In the first two articles of this three-article series, you learned how to convert an existing Visual Basic 6 data-oriented application into a Visual Basic 2005 application—Part 1 walked through the conversion process, and Part 2 demonstrated how to replace ADO code with the corresponding ADO.NET code. Although the converted application ran, it was far from perfect, and still required almost as much as code as the converted Visual Basic 6 application. It doesn't have to be that hard, however. In Part 3, you'll learn to use the new Windows Forms data binding that's available in Visual Studio 2005. The goal is to create a data input and display form that has the same functionality as the form shown in the previous two articles, but this time, to use as little code as possible. The final result should be a form that looks like Figure 1, providing the same functionality, in slightly modified format, as in the Visual Basic 6 application we started with back in Part 1.

ms364044.adotonet3_01(en-US,VS.80).gif

Figure 1. The finished form

You can review Part 1 and Part 2 and their associated downloads. This article starts with the data file the previous two articles used, but creates a new Visual Basic 2005 application from scratch. To download the VB6Demo.mdb database we'll use as our starting point, go to the link at the top of this article.

Converting From Microsoft Access to SQL Server 2005 Express Edition

Although we were divided on how to start the application, we finally decided that most developers most likely will want to use SQL Server (or SQL Server 2005 Express Edition, the free version of SQL Server that's available with Visual Studio 2005 and as a download to host their data. Although Access and its MDB files have their place in application development, for most Visual Basic 2005 applications, you'll want the power and security provided by SQL Server (and SQL Express). Although everything we'll do in this article works with SQL Server 2000 and SQL Server 2005, we opted to use SQL Server Express.

There are many ways to convert data in an Access table to SQL Server, but we came across a simple solution—use Access to do the work! To get started converting the database from Access to SQL Server Express, follow these steps:

Within Visual Studio 2005, select View | Server Explorer.
Within the Server Explorer window, right-click the Data Connections node.
From the context menu, select Add Connection.
In the Add Connection dialog box, fill in values as shown in Figure 1.

Figure 2. Add a new connection to a new database.
Click OK. When prompted to create the VB6Demo database, click Yes.

At this point, you have a new SQL Server 2005 database ready to be filled with tables. Because you already have an Access database with your data, you can export the tables and the data from Access directly into SQL Server. You can take advantage of Access projects, which provide an excellent front end to SQL Server databases. (When you create an Access project, you're simply setting up a link to the existing SQL Server database.)

Follow these steps to export the data from Access into the new SQL Server database:

Open Microsoft Access.
Select File | New, and then from the Task Pane within Access, select Project using existing data....
When prompted, select a location on your hard drive and set the name to Vb6DemoTemp.
In the Data Link Properties dialog box, enter information as shown in Figure 2.

Figure 3. Enter information so that Access can link to the new, empty database.

Now that you have Access pointing to your VB6Demo database in SQL Express, you can export all the tables and data from your old VB6Demo.mdb file into SQL Express. Follow these steps to move the data:

In Microsoft Access, right-click the new Access project, and select Import from the context menu.
Browse to the location of the VB6Demo.mdb sample database, and click Import.
In the Import Objects dialog box, select both tblCustomers and tblStates, as shown in Figure 3. Click OK.

Figure 4. Select tables to import.

If you investigate the tables within the Access project, you'll see that you've successfully imported the data from the sample MDB file into the VB6Demo SQL Server database. Of course, you could have accomplished the same goal in many different ways (SQL Server provides mechanisms for importing Access data, for example), but this solution works fine for simple applications like this one.

Close Microsoft Access when you're done.

Set Up Primary Keys and Relationships

The original database included relationships between the tables; you'll need to set those up next. Follow these steps to complete the conversion process:

In Visual Studio 2005, in the Server Explorer window, expand the nodes within the VB6Demo database until you find the Tables node. If you don't see the two new tables within the node, right-click Tables, and select Refresh from the context menu.
In the Server Explorer window, right-click tblStates, and select Open Table Definition from the context menu.
Right-click the State column, and select Set Primary Key from the context menu.
Select File | Save tblStates, and then close the window.
In the Server Explorer window, right-click tblCustomer, and select Open Table Definition from the context menu.
Right-click the CustomerID column, and select Set Primary Key from the context menu.
Select File | Save tblCustomer.
Right-click any field within tblCustomer, and select Relationships from the context menu.
Click Add to add a new relationship between tblCustomer and tblStates. You'll still need to supply column information, in the next few steps.
Click the ellipsis next to the Tables and Columns Specification property, as shown in Figure 4.

Figure 5. Create the relationship.
Set values as shown in Figure 5—you'll need to modify the Primary key table value, as well as the two key fields. Click OK and then Close when you're done.

Figure 6. Modify settings to configure the relationship.
Select File | Save All, and click Yes when prompted to save changes to the tables.

Create the Form

Now that you have the tables set up in the SQL Server 2005 Express Edition database, you are ready to create a new project and recreate the same form that you created in the previous two articles. However, this time you will use the new data binding features of Visual Basic 2005, and use a lot less code in the process!

To get started, follow these steps:

In Visual Studio 2005, select File | New | Project, and create a new Windows application named DataBinding, in any folder you like. Click OK to create the project.
In the designer window, stretch Form1 to make it both wider and taller. You will need some room to drop all the controls for the tblCustomer table on this form.

In order to display bound data on a Windows form, you'll need to create a new project-wide data source. Follow these steps to create the data source:

Select Data | Show Data Sources to display the list of available data sources. There won't be any, right now.
In the Data Sources window, click Add New Data Source.
In the Data Source Configuration wizard, start by selecting a data source. Although you could also select a Web service or a middle-tier object, for this example, you'll choose the existing VB6Demo database. Click Database and then click Next.
In the Choose Your Data Connection page, select the connection to the VB6Demo database that you created earlier. Click Next.
When prompted to save your connection string, on the Save the Connection String to the Application Configuration File page, accept the default connection string name. Doing this stores the connection string in the application's configuration file. That way, other forms within the project can use the same connection information. Click Next.
On the Choose Your Database Objects page, select the objects you'll be using. From the Tables node, check tblCustomer and tblStates, as shown in Figure 6. Click Finish.

Figure 7. Select the database objects for your project data source.

If you expand all the nodes in the Data Sources window, you'll see tblCustomer and tblStates (Figure 8). In addition, the Data Sources window includes a second link to tblCustomer, under tblStates, because the two tables are related.

ms364044.adotonet3_08(en-US,VS.80).gif

Figure 8. The Data Sources window shows tables, and their relationships.

Within the Data Sources window, you can select the default control that Visual Studio will use when you drag an item onto a form. By default, Visual Studio will place a DataGridView control onto a form when you drag a table, and a TextBox or CheckBox control for each field. For the purposes of this example, you don't want a DataGridView control for tblCustomer; instead, you want details.

Follow these steps to begin laying out the form:

In the Data Sources window, select tblCustomer.
Click the down arrow next to the table name, and select Details from the list of options (see Figure 8).

Figure 9. Change the default view for tblCustomer.
In the Data Sources window, select CustomerID, click the field's drop-down arrow, and select Label from the list of control types. (Because CustomerID is an IDENTITY column, the database will automatically create a new value when you add a new customer—you don't want to allow users to edit, and so a Label control is appropriate.)

Figure 10. Set the default control type for the CustomerID column.
Repeat the previous step, setting the default control type for the State column to be a ComboBox.
From the Data Sources window, click and drag the tblCustomer node. Drop the selection onto the form. Visual Studio creates the contents of the form as shown in Figure 10.

Figure 11. Drag-and-drop from the Data Sources window onto a form.

Note what Visual Studio does when you drag a table from the Data Sources window onto a form. At the top of the form, you'll find the visual representation of the BindingNavigator control. This control acts much like a toolbar, except that it's strongly bound to a data source. Investigating this (and the rest of the controls added to your form) is beyond the scope of this article, but you can start by simply investigating the properties for the control shown in the Properties window. This control allows you to navigate through bound data (as its name implies).

Of course, the form contains one control for each bound field, respecting the choices for control types you made within the Data Sources window.

In the form's tray area, you'll find three components in addition to the BindingNavigator: a strongly typed dataset corresponding to the sample tables (VB6DemoDataSet), a BindingSource object (TblCustomerBindingSource), and a TableAdapter object (TblCustomerTableAdapter). (See Figure 11). The DataSet provides the schema for the sample data. The BindingSource object acts as a currency manager, allowing the binding navigator (TblCustomerBindingNavigator) to move through rows, add a row, delete a row, and so on. Although you needn't worry about what these objects are doing, for now, they're crucial in making the data-binding experience work without any effort on your part.

ms364044.adotonet3_12(en-US,VS.80).gif

Figure 12. The form's tray area includes a number of data-related components.

You just need to satisfy one more task, in order to make the form work: you must hook up the binding for the State field's ComboBox control:

From the Data Sources window, click and drag the tblStates node. Drop the selection on top of the States combo box on the form. This gesture automatically binds the drop-down portion of the control to the States table.
With the States combo box selected, in the Properties window, verify that the DisplayMember property is set to State. If it's set to StateName, you'll see the full state name in the list. If it's set to State, you'll see the two-letter abbreviation.
Select File | Save All to save the project, and then press F5 to run the project. You'll see the form shown in Figure 12.

Figure 13. The sample form handles all the data binding tasks.

The sample form allows you to move through the rows, modify rows, delete rows, and add a new row. The form is still somewhat fragile, however. When adding a row you must be careful to both select a state from the existing list, and type only a 10-character phone number (that is, numbers without punctuation).

Tip There are a number of ways you can solve the state selection problem, but there's one solution that's particularly friendly. Set the control's AutoCompleteMode property to Suggest, and its AutoCompleteSource to ListItems. Once you've made these changes, not only will users see a list of suggestions as they type, but the ComboBox control will treat the data as if you'd selected from the list. To solve the phone number length problem, your best bet is to use the new MaskedTextBox control rather than a standard TextBox control for the field. That way, you can specify the exact formatting you're willing to accept from the user.

Of course, the sample form doesn't look quite the same as it did when you ran the Visual Basic 6 application, or when you converted the application to Visual Basic 2005. For that, you'll need to change the font, and rearrange the controls on the page. We'll leave that for you to tackle. Take advantage of Visual Studio's great new form design features, like snap lines—they sure make it easier to rearrange controls on a form! When you're done rearranging, the form might look like the sample form shown in Figure 1.

Adding a Cancel Button

The original application included the capability of cancelling a pending update. In order to emulate this behavior in the new application, you might want to add a a Cancel button to the form. Because the BindingNavigator control provides a set of data-related buttons already, it seems obvious that a new Cancel button should be placed with the rest of the buttons.

Follow these steps to add a Cancel button to the form's binding navigator:

Open the sample form in the form designer (to the right of the current BindingNavigator control, at the top of the form).
When you see a drop-down arrow appear at the end of the row of buttons, click the arrow to display a list of options as shown in Figure 13.

Figure 14. Add a new control to the existing BindingNavigator control.
Select Button from the list of control types.

Tip In the next step, you'll add an image to the button. If you haven't done so already, now would be a good time to unpack the images provided by Visual Studio 2005. Browse to C:\Program Files\Microsoft Visual Studio 8\Common7\VS2005ImageLibrary\VS2005ImageLibrary.zip (use your own local path, of course), and unzip the library.
In order to supply your own image for the button, in the Properties window for the new button, find the Image property. Browse to an appropriate image. If you've unpacked the Visual Studio image library, you might select bitmaps\commands\24bitcolor\Edit_Undo.bmp from those images.
Delete the existing text from the new button's Text property, and set its Name property to cancelButton.
Double-click the new button, and add the following code to its Click event handler. This code cancels the current edit that's in progress, effectively undoing all changes to the current row:
```
TblCustomerBindingSource.CancelEdit()
```
Note It's hard not to notice the code that's already there for you, in the form. When you added the bound controls to the form, Visual Studio injected a little code into your form. When the form loads, the Load event handler runs code that fills both tables within the form's data set. In addition, when you click the Save button in the form's binding navigator, the form runs code to save the current row.
Select View | Designer.
Select File | Save All.
Run the project, and start editing a row. Make changes to a few fields. Click the new Cancel button, and verify that all the changes to the row are undone. Close the form when you're done.

Tip For more information on exactly what TblCustomerBindingSource does, check out the documentation on the System.Windows.Forms.BindingSource class. This class makes it possible for forms to display controls bound directly to data sources, and keeps all the controls that get their data from the same data source "in sync," making sure that they're all showing data from the same current row. If you're an experienced developer from earlier versions of Visual Studio, you might be familiar with the CurrencyManager class. The new BindingSource class provides the same functionality, in an extended way.

Adding a Find Button

The Visual Basic 6 example included a button that allowed you to find a specific customer, given all or part of the value of a single field. Follow these steps to add the same functionality to your data-bound application (note that this code is nearly identical to the code used in the previous article):

Add a new button to the binding navigator, as you did in the previous section. This time, name the control findButton, and use the bitmaps\commands\24bitcolor\ Find.bmp image within the image library.

Select View | Code. Immediately beneath the form's class name, add the following constant containing the name of the field in which you want to search:
```
' This is the name of the field to use for searching.
Private Const conFindField As String = "LastName"
```
Add the following method to the form's class. This method accepts a search string, and attempts to find the value within the customer table:
```
Public Function Find( _
ByVal SearchCriteria As String) As Boolean
```

' Assume failure. Dim retVal As Boolean = False

Dim dt As DataTable = Me.VB6DemoDataSet.tblCustomer ' The Select method of a DataTable returns ' an array of rows. Dim adr As DataRow() = dt.Select(SearchCriteria)

' If you found a row... If adr.Length > 0 Then ' Set the position to be the index of the ' row you found. Me.TblCustomerBindingSource.Position = _ dt.Rows.IndexOf(adr(0)) retVal = True End If Return retVal End Function

Add the following procedure to the form's class. This procedure prompts the user for a value to find, builds the search string, and calls the Find method:
```
Private Sub FindRow()
Dim nameToFind As String = _
InputBox("Enter " & conFindField & _
" value to find:", Me.Text)
```

If Not String.IsNullOrEmpty(nameToFind) Then ' Search with a wildcard, using the ' value the user entered as the beginning ' of the search string. Dim searchCriteria As String = _ String.Format("{0} LIKE '{1}%'", _ conFindField, nameToFind.Replace("'", "''"))

' Find the row.
If Not Find(searchCriteria) Then
  MessageBox.Show("No match found.", _
   "Find Row", MessageBoxButtons.OK)
End If

End If End Sub

Double click the Find button you added, and add the following code to the button's Click event handler:
```
FindRow()
```
Select View | Designer.
Select File | Save All.
Run the project, and click the Find button. Enter a name (Suyama, for example) and verify that the searching capability works. Enter an invalid name, and verify the behavior. Close the form when you're done.

Handling Form State

In order to make the form work more like the original Visual Basic 6 application, you might want to add code that enables buttons at the appropriate time, based on the state of the form. Follow these steps to add this behavior:

Add the following procedure to the form's class. Given this method, you can simply call it at the appropriate times within the form's life cycle:

Public Sub HandleNavButtons(ByVal isEditMode As Boolean)
BindingNavigatorAddNewItem.Enabled = isEditMode
BindingNavigatorDeleteItem.Enabled = isEditMode
BindingNavigatorMoveFirstItem.Enabled = isEditMode
BindingNavigatorMoveLastItem.Enabled = isEditMode
BindingNavigatorMoveNextItem.Enabled = isEditMode
BindingNavigatorMovePreviousItem.Enabled = isEditMode
BindingNavigatorPositionItem.Enabled = isEditMode
findButton.Enabled = isEditMode

TblCustomerBindingNavigatorSaveItem.Enabled = Not isEditMode cancelButton.Enabled = Not isEditMode End Sub

Add the following line of code to the form's Load event handler:
```
HandleNavButtons(True)
```

Add the same line of code to the end of the existing TblCustomerBindingNavigatorSaveItem Click event handler:

Private Sub TblCustomerBindingNavigatorSaveItem_Click( _
ByVal sender As System.Object, ByVal e As System.EventArgs)
Me.Validate()
Me.TblCustomerBindingSource.EndEdit()
Me.TblCustomerTableAdapter.Update(Me.VB6DemoDataSet.tblCustomer)
  HandleNavButtons(True)
End Sub

Add the same line of code to the end of the existing cancelButton Click event handler:

Private Sub cancelButton_Click( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles cancelButton.Click

TblCustomerBindingSource.CancelEdit() HandleNavButtons(True) End Sub

Select View | Designer.
Select the form, and set its KeyPreview property to True. (Setting the KeyPreview property to True has the same effect it does as in Visual Basic 6; that is, the form's key-handling events handle keystrokes before an individual control's events.)
At the top of the Properties window, select the Events button (the lightning bolt). From the list of events, double-click the KeyPress event.
Assuming that any key press within the form will change the state of the form, modify the KeyPress event handler so that it looks like the following:
```
Private Sub Form1_KeyPress( _
ByVal sender As System.Object, _
ByVal e As System.Windows.Forms.KeyPressEventArgs) _
Handles MyBase.KeyPress
```

HandleNavButtons(False) End Sub

The KeyPress event won't trap all the important keystrokes. Specifically, handling the Delete key requires you to trap the form's KeyDown event. As long as you're in there, it makes sense to have the form handle keystrokes like the Visual Basic 6 application did. As in the previous steps, modify the form's KeyDown event handler:
```
Private Sub Form1_KeyDown( _
ByVal sender As System.Object, _
ByVal e As System.Windows.Forms.KeyEventArgs) _
Handles MyBase.KeyDown
```

Select Case e.KeyCode Case Keys.Delete HandleNavButtons(False)

Case Keys.Enter
  ' Discard Enter Key
  e.SuppressKeyPress = True

Case Keys.Escape
  cancelButton.PerformClick()
  e.SuppressKeyPress = True

Case Keys.PageDown
  ' Ctrl+PgDn goes to the last row.
  ' PgDn goes to the next row.
  If e.Control Then
    BindingNavigatorMoveLastItem.PerformClick()
  Else
    ' The PerformClick method is great: If the 
    ' button isn't enabled, it simply doesn't 
    ' call the method. There's no need to even
    ' check to see if the button is enabled or not!
    BindingNavigatorMoveNextItem.PerformClick()
  End If

Case Keys.PageUp
  ' Ctrl+PgUp goes to the first row.
  ' PgUp goes to the previous row.
  If e.Control Then
    BindingNavigatorMoveFirstItem.PerformClick()
  Else
    BindingNavigatorMovePreviousItem.PerformClick()
  End If

Case Keys.Home
  ' Ctrl+Home goes to the first row.
  If e.Control Then
    BindingNavigatorMoveFirstItem.PerformClick()
  End If

Case Keys.End
  ' Ctrl+End goes to the last row.
  If e.Control Then
    BindingNavigatorMoveLastItem.PerformClick()
  End If

End Select End Sub

> **Tip**   The KeyDown event handler uses the Button.PerformClick method. This method is very cool. It really does nothing more than perform the button click for you. If the button isn't enabled, the code simply doesn't run. This is a simple way to avoid having the same code in multiple places in your application, and makes it easy to automate user actions from code.

Select File | Save All.
Run the project. Try various keystrokes. Edit a value, and then undo the changes. Press PGUP, CTRL+PGUP, UP, DOWN. See what effect these have on the current row.

You've done it! With very little code (and to be honest, almost all the code you wrote was to support the user interface, not the data binding), you've recreated the complete functionality of the original Visual Basic 6 application.

In this series of articles, you've worked your way through the process of migrating a simple data-oriented Visual Basic 6 application to a fully data-bound Visual Basic 2005 application. Yes, the application was not, in any sense of the word, representative of a real application. It did, however, show off a number of features, and demonstrate how you could take advantage of many new features in Visual Studio 2005. Take this application as a starting point. Investigate the BindingSource, BindingNavigator, TableAdapter, and other new classes in Visual Studio 2005. We're sure you'll agree—data binding has never been more satisfying, or simpler, in Visual Basic.

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).

Paul D. Sheriff is the President of PDSA, Inc. (www.pdsa.com), a Microsoft Partner in Southern California. Paul acts as the Microsoft Regional Director for Southern California assisting the local Microsoft offices with Developer Days and several other large events each year. Paul has authored five books on .NET, and two eBooks on SharePoint can be purchased at the PDSA Web site.