Information
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

Walkthrough: Binding a Worksheet Cell to a Database Field

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

    -or-

    Visual Studio Team System

  • Microsoft Office 2003

This walkthrough demonstrates the basics of binding a single data field in a SQL Server database to a named range in Microsoft Office Excel 2003. It includes adding controls that enable you to scroll through all the records in the table.

During this walkthrough, you will learn how to:

  • Create a data source for an Excel project.

  • Add controls to a worksheet.

  • Scroll through database records.

  • Test your project.

NoteNote

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

Visual Studio opens the new Excel workbook in the designer and adds the My Simple 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.

    NoteNote

    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 Customers table.

  9. Click Finish.

The wizard adds the Customers 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 two named ranges and four buttons on the first worksheet. First, add the two named ranges from the Data Sources window so that they are automatically bound to the data source. Next, add the buttons from the Toolbox.

To add two named ranges

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

  2. Open the Data Sources window and expand the Customers node.

  3. Select the CompanyName column, and then click the drop-down arrow that appears.

  4. Select NamedRange in the drop-down list, and then drag the CompanyName column to cell A1.

    A NamedRange control named companyNameNamedRange is created in cell A1. At the same time, a BindingSource named customersBindingSource, 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.

  5. Select the CustomerID column in the Data Sources window, and then click the drop-down arrow that appears.

  6. Click NamedRange in the drop-down list, and then drag the CustomerID column to cell B1.

  7. Another NamedRange control named customerIDNamedRange is created in cell B1, and bound to the BindingSource.

To add four buttons

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

    This button is named Button1.

  2. Add three more buttons to the following cells in this order, so that the names are as shown:

    Cell (Name)

    B3

    Button2

    C3

    Button3

    D3

    Button4

The next step is to add text to the buttons, and in C# add event handlers.

Set the button text and add event handlers during the Startup event.

To initialize the controls

  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 each button.

    this.button1.Text = "|<";
    this.button2.Text = "<";
    this.button3.Text = ">";
    this.button4.Text = ">|";
    
    
  3. For C# only, add event handlers for the button click events to the Sheet1_Startup method.

    this.button1.Click += new EventHandler(button1_Click);
    this.button2.Click += new EventHandler(button2_Click);
    this.button3.Click += new EventHandler(button3_Click);
    this.button4.Click += new EventHandler(button4_Click);
    
    

Now add code to handle the Click events of the buttons so that the user can browse through the records.

Add code to the Click event handler of each button to move through the records.

To move to the first record

  • Add an event handler for the Click event of the Button1 button, and add the following code to move to the first record:

    private void button1_Click(object sender, System.EventArgs e)
    {
        this.customersBindingSource.MoveFirst();
    }
    
    

To move to the previous record

  • Add an event handler for the Click event of the Button2 button, and add the following code to move the position back by one:

    private void button2_Click(object sender, System.EventArgs e)
    {
        this.customersBindingSource.MovePrevious(); 
    }
    
    

To move to the next record

  • Add an event handler for the Click event of the Button3 button, and add the following code to advance the position by one:

    private void button3_Click(object sender, System.EventArgs e)
    {
        this.customersBindingSource.MoveNext();
    }
    
    

To move to the last record

  • Add an event handler for the Click event of the Button4 button, and add the following code to move to the last record:

    private void button4_Click(object sender, System.EventArgs e)
    {
        this.customersBindingSource.MoveLast();
    }
    
    

Now you can test your workbook to make sure that you can browse through the records in the database.

To test your workbook

  1. Press F5 to run your project.

  2. Confirm that the first record appears in cells A1 and B1.

  3. Click the > (Button3) button and confirm that the next record appears in cell A1 and B1.

  4. Click the other scroll buttons to confirm that the record changes as expected.

This walkthrough shows the basics of binding a named range to a field in a database. Here are some tasks that might come next:

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

Show:
© 2014 Microsoft