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 2007 and Excel 2010. 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, you can 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 (ADO.NET).
To validate data when a new row is added to data-bound ListObject
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;
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.BeforeAddDataBoundRow +=new Microsoft.Office.Tools.Excel. BeforeAddDataBoundRowEventHandler(list1_BeforeAddDataBoundRow);
Add code to the list1_BeforeAddDataBoundRow event handler to check whether the age entered falls within the acceptable range.
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
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; } }
Compiling the Code
This code example assumes that 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
Concepts
Extending Word Documents and Excel Workbooks in Application-Level Add-ins at Run Time
Adding Controls to Office Documents at Run Time
Automating Excel by Using Extended Objects