Share via

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

  • Article

Applies to: yesVisual Studio noVisual 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

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;

    Dim id As Integer = 0
Dim employeeTable As System.Data.DataTable

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

    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")

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

    Private Sub list1_BeforeAddDataBoundRow(ByVal sender As Object, ByVal e As  _
    Microsoft.Office.Tools.Excel.BeforeAddDataBoundRowEventArgs) _
    Handles list1.BeforeAddDataBoundRow

    Dim row As System.Data.DataRow = (CType(e.Item, System.Data.DataRowView)).Row

    If Not row("Age") Is Nothing And Not row("Age") Is Convert.DBNull Then

        Dim ageEntered As Integer = CType(row("Age"), Int32)

        If ageEntered < 21 Or ageEntered > 65 Then
            System.Windows.Forms.MessageBox.Show _
                ("Age must be between 21 and 65. The row cannot be added.")
            e.Cancel = True
            Return
        End If

        row("ID") = id
        id += 1

    Else
        System.Windows.Forms.MessageBox.Show("You must enter an age.")
        e.Cancel = True
    End If
End Sub

Compile the code

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

See also