?
Solved

Validating data, Visual Basic, VS2005

Posted on 2007-07-20
5
Medium Priority
?
507 Views
Last Modified: 2010-04-23
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.)
0
Comment
Question by:ProSol-Inc
  • 3
  • 2
5 Comments
 
LVL 18

Accepted Solution

by:
vbturbo earned 1500 total points
ID: 19538228
Hi

Well there are serveral approaches to what you are asking , also you are asking a lot of questions (not just one)

here is a link (hard coding) to moving  forward, prevous...ect.

http://www.homeandlearn.co.uk/NET/nets12p9.html

However you'll benefit a lot if you study tutorial from the beginning.

another approach is the use of the CurrencyManager Class ,that way you can better control what you want to represent to your user.
http://msdn2.microsoft.com/en-us/library/system.windows.forms.currencymanager.aspx

===========
RowChanging is one way to do checks on the data you want to validate.
http://msdn2.microsoft.com/en-us/library/w9y9a401.aspx

And you can also use the DataRow.HasErrors Property
http://msdn2.microsoft.com/en-us/library/system.data.datarow.haserrors.aspx

But in my own humble opinion i do validation (regexp) on the textbox'es before i let a user move to a next or prev
record.

hope this helps a bit

vbturbo




0
 

Author Comment

by:ProSol-Inc
ID: 19540544
All good examples, thank you. Couple of comments:

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?

0
 
LVL 18

Expert Comment

by:vbturbo
ID: 19540703
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) Then
                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.RowError)
       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.CancelEdit()
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
0
 

Author Comment

by:ProSol-Inc
ID: 19541401
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.SalesOrders.SalesOrder), CurrencyManager)

    Private Sub SalesOrderBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SalesOrderBindingNavigatorSaveItem.Click

        Me.Validate()

        Dim ctl As Control = Nothing
        Dim errMsg As String = String.Empty

        If Me.ValidateData(errMsg, ctl) Then
            Me.SalesOrderBindingSource.EndEdit()
            Try
                Me.SalesOrderTableAdapter.Update(Me.SalesOrders.SalesOrder)
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            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.Rows.Count - 1

            r = Me.SalesOrders.SalesOrder.Rows(i)

            If r.RowState <> DataRowState.Unchanged Then

                If r("StatusID") = "O" Then
                    Try

                        If r.HasErrors Then
                            Me.SalesOrderCurrencyManager.Position = i + 1
                            OffendingControl = Nothing
                            r.CancelEdit()
                            ValidateData = False
                            Exit For
                        End If
                        If IsDBNull(r("CustomerPO")) = True Then
                            Msg = "Customer PO cannot be blank."
                            Me.SalesOrderBindingSource.Position = i + 1
                            ValidateData = False
                            OffendingControl = Me.CustomerPOTextBox
                            Exit For
                        End If
                        If r("CustomerID") = 0 Then
                            Msg = "Customer cannot be blank."
                            OffendingControl = Me.cboCustomer
                            Me.SalesOrderBindingSource.Position = i + 1
                            ValidateData = False
                            OffendingControl = Me.cboEmployee
                            Exit For
                        End If
                        If r("EmployeeID") = 0 Then
                            Msg = "Employee cannot be blank."
                            OffendingControl = Me.cboEmployee
                            Me.SalesOrderBindingSource.Position = i + 1
                            ValidateData = False
                            OffendingControl = Me.cboEmployee
                            Exit For
                        End If
                        If IsDBNull(r("Terms")) Then
                            Msg = "Terms cannot be blank."
                            Me.SalesOrderBindingSource.Position = i + 1
                            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.Position = i + 1
                                ValidateData = False
                                OffendingControl = Me.txtCCNumber
                                Exit For
                            End If

                        End If
                    Catch ex As Exception
                        Me.SalesOrderCurrencyManager.Position = i + 1
                        r.CancelEdit()
                        MessageBox.Show(ex.ToString)
                        ValidateData = False
                    End Try

                End If

            End If

        Next

    End Function
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 19543628
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

 
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question