Get DataGridView cell value in nested For Loops

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?

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:

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)":

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.

Email codedump link for Get DataGridView cell value in nested For Loops