How to: Update a data source with data from a host control

Article
02/02/2017

Applies to: Visual Studio Visual Studio for Mac

Note

This article applies to Visual Studio 2017. If you're looking for the latest Visual Studio documentation, see Visual Studio documentation. We recommend upgrading to the latest version of Visual Studio. Download it here

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. There are two main steps in this process:

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.
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 Bind data to controls in Office solutions.

Applies to: The information in this topic applies to document-level projects and VSTO Add-in projects for Excel and Word. For more information, see Features available by Office application and project type.

Update 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.
```
this.namedRange1.DataBindings["Value2"].WriteValue();
```
```
Me.NamedRange1.DataBindings("Value2").WriteValue()
```

Automatically update 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 a VSTO Add-in project, you must use code.

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

Use the System.Windows.Forms.DataSourceUpdateMode.OnPropertyChanged mode 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 System.Windows.Forms.DataSourceUpdateMode.OnValidation.
- To update the data source when the value of the data-bound property of the control changes, set this property to System.Windows.Forms.DataSourceUpdateMode.OnPropertyChanged.
  
  Note
  
  The System.Windows.Forms.DataSourceUpdateMode.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.
```
this.namedRange1.DataBindings["Value2"].DataSourceUpdateMode = 
    DataSourceUpdateMode.OnPropertyChanged;
```
```
Me.NamedRange1.DataBindings("Value2").DataSourceUpdateMode = _
    DataSourceUpdateMode.OnPropertyChanged
```

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

In Visual Studio, open the Word document or Excel workbook in the designer.
Click the control that you want to automatically update the data source.
In the Properties window, expand the (DataBindings) property.
Next to the (Advanced) property, click the ellipsis button ().
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.
Close the Formatting and Advanced Binding dialog box.

Update 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 Save data back to the database and Update data by using a TableAdapter .

To update the database

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.
```
this.customersBindingSource.EndEdit();
```
```
Me.CustomersBindingSource.EndEdit()
```
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.
```
this.customersTableAdapter.Update(this.northwindDataSet.Customers);
```
```
Me.CustomersTableAdapter.Update(Me.NorthwindDataSet.Customers)
```

Share via