ProSol-Inc
asked on
Validating data, Visual Basic, VS2005
Visual Basic, VS2005
How do I validate the data in a table before updating to the database.
Here's the situation:
Using a binding navigator and strongly type datasets, with a form that will load from 1 to N records, a user is able to make changes on any record by scrolling with the binding navigator. When they press save, I want to evaluate certain columns in the table, throw an error, have the form return to the offending record, and positon at the offending field.
This sounds simple enough but I've been struggling for several days trying to ascertain the techniques that i'm supposed to be employing. Is my strategy of validating the table before the .Update off base? Am I supposed to be checking before they scroll from the record? How are either of those strategies expressed in code? For example, how do I repostion the form to the record that generated the error? How do i cancel a move next or move previous if there is an error on the current record? (For that matter, does the move forward or prevous click even happen before or after the record has moved?)
Despite hours of searching, I've been unable to locate any decent documention on exactly how all of these new constructs are supposed to be employed in real-life, data driven applications. At the very least I need references to documentation that is demonstrating practical examples. (Not the tripe that passes for coding examples in some of the MS documentation.)
How do I validate the data in a table before updating to the database.
Here's the situation:
Using a binding navigator and strongly type datasets, with a form that will load from 1 to N records, a user is able to make changes on any record by scrolling with the binding navigator. When they press save, I want to evaluate certain columns in the table, throw an error, have the form return to the offending record, and positon at the offending field.
This sounds simple enough but I've been struggling for several days trying to ascertain the techniques that i'm supposed to be employing. Is my strategy of validating the table before the .Update off base? Am I supposed to be checking before they scroll from the record? How are either of those strategies expressed in code? For example, how do I repostion the form to the record that generated the error? How do i cancel a move next or move previous if there is an error on the current record? (For that matter, does the move forward or prevous click even happen before or after the record has moved?)
Despite hours of searching, I've been unable to locate any decent documention on exactly how all of these new constructs are supposed to be employed in real-life, data driven applications. At the very least I need references to documentation that is demonstrating practical examples. (Not the tripe that passes for coding examples in some of the MS documentation.)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi
as stated in my preliminary post , there are really a lot of solutions to this senario.
with the DataRow.RowError Property you can check and fetch the row(s) that contains an error before doing a update to the datasource and show the rows that contains errors that has to be corrected by the user before the final save.
http://msdn2.microsoft.com/en-us/library/system.data.datarow.rowerror.aspx
a small test:
Private Sub SetRowErrors(ByVal table As DataTable)
' Set error text for ten rows.
Dim i As Integer
For i = 0 to 10
' Insert column 1 value into each error.
table.Rows(i).RowError = "ERROR: " & _
table.Rows(i)(1).ToString(
Next
' Get the DataSet for the table, and test it for errors.
Dim dataSet As DataSet = table.DataSet
TestForErrors(dataSet)
End Sub
Private Sub TestForErrors(ByVal dataSet As DataSet)
' Test for errors. If DataSet has errors,
' test each table.
If dataSet.HasErrors
Dim tempDataTable As DataTable
For Each tempDataTable in dataSet.Tables
' If the table has errors, then print them.
If(tempDataTable.HasErrors
PrintRowErrs(tempDataTable
End If
Next
' Refresh the DataGrid to see the error-marked rows.
DataGrid1.Refresh()
End If
End Sub
Private Sub PrintRowErrs(ByVal table As DataTable)
Dim row As DataRow
For Each row in table.Rows
If(row.HasErrors) Then
Console.WriteLine(row.RowE
End If
Next
End Sub
regarding e.cancel
http://msdn2.microsoft.com/en-us/library/safa4957(vs.80).aspx
Private Sub CancelButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CancelButton.Click
BindingSourceName.CancelEd
End Sub
or
DataRow.CancelEdit Method
http://msdn2.microsoft.com/en-US/library/system.data.datarow.canceledit.aspx
Private Sub AcceptOrReject(ByVal row As DataRow)
' Use a function to validate the row's values.
' If the function returns true, end the edit;
' otherwise cancel it.
If ValidateRow(row) Then
row.EndEdit()
Else
row.CancelEdit()
End If
End Sub
Private Function ValidateRow(ByVal row As DataRow) As Boolean
Dim isValid As Boolean
' Insert code to validate the row values.
' Set the isValid variable.
ValidateRow = isValid
End Function
as stated in my preliminary post , there are really a lot of solutions to this senario.
with the DataRow.RowError Property you can check and fetch the row(s) that contains an error before doing a update to the datasource and show the rows that contains errors that has to be corrected by the user before the final save.
http://msdn2.microsoft.com/en-us/library/system.data.datarow.rowerror.aspx
a small test:
Private Sub SetRowErrors(ByVal table As DataTable)
' Set error text for ten rows.
Dim i As Integer
For i = 0 to 10
' Insert column 1 value into each error.
table.Rows(i).RowError = "ERROR: " & _
table.Rows(i)(1).ToString(
Next
' Get the DataSet for the table, and test it for errors.
Dim dataSet As DataSet = table.DataSet
TestForErrors(dataSet)
End Sub
Private Sub TestForErrors(ByVal dataSet As DataSet)
' Test for errors. If DataSet has errors,
' test each table.
If dataSet.HasErrors
Dim tempDataTable As DataTable
For Each tempDataTable in dataSet.Tables
' If the table has errors, then print them.
If(tempDataTable.HasErrors
PrintRowErrs(tempDataTable
End If
Next
' Refresh the DataGrid to see the error-marked rows.
DataGrid1.Refresh()
End If
End Sub
Private Sub PrintRowErrs(ByVal table As DataTable)
Dim row As DataRow
For Each row in table.Rows
If(row.HasErrors) Then
Console.WriteLine(row.RowE
End If
Next
End Sub
regarding e.cancel
http://msdn2.microsoft.com/en-us/library/safa4957(vs.80).aspx
Private Sub CancelButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CancelButton.Click
BindingSourceName.CancelEd
End Sub
or
DataRow.CancelEdit Method
http://msdn2.microsoft.com/en-US/library/system.data.datarow.canceledit.aspx
Private Sub AcceptOrReject(ByVal row As DataRow)
' Use a function to validate the row's values.
' If the function returns true, end the edit;
' otherwise cancel it.
If ValidateRow(row) Then
row.EndEdit()
Else
row.CancelEdit()
End If
End Sub
Private Function ValidateRow(ByVal row As DataRow) As Boolean
Dim isValid As Boolean
' Insert code to validate the row values.
' Set the isValid variable.
ValidateRow = isValid
End Function
ASKER
Thanks...i see the confusion....while the form is parent/child its not the child records i'm concerned about, it's the parent. Using what you've said so far, here's what i came up with. It seems to do what i want, returning to the offending order. AllI need to do is add checking for the line item detail records, most of which will be caught by the grid events.
'Declarations section of the form.
Dim SalesOrderCurrencyManager As CurrencyManager
'Form Load
SalesOrderCurrencyManager = CType(Me.BindingContext(Me
Private Sub SalesOrderBindingNavigator
Me.Validate()
Dim ctl As Control = Nothing
Dim errMsg As String = String.Empty
If Me.ValidateData(errMsg, ctl) Then
Me.SalesOrderBindingSource
Try
Me.SalesOrderTableAdapter.
Catch ex As Exception
MessageBox.Show(ex.ToStrin
End Try
Else
MessageBox.Show(errMsg, "Invalid Sales Order", MessageBoxButtons.OK, MessageBoxIcon.Stop)
If Not ctl Is Nothing Then
ctl.Focus()
End If
End If
End Sub
Private Function ValidateData(ByRef Msg As String, _
ByRef OffendingControl As Control) As Boolean
Dim r As SalesOrders.SalesOrderRow
ValidateData = True
Msg = String.Empty
For i As Int16 = 0 To Me.SalesOrders.SalesOrder.
r = Me.SalesOrders.SalesOrder.
If r.RowState <> DataRowState.Unchanged Then
If r("StatusID") = "O" Then
Try
If r.HasErrors Then
Me.SalesOrderCurrencyManag
OffendingControl = Nothing
r.CancelEdit()
ValidateData = False
Exit For
End If
If IsDBNull(r("CustomerPO")) = True Then
Msg = "Customer PO cannot be blank."
Me.SalesOrderBindingSource
ValidateData = False
OffendingControl = Me.CustomerPOTextBox
Exit For
End If
If r("CustomerID") = 0 Then
Msg = "Customer cannot be blank."
OffendingControl = Me.cboCustomer
Me.SalesOrderBindingSource
ValidateData = False
OffendingControl = Me.cboEmployee
Exit For
End If
If r("EmployeeID") = 0 Then
Msg = "Employee cannot be blank."
OffendingControl = Me.cboEmployee
Me.SalesOrderBindingSource
ValidateData = False
OffendingControl = Me.cboEmployee
Exit For
End If
If IsDBNull(r("Terms")) Then
Msg = "Terms cannot be blank."
Me.SalesOrderBindingSource
ValidateData = False
OffendingControl = Me.cboTerms
Exit For
End If
If r("Terms") = "Credit Card" Then
If IsDBNull(r("CCName")) Or IsDBNull(r("CCNumber")) _
Or IsDBNull(r("CCDate")) Or IsDBNull(r("CCAddress")) _
Or IsDBNull(r("CCZIP")) Or IsDBNull(r("CCSecCode")) Then
Msg = "Credit info required for ""Credit Card"" terms"
Me.SalesOrderBindingSource
ValidateData = False
OffendingControl = Me.txtCCNumber
Exit For
End If
End If
Catch ex As Exception
Me.SalesOrderCurrencyManag
r.CancelEdit()
MessageBox.Show(ex.ToStrin
ValidateData = False
End Try
End If
End If
Next
End Function
'Declarations section of the form.
Dim SalesOrderCurrencyManager As CurrencyManager
'Form Load
SalesOrderCurrencyManager = CType(Me.BindingContext(Me
Private Sub SalesOrderBindingNavigator
Me.Validate()
Dim ctl As Control = Nothing
Dim errMsg As String = String.Empty
If Me.ValidateData(errMsg, ctl) Then
Me.SalesOrderBindingSource
Try
Me.SalesOrderTableAdapter.
Catch ex As Exception
MessageBox.Show(ex.ToStrin
End Try
Else
MessageBox.Show(errMsg, "Invalid Sales Order", MessageBoxButtons.OK, MessageBoxIcon.Stop)
If Not ctl Is Nothing Then
ctl.Focus()
End If
End If
End Sub
Private Function ValidateData(ByRef Msg As String, _
ByRef OffendingControl As Control) As Boolean
Dim r As SalesOrders.SalesOrderRow
ValidateData = True
Msg = String.Empty
For i As Int16 = 0 To Me.SalesOrders.SalesOrder.
r = Me.SalesOrders.SalesOrder.
If r.RowState <> DataRowState.Unchanged Then
If r("StatusID") = "O" Then
Try
If r.HasErrors Then
Me.SalesOrderCurrencyManag
OffendingControl = Nothing
r.CancelEdit()
ValidateData = False
Exit For
End If
If IsDBNull(r("CustomerPO")) = True Then
Msg = "Customer PO cannot be blank."
Me.SalesOrderBindingSource
ValidateData = False
OffendingControl = Me.CustomerPOTextBox
Exit For
End If
If r("CustomerID") = 0 Then
Msg = "Customer cannot be blank."
OffendingControl = Me.cboCustomer
Me.SalesOrderBindingSource
ValidateData = False
OffendingControl = Me.cboEmployee
Exit For
End If
If r("EmployeeID") = 0 Then
Msg = "Employee cannot be blank."
OffendingControl = Me.cboEmployee
Me.SalesOrderBindingSource
ValidateData = False
OffendingControl = Me.cboEmployee
Exit For
End If
If IsDBNull(r("Terms")) Then
Msg = "Terms cannot be blank."
Me.SalesOrderBindingSource
ValidateData = False
OffendingControl = Me.cboTerms
Exit For
End If
If r("Terms") = "Credit Card" Then
If IsDBNull(r("CCName")) Or IsDBNull(r("CCNumber")) _
Or IsDBNull(r("CCDate")) Or IsDBNull(r("CCAddress")) _
Or IsDBNull(r("CCZIP")) Or IsDBNull(r("CCSecCode")) Then
Msg = "Credit info required for ""Credit Card"" terms"
Me.SalesOrderBindingSource
ValidateData = False
OffendingControl = Me.txtCCNumber
Exit For
End If
End If
Catch ex As Exception
Me.SalesOrderCurrencyManag
r.CancelEdit()
MessageBox.Show(ex.ToStrin
ValidateData = False
End Try
End If
End If
Next
End Function
Hi
and sorry for the delay
< which will be caught by the grid events.
you can always trap the user's input in a datagridview cell by using the grids(cell leave) event.
That gives you the possibility to validate a correct input in the cell.
I dont know if you let the user type in the grid or just use's txt boxes. That is however up to you.
also you are talking about parent/child , i assume you mean that you have setup a table relation in your dataset.
Though my advise is to keep it simple and do validation to all important inputs from a user ! right away when they are entered.
This eliminates a lot of unnessasery checking(coding).
e.g a user types something in a cell - then when trying to leave the cell (tabbing or mouse) then call
a function like this one below (validates if there are 10 digit's present in the cell. if true let the user leave the cell
else promt(msg.box) that the input is not correct.
Public Function IsValidCreditcardno(strIn As String) As Boolean
Dim pattern As String = "^\d{10}$"
Dim expression As New Regex(pattern)
Return expression.IsMatch(strIn)
End Function
vbturbo
and sorry for the delay
< which will be caught by the grid events.
you can always trap the user's input in a datagridview cell by using the grids(cell leave) event.
That gives you the possibility to validate a correct input in the cell.
I dont know if you let the user type in the grid or just use's txt boxes. That is however up to you.
also you are talking about parent/child , i assume you mean that you have setup a table relation in your dataset.
Though my advise is to keep it simple and do validation to all important inputs from a user ! right away when they are entered.
This eliminates a lot of unnessasery checking(coding).
e.g a user types something in a cell - then when trying to leave the cell (tabbing or mouse) then call
a function like this one below (validates if there are 10 digit's present in the cell. if true let the user leave the cell
else promt(msg.box) that the input is not correct.
Public Function IsValidCreditcardno(strIn As String) As Boolean
Dim pattern As String = "^\d{10}$"
Dim expression As New Regex(pattern)
Return expression.IsMatch(strIn)
End Function
vbturbo
ASKER
I'm more concerned about what the user hasn't done than what they have done so using the validating event does me no good since they haven't touched the field. In addition, in certain cases I'm looking for combinations of values, i.e., if a field has a certain value then other fields should be entered or contain certain values. For example, if the order is on a credit card, then I want the credit card info captured, otherwise, I want those fields empty. It seems to me that the only way to accomplish that is to check the contents of the record when the user says they're finished (presses save, next or previous on the binding navigator.)
It seems to me that i can
A. Abandon the binding navigator so that only one record is loaded at a time. That solves the whole problem since all the user can do is save and with one recod positioning the form is not an issue
B. Force each field to be validated when the move or save binding navigator buttons are pressed. This raises the question How do I prevent the MoveNext, Previous, First, or Last from actually occuring (there doesn't appear to be an "e.cancel" associated with those events)?
C. Validate the data in all the records when the user presses save or tries to exit the form. Question here is How do I reposition the form at the offending record?