DataGridViewComboBoxColumn Duplicate Check

[SgtJarrow]

I have a bound DataGridView with a bound ComboBoxColumn in it. This CombobBoxColumn is a list of delivery routes for our company. I want to restrict this ComboBoxColumn to not allow duplicate routes to be selected in more than one row. I am having a tough time in figuring a way to do this.

Driver         Assigned Route
John Smith     101
Jacob Jones    102
Mark Summers   103
Gary Love      103   ***  I don't want this to be able to happen ***
Julias Harvey  104

I am willing to consider any suggestions...be they from a validating standpoint, a datasource change standpoint...anything you might have to offer. it can even be as simple of a message to the user saying the selected route is already assigned.

I have tried a few things, such as using the below check and EditingControlShowing event examples I found with web searches.

Example:

    Private Sub NamesAndNumbersDataGridView_CellValidating(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellValidatingEventArgs) Handles NamesAndNumbersDataGridView.CellValidating

        If e.ColumnIndex = 13 AndAlso Me.NamesAndNumbersDataGridView.CurrentCell.FormattedValue.ToString.Length > 0 Then
            Dim UsedRoutes(999) As Int32
            Dim i As Int32 = -1
            For j As Int32 = 0 To Me.NamesAndNumbersDataGridView.Rows.Count - 1
                i += 1
                If Me.NamesAndNumbersDataGridView.Rows(j).Cells(13).FormattedValue.ToString.Trim.Length > 0 Then
                    UsedRoutes(i) = Convert.ToInt32(Me.NamesAndNumbersDataGridView.Rows(j).Cells(13).FormattedValue.ToString)
                End If
            Next
            If Array.IndexOf(UsedRoutes, Convert.ToInt32(Me.NamesAndNumbersDataGridView.CurrentCell.FormattedValue.ToString)) > 0 Then
                MessageBox.Show("Route " & Me.NamesAndNumbersDataGridView.CurrentCell.FormattedValue.ToString & " is already in use.")
            End If
        End If

    End Sub

Thanks for any input you can offer....

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[jebenson]

Can you show the code you are using to populate the datagridview?

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[SgtJarrow]

It is using the basic Fill from a TableAdapter. The ComboBoxColumn is also filled via a TableAdapter Fill.

Once thing I forgot to mention is that I also have a bunch of people that have no assigned route. This matters because I thought I could use a SQL table constraint but then realized I have many a NULL.

Don't put too much effort into it. I created a work-around by generating a report that find the duplicates and is automatically sent to the users daily. The data is part of a phone-listing application and being one day behind is not critical.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer