windows forms datagridview compare new row against entire grid

I have a datagridview on my windows form.  I have 2 columns.  
1st column is upc and 2nd column is item description.
if a user puts in a upc, it then goes to the db and gets the item description.
i am now doing some error checking.  so if a user puts in the same upc a second time i want a msgbox to pop up saying duplicate entry.
i am doing this error checking in the   Datagridview1_CellValueChanged (is this the best place?)

here is what i have and it isnt working well at all.

  Private Sub DataGridView1_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged

        If Not e.RowIndex = "-1" Then

            Dim strBuild As New System.Text.StringBuilder()
            Dim strSQLString As String
            Dim sqlcmdcnt As SqlCommand
            Dim drIV00200 As SqlDataReader


                lRowIndex = DataGridView1.CurrentRow.Index
                lRowError = False

                If e.ColumnIndex = 0 Then

                    Me.DataGridView1.Rows(e.RowIndex).ErrorText = String.Empty

                    'If any values entered/scanned on the line
                    If (DataGridView1(0, DataGridView1.CurrentRow.Index).EditedFormattedValue.ToString.Trim <> "") Then

                        'Check for duplicate entries
                        For Each row As DataGridViewRow In DataGridView1.Rows
                            If sender.text.Trim.ToString = sender.EditingControl.text.trim.ToString() Then

                                DataGridView1.Rows(e.RowIndex).ErrorText = "Duplicate entry - " + DataGridView1(0, DataGridView1.CurrentRow.Index).EditedFormattedValue.ToString().Trim
                                Me.DataGridView1.EditingControl.Text = ""
                                '''  e.Cancel = True   ? do i put a remove command here also?

                                Exit Sub
                            End If

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Are you using a DataTable? If so, you can do a RowFilter on it

dTable.DefaultView.RowFilter = "UPC=" & newUPCvalue

If dTable.DefaultView.Count > 0 Then
   'Duplicate value.
End If
purdyra1Author Commented:
Thanks for the reply.  I have figured it out and here is what I did.
I went back to the CellValidating and did my code there.

I check and see if the data coming across is blank or not and then if so,
      e.Cancel = True
      DataGridView1.Rows(e.RowIndex).ErrorText = "This field cannot be empty or press Escape"
I also added:
    ' Clear the row error in case the user presses ESC.  
    Private Sub dataGridView1_CellEndEdit(ByVal sender As Object, _
    ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _
    Handles DataGridView1.CellEndEdit

        dataGridView1.Rows(e.RowIndex).ErrorText = String.Empty

    End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
purdyra1Author Commented:
I tried my best and this seems to work, i don't do much windows forms stuff, mainly
the CellValueChanged don´t has e.Cancel support.

You can use CellValidating event.

    Private Sub dgv_CellValidating(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellValidatingEventArgs) Handles dgv.CellValidating
        If dgv.IsCurrentCellDirty = False Then Return
            nv = DurCpm.Parse(e.FormattedValue)
        Catch ex As Exception
            e.Cancel = True
        End Try

- I use dgv.IsCurrentCellDirty to avoid validate a not changed Cell.
- Use e.FormattedValue to get the user typed value. Cell value is not set yet.
- You can use e.cancel when value is not valid.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.