This documentation is archived and is not being maintained.

Walkthrough: Binding Cells to Multiple Columns in a Table

Note Required applications

The features in this topic are available only if you have the required applications installed.

For more information, see Features Available by Product Combination.

  • One of these development environments:

    VSTO 2005


    Visual Studio Team System

  • Microsoft Office 2003

This walkthrough demonstrates the basics of binding multiple cells in a Microsoft Office Excel 2003 worksheet to fields in the Northwind SQL Server database.

During this walkthrough, you will learn how to:

  • Add controls to a worksheet.

  • Save any data changes back to the database.

  • Test your project.


The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.

To complete this walkthrough, you will need:

  • Microsoft Visual Studio 2005 Tools for the Microsoft Office System.

  • Microsoft Office Excel 2003.

  • Access to a server with the Northwind SQL Server sample database.

  • Permissions to read from and write to the SQL Server database.

In this step, you will create an Excel Workbook project.

To create a new project

  • Create an Excel Workbook project with the name My Complex Data Binding, using either Visual Basic or C#. Make sure that Create a new document is selected. For more information, see How to: Create Visual Studio Tools for Office Projects.

    Visual Studio opens the new Excel workbook in the designer and adds the My Complex Data Binding project to Solution Explorer.

Use the Data Sources window to add a typed dataset to your project.

To create the data source

  1. If the Data Sources window is not visible, click Show Data Sources on the Data menu.


    If Show Data Sources is not available, click inside the Excel workbook and then check again.

  2. Click Add New Data Source to start the Data Source Configuration Wizard.

  3. Select Database and then click Next.

  4. Select a data connection to the Northwind sample SQL Server database, or add a new connection using the New Connection button.

  5. After a connection has been selected or created, click Next.

  6. Clear the option to save the connection if it is selected, and then click Next.

  7. Expand the Tables node in the Database objects window.

  8. Select the check box next to the Employees table.

  9. Click Finish.

The wizard adds the Employees table to the Data Sources window. It also adds a typed dataset to your project that is visible in Solution Explorer.

For this walkthrough, you need a ListObject control and a Button control on the first worksheet. First, add the ListObject control from the Data Sources window so that it is automatically bound to the data source. Next, add the Button control from the Toolbox.

To add a listobject

  1. Verify that the My Complex Data Binding.xls workbook is open in the Visual Studio designer, with Sheet1 displayed.

  2. Open the Data Sources window and select the Employees node.

  3. Click the drop-down arrow that appears.

  4. Select ListObject in the drop-down list, and then drag the Employees table to cell A6.

    A ListObject control named EmployeesListObject is created in cell A6. At the same time, a BindingSource named EmployeesBindingSource, a table adapter, and a DataSet instance are added to the project. The control is bound to the BindingSource, which in turn is bound to the DataSet instance.

To add a button

  • From the Common Controls tab of the Toolbox, add a Button control to cell A4 of the worksheet.

The next step is to add text to the button when the worksheet opens.

Add text to the button in the Startup event handler.

To initialize the control

  1. In Solution Explorer, right-click Sheet1.vb or Sheet1.cs, and then click View Code on the shortcut menu.

  2. Add the following code to the Sheet1_Startup method to set the text for the button.

    this.button1.Text = "Save";
  3. For C# only, add an event handler for the Click event to the Sheet1_Startup method.

    this.button1.Click += new EventHandler(button1_Click);

Now add code to handle the Click event of the button.

Any changes have been made to the data exist only in the local dataset until they are explicitly saved back to the database.

To save changes to the database

  • Add an event handler for the Click event of the button, and add the following code to commit all changes that have been made in the dataset back to the database:

    private void button1_Click(object sender, EventArgs e)
        catch (System.Data.DataException ex)

Now you can test your workbook to make sure that the data appears as expected, and that you can manipulate the data in the list object.

To test the data binding

  • Press F5.

    Verify that when the workbook opens, the list object is filled with data from the Employees table.

To modify data

  1. Click cell B7, which should contain the name Davolio.

  2. Type the name Anderson, and then press ENTER.

To modify a column header

  1. Click the cell that contains the column header LastName.

  2. Type Last Name, including a space between the two words, and then press ENTER.

To save data

  1. Click Save on the worksheet.

  2. Close the workbook. Click No when prompted to save the changes you made.

  3. Press F5 to run the project again.

    The list object is filled with data from the Employees table.

  4. Notice that the name in cell B7 is still Anderson, which is the data change that you made and saved back to the database. The column header LastName has changed back to its original form with no space, because the column header is not bound to the database and you did not save the changes you made to the worksheet.

To add new rows

  1. Select a cell inside the list object.

    A new row appears at the bottom of the list, with an asterisk (*) in the first cell of the new row.

  2. Add the following information in the empty row:

    EmployeeID LastName FirstName Title




    Sales Manager

To delete rows

  1. Select row 16, which should contain the new row that you added, by clicking the number 16 on the far left side of the worksheet.

  2. On the Edit menu, click Delete.

To sort the rows in the List

  1. Select a cell inside the list.

    Arrow buttons appear in each column header.

  2. Click the arrow button in the Last Name column header.

  3. Click Sort Ascending.

    The rows are sorted alphabetically by last names.

To filter information

  1. Select a cell inside the list.

  2. Click the arrow button in the Title column header.

  3. Click Sales Representative.

    The list shows only those rows that have Sales Representative in the Title column.

  4. Click the arrow button in the Title column header again.

  5. Click (All).

    Filtering is removed and all the rows appear.

This walkthrough shows the basics of binding a table in a database to a list object. Here are some tasks that might come next: