How to obtain the data from the database and then bind the data to controls by using Visual Basic .NET or Visual Basic 2005 (821763)

SUMMARY

This article describes how to obtain the data from the database and then bind the data to controls. You can use the DataSet class and the DataAdapter class to obtain the data from the database. You can use the DataBindings property of the control to bind the data of the DataSet object to the control.

The step-by-step example in this article describes how to connect to the Northwind sample database (Northwind.mdb) in Microsoft Access and then obtain data by using the OleDbDataAdapter class. In the example, you add the Binding objects to the ControlBindingsCollection class to bind the data that you obtain to the TextBox control and to the ListBox control on the Microsoft Windows form.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:

• Microsoft Visual Studio .NET or Microsoft Visual Studio 2005
• Northwind.mdb in Access

This article assumes that you are familiar with the following topics:

• Microsoft Visual Basic .NET or Microsoft Visual Basic 2005 syntax
• Windows forms controls
• Connectivity to the Access database

back to the top

Create a Microsoft Windows Application

To create a new Windows application by using Visual Basic .NET or Visual Basic 2005, follow these steps:

1. Start Visual Studio .NET or Visual Studio 2005.
2. On the File menu, point to New, and then click Project.
3. Under Project Types, click Visual Basic Projects.
   
   Note In Visual Studio 2005, click Visual Basic under Project Types.
4. Under Templates, click Windows Application, and then click OK.
   
   By default, Form1 is created.

back to the top

Connect to the Northwind Database to Obtain the Data

Connect to Northwind.mdb in Access by using the Microsoft OLE DB provider. Use the OleDbDataAdapter class to obtain data from the database. The Fill method of the OleDbDataAdapter class loads the data into the DataSet, as follows:

1. On the View menu, click Server Explorer.
2. In Server Explorer, right-click Data Connections, and then click Add Connection.
3. In the Data Link Properties dialog box, click the Provider tab.
4. Click Microsoft Jet 4.0 OLE DB Provider on the OLE DB Provider(s) list, and then click Next.
5. On the Connection tab, click the ellipses (...) button.
6. In the Select Access Database dialog box, click the Program Files folder, click the Microsoft Office folder, click the Office10 folder, and then click the Samples file.
7. Click the Northwind.mdb file, and then click Open.
   
   Note If you use Microsoft Office Access 2002, the Samples folder is located in the Office10 folder. If you use Microsoft Office Access 2003, the Samples folder is located in the Office11 folder.
8. To verify that you are connected to Northwind.mdb, click Test Connection.
   
   If the test connection succeeds, click OK in the Data Link Properties dialog box.
9. In Server Explorer, expand your Access database connection, and then expand Tables.
10. Drag the Employees table to Form1.
    
    By default, OleDbConnection1 and OleDbDataAdapter1 are created.
11. Right-click OleDbDataAdapter1, and then click Generate Dataset.
12. In the Generate Dataset dialog box, click OK.
13. Right-click DataSet11, and then click Properties.
14. In the Properties dialog box, set the Name property to ds.

If you are using Visual Studio 2005, use the following steps:

1. On the View menu, point to Other Windows, and then click Server Explorer.
2. In Server Explorer, right-click Data Connections, and then click Add Connection.
3. In the Choose Data Source dialog box, click Microsoft Access Database File under Data source, and then click Continue.
4. Click the Brown button.
5. In the Select Microsoft Access Database File dialog box, click the Program Files folder, click the Microsoft Office folder, click the OFFICE11 folder, and then click the SAMPLES file.
6. Click the Northwind.mdb file, and then click Open.
7. To verify that you are connected to Northwind.mdb, click Test Connection. If the test connection succeeds, click OK in the Add Connection dialog box.
8. In Server Explorer, expand your Access database connection, and then expand Tables.
9. Drag the Employees table to Form1.
   
   By default, OleDbConnection1 and OleDbDataAdapter1 are created.
10. Right-click OleDbDataAdapter1, and then click Generate Dataset.
11. In the Generate Dataset dialog box, click OK.
12. Right-click DataSet11, and then click Properties.
13. In the Properties dialog box, set the Name property to ds.

back to the top

Bind the Data to the Controls

To bind the data to the controls, add the Binding object to ControlBindingsCollection. The Binding object creates and then maintains a simple binding between the property value of an object and the property value of the control. You can use the DataBindings property to access ControlBindingsCollection.

You can create Binding objects as follows:

• Specify the property of the control that you want to bind the data to. For example, to display data in a TextBox control, specify the Text property.
• Specify the instance of a data source. For example, use the DataSet as a data source.
• Specify the navigation path. The navigation path may be an empty string (""), may be a single property name, or may be a period-delimited hierarchy of names. When you set the navigation path to an empty string, the ToString method is called on the underlying data source object. For example, if you use a DataSet as a data source, you can specify TableName.ColumnName as the navigation path.

The BindingManagerBase class enables you to synchronize data-bound controls on a Windows form that are bound to the same data source. In the following example, the Windows form contains a ListBox control, TextBox controls, and RichTextBox controls that are bound to the same data source but are bound to different columns of the Employee data table. You must synchronize the controls to display the details of the same employee. The CurrencyManager class inherits from the BindingManagerBase class. CurrencyManager synchronizes the controls by maintaining a pointer to the current item. The controls are bound to the current item. Therefore, they display the information for the same row. To bind the data in the DataSet to controls on Form1, follow these steps:

1. Add two Panel controls to Form1.
2. Add two Label controls to Form1.
3. Put Label1 above Panel1, and then put Label2 above Panel2.
4. In the Properties dialog box, set the Text property of Label1 to Select the Employee, and then set the Text property of Label2 to Details of the Employee.
5. Add a ListBox control to Form1.
6. Put ListBox1 in Panel1.
7. In the Properties dialog box of ListBox1, set the DataSource property to ds, and then set the DisplayMember property to Employees.FirstName.
8. Add five TextBox controls to Form1. Put the controls in Panel2.
9. In the Properties dialog box, set the Name property of the TextBox control and set the Text property of the TextBox control to the following values:
   
   

   Control	Name	Text
   TextBox1	EmpId	EmpId
   TextBox2	LastName	LastName
   TextBox3	BirthDate	BirthDate
   TextBox4	Title	Title
   TextBox5	HomePhone	HomePhone

10. Add two RichTextBox controls to Form1. Put the controls in Panel2.
11. In the Properties dialog box, set the Name property of the RichTextBox control and set the Text property of the RichTextBox control to the following values:
    
    

    Control	Text	Name
    RichTextBox1	Notes	Notes
    RichTextBox2	Address	Address

12. Add seven Label controls to Form1. Put each Label control on the left side of the TextBox control and on the left side of the RichTextBox control as follows:
    
    

    Control Name	Label
    EmpId	Label3
    LastName	Label4
    BirthDate	Label5
    Title	Label6
    HomePhone	Label7
    Notes	Label8
    Address	Label9

13. In the Properties dialog box, set the Text property of the Label controls to the following values:
    
    

    Control Name	Text Value
    Label3	Employee ID
    Label4	Last Name
    Label5	Date of Birth
    Label6	Title
    Label7	Home Phone Number
    Label8	Notes
    Label9	Address of the Employee

14. Add the following code to the Form1_Load event handler:

    'Adds rows to dataset.
    OleDbDataAdapter1.Fill(ds)
    'Add a binding object to the controls.
    'Set Text as the data-bound property, and set ds as the data source.
    'TableName.ColumnName specifies the data member. 
    BirthDate.DataBindings.Add(New Binding("Text", ds, "Employees.BirthDate"))
    LastName.DataBindings.Add(New Binding("Text", ds, "Employees.LastName"))
    Notes.DataBindings.Add(New Binding("Text", ds, "Employees.Notes"))
    Title.DataBindings.Add(New Binding("Text", ds, "Employees.Title"))
    HomePhone.DataBindings.Add(New Binding("Text", ds, "Employees.HomePhone"))
    Address.DataBindings.Add(New Binding("Text", ds, "Employees.Address"))
    EmpId.DataBindings.Add(New Binding("Text", ds, "Employees.EmployeeID"))

back to the top

Verify That It Works

1. On the Build menu, click Build Solution.
2. On the Debug menu, click Start.
3. In the ListBox control, click an employee name.
   
   The employee details are available in the TextBox controls and in the RichTextBox controls.

back to the top