How to: Handle Errors When a New Row is Added to a ListObject 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 2007

  • Excel 2003

Application-level projects

  • Excel 2007

For more information, see Features Available by Application and Project Type.

Users can add new rows to a ListObject control that is bound to data. Sometimes the relationship of the list object to the data source causes routine errors. For example, you can map which columns you want to appear in the ListObject, so if you omit columns that have restrictions, such as a field that cannot accept null values, errors are raised every time a row is created. You can write code to add the missing values in an event handler for the ErrorAddDataBoundRow event.

To handle errors that occur when a data bound row is added

  1. Create variables for the ID and DataTable at the class level.

    Dim id As Integer = 0
    Dim employeeTable As System.Data.DataTable
    private int id = 0;
    private System.Data.DataTable employeeTable;
  2. Create a new DataTable and add sample columns and data in the Startup event handler of the Sheet1 class (in a document-level project) or ThisAddIn class (in an application-level project).

    employeeTable = New System.Data.DataTable("Employees")
    Dim column As System.Data.DataColumn = employeeTable.Columns.Add("Id", GetType(Int32))
    column.AllowDBNull = False
    employeeTable.Columns.Add("FirstName", GetType(String))
    employeeTable.Columns.Add("LastName", GetType(String))
    employeeTable.Columns.Add("Age", GetType(Int32))
    employeeTable.Rows.Add(id, "Nancy", "Anderson", 56)
    employeeTable.Rows.Add(id, "Robert", "Brown", 44)
    id += 1
    list1.SetDataBinding(employeeTable, "", "FirstName", "LastName", "Age")
    employeeTable = new System.Data.DataTable("Employees");
    System.Data.DataColumn column = 
        employeeTable.Columns.Add("Id", typeof(int));
    column.AllowDBNull = false;
    employeeTable.Columns.Add("FirstName", typeof(string));
    employeeTable.Columns.Add("LastName", typeof(string));
    employeeTable.Columns.Add("Age", typeof(int));
    employeeTable.Rows.Add(id, "Nancy", "Anderson", "56");
    employeeTable.Rows.Add(id, "Robert", "Brown", "44");
    list1.SetDataBinding(employeeTable, "", "FirstName", "LastName", "Age");
    list1.ErrorAddDataBoundRow += new Microsoft.Office.Tools.Excel.
  3. In the event handler for the ErrorAddDataBoundRow event, add the next incremented ID number to the Id field and add the row again.

    Private Sub list1_ErrorAddDataBoundRow(ByVal sender As Object, ByVal e As  _
        Microsoft.Office.Tools.Excel.ErrorAddDataBoundRowEventArgs) _
        Handles list1.ErrorAddDataBoundRow
        Dim row As System.Data.DataRow = (CType(e.Item, System.Data.DataRowView)).Row
        If TypeOf (e.InnerException) Is NoNullAllowedException Then
            row("Id") = id
            id += 1
            e.Retry = True 
        End If 
    End Sub
    private void list1_ErrorAddDataBoundRow(object sender,
        Microsoft.Office.Tools.Excel.ErrorAddDataBoundRowEventArgs e)
        System.Data.DataRow row = ((System.Data.DataRowView)e.Item).Row;
        if (e.InnerException is NoNullAllowedException)
            row["Id"]= id;
            e.Retry = true;

Compiling the Code

  • This code example assumes you have an existing ListObject named list1 on the worksheet in which this code appears.

See Also


How to: Map ListObject Columns to Data

How to: Validate Data When a New Row is Added to a ListObject Control


Extending Word Documents and Excel Workbooks in Application-Level Add-ins at Run Time

Excel Application-Level Add-in Development

Adding Controls to Office Documents at Run Time

ListObject Control

Understanding ListObject Column and Row Order Persistence

Other Resources

Controls on Office Documents

Excel Host Controls

Change History




July 2008

Modified the example so that it can be used in an application-level add-in.

SP1 feature change.