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

Users can add new rows to a ListObject control that is bound to data. You can validate the user's data before committing the changes to the data source.

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

Whenever a row is added to a ListObject that is bound to data, the BeforeAddDataBoundRow event is raised. You can handle this event to perform your data validation. For example, if your application requires that only employees between the ages of 18 and 65 can be added to the data source, you can verify that the age entered falls within that range before the row is added.

Note Note

You should always check user input on the server in addition to the client. For more information, see Secure Client Applications.

To validate data when a new row is added to data-bound ListObject

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

    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");
    
    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.BeforeAddDataBoundRow +=new Microsoft.Office.Tools.Excel.
        BeforeAddDataBoundRowEventHandler(list1_BeforeAddDataBoundRow);
    
  3. Add code to the list1_BeforeAddDataBoundRow event handler to check whether the age entered falls within the acceptable range.

    private void list1_BeforeAddDataBoundRow(object sender,
        Microsoft.Office.Tools.Excel.BeforeAddDataBoundRowEventArgs e)
    {
        System.Data.DataRow row = ((System.Data.DataRowView)e.Item).Row;
    
        if (row["Age"] != null && row["Age"] != Convert.DBNull)
        {
            int ageEntered = (int)row["Age"];
    
            if (ageEntered < 21 || ageEntered > 65)
            {
                System.Windows.Forms.MessageBox.Show
                    ("Age must be between 21 and 65. The row cannot be added.");
                e.Cancel = true;
                return;
            }
            row["ID"] = id;
            id++;
        }
        else
        {
            System.Windows.Forms.MessageBox.Show("You must enter an age.");
            e.Cancel = true;
        }
    }
    

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

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft