Thursday, June 26, 2008

How to add Column/Row Validation Using Typed DataTable

In this post we will see how we can add the Validation Rule in Typed DataTable of a Typed DataSet (using Partial Class) and then how can we use this Validation on our Form in DataGridView or Details.

Level: Intermediate

Knowledge Required:
  • Typed DataSet
  • DataTable
  • Partial Class
  • DataGridView
  • ErrorProvider
Description:
This post is based on Beth Massi's Video Tutorial.

As we have used the DataGridView control for entering the data in a DataTable. If we have set one column of our DataTable AllowDBNull = False, then in DataGridView we encounter an exception when we try to enter a Null in this column. This exception can be handled in DataError event of DataGridView control. But the row will be removed from list anyway. So to make it more user friendly, we do the following things,

1) Set the AllowDBNull = True for the column
2) Add Validation Rule in the DataTable

Example:
I have created a Typed DataSet StudentDataSet in which I have added 1 Table


  • Student_ID (primary key, AllowDBNull=False, AutoIncreament=True)
  • Student_Name (AllowDBNull=True)
  • Father_Name (AllowDBNull=True)
  • Age (AllowDBNull=True)
All the columns (except the 1st one) are Allowed to have NULL values. Now we will add the Validation Business Rule in our DataTable by using Partial Class as,

Partial Class StudentDataSet
Partial Class StudentDataTable
Private Sub CheckStudentName(ByVal rowStudent As StudentDataSet.StudentRow)
If rowStudent.IsStudent_NameNull() OrElse rowStudent.Student_Name = "" Then
' set error
rowStudent.SetColumnError(Me.Student_NameColumn, "Student Name is Required")
Else
' ok clear the error
rowStudent.SetColumnError(Me.Student_NameColumn, "")
End If
End Sub

Private Sub CheckAge(ByVal rowStudent As StudentDataSet.StudentRow)
If rowStudent.IsAgeNull OrElse _
rowStudent.Age < 3 OrElse _
rowStudent.Age > 10 Then
rowStudent.SetColumnError(Me.AgeColumn, "Age must be between 3 to 10")
Else
rowStudent.SetColumnError(Me.AgeColumn, "")
End If
End Sub

Private Sub StudentDataTable_ColumnChanged(ByVal sender As Object, ByVal e As System.Data.DataColumnChangeEventArgs) Handles Me.ColumnChanged
If e.Column Is Me.Student_NameColumn Then
Call Me.CheckStudentName(CType(e.Row, StudentRow))
ElseIf e.Column Is Me.AgeColumn Then
Call Me.CheckAge(CType(e.Row, StudentRow))
End If
End Sub

Private Sub
StudentDataTable_TableNewRow(ByVal sender As Object, ByVal e As System.Data.DataTableNewRowEventArgs) Handles Me.TableNewRow
Call Me.CheckStudentName(CType(e.Row, StudentRow))
Call Me.CheckAge(CType(e.Row, StudentRow))
End Sub
End Class
End Class

First we have created 2 private methods

1) CheckStudentName() - validates the Student Name column
2) CheckAge() - validates the Age column

Then we have used 2 DataTable Events

1) ColumnChanged - in this event handler we check which column has been changed then we call the method according to that column
2) TableNewRow - in this event handler we execute both methods, since whole row is chagned

Here we have done with our Custom Validation. Now we just simply put the DataGridView control on Form, bind it with this DataTable and the rest of the things will be handled by DataGridView itself.

DataGridView:

As you can see the DataGridView is Displaying a Red Icon in the Age column and it displays a ToolTip of Error that we have set in our Validation.

And finally when we are about to Save this Table, then we will first check the Error as,

If Me.StudentDataSet.Student.HasErrors() Then
MsgBox("Table contains Error, cannot save", MsgBoxStyle.Exclamation, "Save")
Else
MsgBox("Table does NOT contain any Error, can Save now", MsgBoxStyle.Information, "Save")
End If

Detail View:


Now we can implement the same thing in our Detail View. As you can see the above Form I have put the TextBox Controls and have Bind them with the same Table. But to show the Error Icon we will use the ErrorProvider Component.

1) Add the ErrorProvider Component
2) Set its BindingSource to the StudentBindingSource

That is it, ErrorProvider will display the Error Icon with TextBox that violates the Business Rule. Note that StudentBindingSource is the same BindingSource which is Bound with the TextBox Controls.

Project Details:
Custom Validation on Typed DataTable

Project Download:
CustomValidation.zip

4 comments:

Pietswieb said...

Hi Arsalan,

thanks for your post. But i've still got a question.

In my opion it a bit strange to set the AllowDBNull in the database to true when in the businesslayer the validation does not allow nulls.

I was hoping that the dataerror event would not occur in the datagrid when the columnerror was set.

Now you've got to handel the error twice. First as a columnValidation and secondly the dataerror in the Datagridview.

I like to set the allownull option as close to the database as possible to prevent null values inserted directy into the database.

kind regards,

Frans van Ek

Arsalan Tamiz said...

Hi Frans,

Your approach is correct that to put “allownull option as closer as possible to database”. But it is better to put the constraints ON the Database level. Because if some how code passes the validation from your business layer then it will be stopped on Database end. Another reason to put the restriction on Database level is because we are HUMAN, we do mistakes. So by restricting on database end, we remove the chance of mistake.

As far as the handling of error is concern, we don’t need to handle any error, since we have already set the AllowDBNull = True (which means we allowed to have NULL values, this is a necessary step, otherwise it is useless to write custom validation). So if we just add the blank lines in the DataGridView control, it will allow us to do so. But it will be pointing the Error by a Red Icon, normally no exception occurs when we use SetErrorColumn(), it just sets an indication. And finally when we are about to save the data in Physical Database we first check any error exists in DataTable or NOT then we send this table to save.

According to MSDN:
DataError event occurs when an external data-parsing or validation operation throws an exception, or when an attempt to commit data to a data source fails.

Anonymous said...

Hi Arsalan,

How to combine this code with your previous code Making Enter Key, Move to Next Cell/Column in DataGridView After Cell Edit or DatagridviewEnterMoveNext class? Shall I drag it from the toolbox or what?

Unknown said...

It is not necessary to create partial class.. we can add the events handler at Form load event