How to: Update a Data Source with Data from a Host Control

Applies to
The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office. Document-level projects Excel 2003 Excel 2007 Word 2003 Word 2007 Application-level projects Excel 2007 Word 2007 For more information, see Features Available by Application and Project Type.

You can bind a host control to a data source and update the data source with the changes that are made to the data in the control.

Starting in Visual Studio 2008 Service Pack 1 (SP1), you can bind host controls to data in application-level projects. If you do not have SP1 installed, you can use host controls only in document-level projects.

There are two main steps in this process:

1. Update the in-memory data source with the modified data in the control. Typically, the in-memory data source is a DataSet, a DataTable, or some other data object.

2. Update the database with the changed data in the in-memory data source. This is applicable only if the data source is connected to a back-end database, such as a SQL Server or Microsoft Office Access database.

For more information about host controls and data binding, see Host Items and Host Controls Overview and Binding Data to Controls in Office Solutions.

Updating the In-Memory Data Source

By default, host controls that enable simple data binding (such as content controls on a Word document or a named range control on an Excel worksheet) do not save data changes to the in-memory data source. That is, when an end user changes a value in a host control and then navigates away from the control, the new value in the control is not automatically saved to the data source.

To save the data to the data source, you can write code that updates the data source in response to a specific event at run time, or you can configure the control to automatically update the data source when the value in the control changes.

You do not need to save ListObject changes to the in-memory data source. When you bind a ListObject control to data, the ListObject control automatically saves changes to the in-memory data source without requiring additional code.

To update the in-memory data source at run time

• Call the WriteValue method of the Binding object that binds the control to the data source.

  The following example saves changes made to a NamedRange control on an Excel worksheet to the data source. This example assumes that you have a NamedRange control named namedRange1 with its Value2 property bound to a field in a data source.

  Me.NamedRange1.DataBindings("Value2").WriteValue()
  

  this.namedRange1.DataBindings["Value2"].WriteValue();
  

Automatically Updating the In-Memory Data Source

You can also configure a control so that it automatically updates the in-memory data source. In a document-level project, you can do this by using code or the designer. In an application-level project, you must use code.

To set a control to automatically update the in-memory data source by using code

• Use the DataSourceUpdateMode property of the Binding object that binds the control to the data source. There are two options for updating the data source:

  • To update the data source when the control is validated, set this property to OnValidation.

  • To update the data source when the value of the data-bound property of the control changes, set this property to OnPropertyChanged.

    Note

    The OnPropertyChanged option does not apply to Word host controls, because Word does not offer document-change or control-change notifications. However, this option can be used for Windows Forms controls on Word documents.

  The following example configures a NamedRange control to automatically update the data source when the value in the control changes. This example assumes that you have a NamedRange control named namedRange1 with its Value2 property bound to a field in a data source.

  Me.NamedRange1.DataBindings("Value2").DataSourceUpdateMode = _
      DataSourceUpdateMode.OnPropertyChanged
  

  this.namedRange1.DataBindings["Value2"].DataSourceUpdateMode = 
      DataSourceUpdateMode.OnPropertyChanged;
  

To set a control to automatically update the in-memory data source by using the designer

1. In Visual Studio, open the Word document or Excel workbook in the designer.

2. Click the control that you want to automatically update the data source.

3. In the Properties window, expand the (DataBindings) property.

4. Next to the (Advanced) property, click the ellipsis button ().

5. In the Formatting and Advanced Binding dialog box, click the Data Source Update Mode drop-down list and select one of the following values:

   • To update the data source when the control is validated, select OnValidation.

   • To update the data source when the value of the data-bound property of the control changes, select OnPropertyChanged.

     Note

     The OnPropertyChanged option does not apply to Word host controls, because Word does not offer document-change or control-change notifications. However, this option can be used for Windows Forms controls on Word documents.

6. Close the Formatting and Advanced Binding dialog box.

Updating the Database

If the in-memory data source is associated with a database, you must update the database with the changes to the data source. For more information about updating a database, see Saving Data Overview and How to: Update Data by Using a TableAdapter.

To update the database

1. Call the EndEdit method of the BindingSource for the control.

   The BindingSource is automatically generated when you add a data-bound control to a document or workbook at design time. The BindingSource connects the control to the typed dataset in your project. For more information, see BindingSource Component Overview.

   The following code example assumes that your project contains a BindingSource named customersBindingSource.

   Me.CustomersBindingSource.EndEdit()
   

   this.customersBindingSource.EndEdit();
   

2. Call the Update method of the generated TableAdapter in your project.

   The TableAdapter is automatically generated when you add a data-bound control to a document or workbook at design time. The TableAdapter connects the typed dataset in your project to the database. For more information, see TableAdapter Overview.

   The following code example assumes that you have a connection to the Customers table in the Northwind database, and that your project contains a TableAdapter named customersTableAdapter and a typed dataset named northwindDataSet.

   Me.CustomersTableAdapter.Update(Me.NorthwindDataSet.Customers)
   

   this.customersTableAdapter.Update(this.northwindDataSet.Customers);
   

See Also

Tasks

How to: Update Data by Using a TableAdapter

How to: Scroll Through Database Records in a Worksheet

How to: Populate Worksheets with Data from a Database

How to: Populate Documents with Data from Objects

How to: Populate Documents with Data from a Database

How to: Populate Documents with Data from Services

Concepts

Binding Data to Controls in Office Solutions

Saving Data Overview

Change History

Date	History	Reason
July 2008	Added information about binding data in application-level add-ins.	SP1 feature change.