Edit

Validate data when a new row is added to a ListObject control

  • Article

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 VSTO Add-in projects for Excel. For more information, see Features available by Office application and project type.

Data validation

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, verify that the age entered falls within that range before the row is added.

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 a VSTO Add-in 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;
    }
}

Compile the code

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

Related content