Walkthrough: Simple Data Binding in a Document-Level Project

  • Article

This walkthrough demonstrates the basics of data binding in a document-level project. A single data field in a SQL Server database is bound to a named range in Microsoft Office Excel. The walkthrough also shows how to add controls that enable you to scroll through all the records in the table.

Applies to: The information in this topic applies to document-level projects for Excel 2013 and Excel 2010. For more information, see Features Available by Office Application and Project Type.

This walkthrough illustrates the following tasks:

  • Creating a data source for an Excel project.

  • Adding controls to a worksheet.

  • Scrolling through database records.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

Prerequisites

You need the following components to complete this walkthrough:

  • An edition of Visual Studio 2012 that includes the Microsoft Office developer tools. For more information, see Configuring a Computer to Develop Office Solutions.

  • Excel 2013 or Excel 2010.

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

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

Creating a New Project

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.

Creating the Data Source

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, display it by, on the menu bar, choosing View, Other Windows, Data Sources.

  2. Choose 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.

Adding Controls to the Worksheet

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

Initializing the Controls

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.

    With Me
    .Button1.Text = "|<"
    .Button2.Text = "<"
    .Button3.Text = ">"
    .Button4.Text = ">|" 
End With

    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.

Adding Code to Enable Scrolling 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 Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles Button1.Click

    Me.CustomersBindingSource.MoveFirst()
End Sub

    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 Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles Button2.Click

    Me.CustomersBindingSource.MovePrevious()
End Sub

    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 Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles Button3.Click

    Me.CustomersBindingSource.MoveNext()
End Sub

    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 Sub Button4_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles Button4.Click

    Me.CustomersBindingSource.MoveLast()
End Sub

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

Testing the Application

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.

Next Steps

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

See Also

Tasks

Walkthrough: Complex Data Binding in a Document-Level Project

Other Resources

Binding Data to Controls in Office Solutions

Data in Office Solutions