How to: Handle Errors When a New Row is Added to a ListObject Control

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");
   id++;
   
   list1.SetDataBinding(employeeTable, "", "FirstName", "LastName", "Age");
   
   list1.ErrorAddDataBoundRow += new Microsoft.Office.Tools.Excel.
       ErrorAddDataBoundRowEventHandler(list1_ErrorAddDataBoundRow);
   

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;
           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

Tasks

How to: Map ListObject Columns to Data

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

Concepts

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