David David - 25 days ago 10
Vb.net Question

Get DataGridView cell value in nested For Loops

I've got some data displayed on a

DataGridView
, and in my
btnSave_Click
event, I'm calling a subroutine to validate the data in the grid before saving it into my database.

The way I'm doing it is by using a loop for each row and inside that loop using another for loop, for each column.

I need to then compare each char in the cell value that it's validating (row dr, cell dc). However, I cannot work out the way to use the row/column co-ordinates to get the value in the cell.

It's a little hard to explain what I mean, but in this code I have the
For Loops
set up on the first 2 lines, then on the third line, notice
If IsDBNull(dc.TextInCell)
- TextInCell is what I need to replace.

In Row; dr and Column; dc, I need to then validate the value stored in that cell...

For Each dr As DataGridViewRow In dgvImport.Rows
For Each dc As DataGridViewColumn In dgvImport.Columns
If dc.HeaderText = "Product Code" Then
If IsDBNull(dc.TextInCell) = True Or dc.TextInCell = Nothing Or dc.TextInCell = "" Then
Me.Cursor = Cursors.Default
MsgBox("Import failed. One or more required fields were not entered", MsgBoxStyle.OkOnly, "Error")
Exit Sub
End If
For Each c As Char In dc.TextInCell
If Not Char.IsLetterOrDigit(c) Then
If Not Char.IsWhiteSpace(c) Then
If c <> "&" AndAlso c <> "-" AndAlso c <> "(" AndAlso c <> ")" Then
Me.Cursor = Cursors.Default
MsgBox("Import failed. One or more cells contains an invalid character", MsgBoxStyle.OkOnly, "Error")
Exit Sub
End If
End If
End If
Next


How do I get the cell value into a variable from here to send it through the validation?

Answer

It will (almost) always be faster to iterate the rows in the datatable than to root thru the control. There is also at least one inefficiency in your code:

For Each dr As DataGridViewRow In dgvImport.Rows
    For Each dc As DataGridViewColumn In dgvImport.Columns
        If dc.HeaderText = "Product Code" Then

You dont need to find the target column for each row - it will be at the same index for each row.

I dont know what the expected pattern is for these, but if there is a defined pattern like "N-LLL-AAA-NLN" (e.g: 9-WDM-6K6-6ZC) you might want to look into RegEx for comprehensive pattern testing. For instance your code is basically just testing for a limited set of special characters anywhere in the string; if there is a ( shouldn't it be before any )?

You will surely need to fiddle with the actual validation code, but this is many times faster:

'... code to fill the DT
' add a column to track if the row is valid
dtSample.Columns.Add(New DataColumn("IsValid", GetType(Boolean)))

Dim specialChars = "&-()"
Dim txt As String = ""
Dim bValid As Boolean
Dim prodIndex As Int32

' index of the target column using the column name
prodIndex = dtSample.Columns.IndexOf("ProductCode")

For Each dr As DataRow In dtSample.Rows
    ' get the text
    txt = dr.Field(Of String)(prodIndex)
    If String.IsNullOrEmpty(txt) = False Then
        bValid = True
        ' each char must be letter, digit or authorized special char
        For n As Int32 = 0 To txt.Length - 1
            If Char.IsLetterOrDigit(txt(n)) = False AndAlso
                        specialChars.Contains(txt(n)) = False Then
                bValid = False
                Exit For
            End If
        Next
        dr("IsValid") = bValid
    End If
Next

dgv1.DataSource = dtSample
' hide our scratch column
dgv1.Columns("IsValid").Visible = False

Results:

enter image description here

Not shown is 2-3 lines in the RowPrePaint event to color the rows where IsValid is false. More importantly, it is fast: 125 milliseconds to process 75,000 rows; digging thru the DGV and finding the same column over and over takes 7-8 secs.

Even without RegEx, you can test for special characters at specific locations (assuming a fixed pattern). For instance, to test "A-78*X(2012)":

bValid = pcode(1) = "-"c AndAlso
         pcode(4) = "*"c AndAlso
         pcode(6) = "("c AndAlso
         pcode(11) = ")"c

You could also split the string by those characters in order to test that parts(3) is a value between 2010 and 2015 or whatever, if you wanted to perform that level of testing. The more of that you do, the more useful RegEX would be.