VB.net Datagridview Cell Validation on bottom row with Enter key-CodePudding

I have a datagridview and I'm using the Cell Validating event to do something. I've ran into the issue where pressing the Enter key on the bottom row does not fire the Cell Validating event. This appears to due to the focus not changing to a different cell. This can be tested by adding a datagridview to a form, adding some columns and at least 1 row, and then adding a Cell Validating event with a message prompt.

Is there a way to get the Cell Validating event to fire when pressing Enter on the bottom row?

The reason I'm trying to do this is because I have a combobox column which is being modified to allow manual text entry and part of that code uses the Cell Validating event to add the manual entry to the combobox item list. If the manually entered text is not added to the combobox item list it will be cleared. So if you manually type something in the bottom row and hit enter that text will disappear because the Cell Validating event did not fire. The code for allowing manual entry into the datagridview combobox cell is below.

Private Sub DGV_Risk_EditingControlShowing(ByVal sender As Object, ByVal e As DataGridViewEditingControlShowingEventArgs) Handles DGV_Risk.EditingControlShowing
    If (DGV_Risk.CurrentCell.ColumnIndex = 1) Then
        Dim c As ComboBox = TryCast(e.Control, ComboBox)
        If c IsNot Nothing Then c.DropDownStyle = ComboBoxStyle.DropDown
    End If
End Sub

Private Sub DGV_Risk_CellValidating(sender As Object, e As DataGridViewCellValidatingEventArgs) Handles DGV_Risk.CellValidating
    MsgBox("here")
    If (e.ColumnIndex <> 1) Then Exit Sub
    Dim Entry As String = e.FormattedValue
    If (Not User.Items.Contains(Entry)) Then
        User.Items.Add(Entry)
        DGV_Risk.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = Entry
    End If
End Sub

CodePudding user response：

This doesn't directly solve the original question but achieves the end goal I wanted. Using JohnG's suggestion, the datagridview combobox was reverted to the default non-editable state and a separate button to add values to the combobox was added. An input box gets a value from the user and after a simple validation adds the value to the combobox list.

Private Sub Btn_AddNewUser_Click(sender As Object, e As EventArgs) Handles Btn_AddNewUser.Click
    Dim NewUser As String = InputBox("Please enter a new user")
    If (NewUser <> "") Then
        Me.User.Items.Add(NewUser)
    End If
End Sub

CodePudding user response：

The grids combo box cell can be very finicky and will throw the grids DataError when a regular combo box will not. The grids combo box is notorious for throwing the grids DataError claiming that the combo box item in the combo box cell does not “belong” to the combo boxes list of items… and it will “appear” that the item does indeed belong to the combo boxes list of items. See the try it at home at the end of this post.

Your current answer will work, however, there are a couple of things you may want to fix/re-consider before using this approach. One fixable issue is that there is no checking for duplicate items in the combo box list of items nor any checking for superfluous additions like “item1” and “item 1” etc.

In addition, a not so fixable issue is if the “GRID” uses a DataSource. In my tests, using your code, more work is needed to add a new item to the combo boxes list of items. The newly added values were not displayed in the combo boxes in the grid when the grid used a DataSource. While tracing… you can see the newly added items in the combo box columns list of items; however, they were “not” displayed.

I have a guess as to “why” this is; however, I really do not care because 99.999% of the time… “IF” I use a DataGridView… “THEN” I will use a DataSource. And I highly recommend you do the same. This not only reduces the amount of code YOU have to write, but it may open doors to possibly many “built-in-features” that you would normally have to implement… like… sorting and filtering among other useful features. This same DataSource philosophy would also apply to the DataGridViewComboBoxColumn.

Therefore in my example below, I used a DataSource for both the grid and the combo box column. For the grid, the code uses a simple DataTable as a data source as this is a common data container returned from a DB query. The combo box uses a List<ComboItem>… which is a simple List of a custom Class created specifically for the combo box. We could use a DataTable for the combo boxes data source, however for this example I used a list of my custom Class. Some reasons for this…

To help minimize the chances of the grid throwing its DataError because of “seemingly” bad item values… I suggest making a Class “specifically” for the combo box items… even if they are simple strings. A List(Of MyObject) “should” make things work in a more intuitive fashion. The simple example below uses a very simple Class called ComboItem specifically for the grid’s combo box column.

The class contains a single String property and we will have a List(Of ComboItem) to use as a DataSource to the grid’s combo box column. In most cases a unique int ID property would also be used and that would become the combo boxes ValueMember property, however in this case, it is not used and the String property ItemName value will be used as the combo boxes DisplayMember. The only other methods the class needs to implement are the Equals and CompareTo methods to allow the code to use the List’s Contains and Sort methods. This simple class may look something like…

Public Class ComboItem : Implements IComparable

  Public Property ItemName As String

  Public Overrides Function Equals(obj As Object) As Boolean
    Dim that = TryCast(obj, ComboItem)
    If that IsNot Nothing Then
      Return Me.ItemName.Equals(that.ItemName)
    End If
    Return False
  End Function

  Public Function CompareTo(obj As Object) As Integer Implements IComparable.CompareTo
    Dim that = CType(obj, ComboItem)
    Return Me.ItemName.CompareTo(that.ItemName)
  End Function

End Class

Keep in mind… that the above class is basically a “wrapper” for a String object.

The example below uses three global variables… A DataTable called GridTable that is used as a DataSource to the DataGridView. A List(Of ComboItem) called ComboItems and is used as a DataSource to the DatagridViewComboBoxColumn. Finally the DataGridViewComboBoxColumn is exposed globally for convenience as we will update its DataSource when the user adds new items to the ComboItems list.

Dim GridTable As DataTable
Dim ComboItems As List(Of ComboItem)
Dim comboCol As DataGridViewComboBoxColumn

To help test this we will create some test data for the grid. It will be a simple table with three (3) String columns named Col0, Col1 and Col2. Where Col1 is the combo box column. Since the grid is going to use a DataSouce with actual test data… then, we will need to get the test data values from Col1 to add those values to the combo boxes list of items. We MUST do this BEFORE we set the grids DataSource to avoid getting the grids DataError.

Below is a method GetGridData that returns a DataTable with five (5) rows of test data. The Col1 values… “ZZZ”, “Cab”, “Bac” and “AAA” will need to be added to the combo boxes list of items to avoid errors.

Private Function GetGridData() As DataTable
  Dim dt = New DataTable()
  dt.Columns.Add("Col0", GetType(String))
  dt.Columns.Add("Col1", GetType(String))
  dt.Columns.Add("Col2", GetType(String))
  dt.Rows.Add("C0", "ZZZ", "C2")
  dt.Rows.Add("C0", "Cab", "C2")
  dt.Rows.Add("C0", "Bac", "C2")
  dt.Rows.Add("C0", "Cab", "C2")
  dt.Rows.Add("C0", "AAA", "C2")
  dt.Rows.Add("", "", "")
  Return dt
End Function

Next, we need to get the data for the combo box column. If the grid has no data then an empty List(Of ComboItem) will work. Therefore, a GetComboData method is created that takes a DataTable curData and a String colName and returns a List(Of ComboItem). This will return all the sting values in the given data table where the column name equals colName. This list will be used as the data source for the combo box column. After we add the column to the grid, then we can rest easier knowing all the combo box items in the grids data source ARE also items in combo box columns list of items.

Private Function GetComboData(curData As DataTable, colName As String) As List(Of ComboItem)
  Dim items As List(Of ComboItem) = New List(Of ComboItem)
  Dim ci As ComboItem
  For Each row As DataRow In curData.Rows
    ci = New ComboItem()
    ci.ItemName = row(colName).ToString()
    If (Not String.IsNullOrEmpty(ci.ItemName)) Then
      If (Not items.Contains(ci)) Then
        items.Add(ci)
      End If
    End If
  Next
  Return items
End Function

Next, we will (in code) set up the columns in the grid. There will be two DataGridViewTextBoxColumns and one DataGridViewComboBoxColumn. It should be obvious that we need to call the GetComboData method to fill the global variable ComboItems before we call this method. It is fairly straight forward, each column’s DataPropertyName is set to the proper column name in the given data table, and I would point out the combo box column is given a DataSource and its DisplayMember property is set to the ItemName property of our ComboItem class.

Private Sub AddGridColumns()
  Dim txtCol As DataGridViewTextBoxColumn = New DataGridViewTextBoxColumn()
  txtCol.HeaderText = "Col0"
  txtCol.Name = "Col0"
  txtCol.DataPropertyName = "Col0"
  DGV_Risk.Columns.Add(txtCol)
  'combo box column
  comboCol = New DataGridViewComboBoxColumn()
  comboCol.HeaderText = "Col1"
  comboCol.Name = "Col1"
  comboCol.DataPropertyName = "Col1"
  comboCol.DataSource = ComboItems
  comboCol.DisplayMember = "ItemName"
  DGV_Risk.Columns.Add(comboCol)

  txtCol = New DataGridViewTextBoxColumn()
  txtCol.HeaderText = "Col2"
  txtCol.Name = "Col2"
  txtCol.DataPropertyName = "Col2"
  DGV_Risk.Columns.Add(txtCol)
End Sub

If we put all this together in the forms load event… it may look something like below…

Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
  GridTable = GetGridData()
  ComboItems = GetComboData(GridTable, "Col1")
  AddGridColumns()
  DGV_Risk.DataSource = GridTable
End Sub

If we run the current code, the combo boxes should work as expected, however the user can not add items to the combo box columns list of items. For that we will add a button to open a new form to add items. It will have a ListBox, TextBox and a Button. The forms constructor will be given a List(Of ComboItem). When the form loads, the ListBox will be filled with the given list of combo box items. The user can type into the text box to “add” items to the list. When finished the user can click on the close box to go back to the original form.

When the additions are made by the user and the dialog form is closed, the code returns to the previous form and updates the combo box column’s data source to reflect the new items. This basic form may look something like…

Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load
  listBoxCurItems.DataSource = itemsList
  listBoxCurItems.DisplayMember = "ItemName"
End Sub

Dim itemsList As List(Of ComboItem)

Public Sub New(items As List(Of ComboItem))
  InitializeComponent()
  itemsList = items
End Sub

Private Sub btnAddNewComboText_Click(sender As Object, e As EventArgs) Handles btnAddNewComboText.Click
  Dim newValue = TextBox2.Text.Trim()
  Dim ci As ComboItem = New ComboItem
  ci.ItemName = newValue
  If (Not String.IsNullOrEmpty(ci.ItemName)) Then
    If (Not itemsList.Contains(ci)) Then
      itemsList.Add(ci)
      itemsList.Sort()
      listBoxCurItems.DataSource = Nothing
      listBoxCurItems.DataSource = itemsList
      listBoxCurItems.DisplayMember = "ItemName"
    Else
      MessageBox.Show("Duplicate items not allowed")
    End If
  Else
    MessageBox.Show("Item cannot be an empty string")
  End If
End Sub

In this example the form to add new items is called when the user presses a button on the form and would look something like…

Private Sub btnAddNewComboText_Click(sender As Object, e As EventArgs) Handles btnAddNewComboText.Click
  Dim f3 = New Form3(ComboItems)
  F3.ShowDialog()
  comboCol.DataSource = Nothing
  comboCol.DataSource = ComboItems
End Sub

That should do it. As previously noted, this eliminated all the grid event code we previously wrote and the functionality works in an intuitive and user-friendly fashion.

I hope this helps and makes sense.

TRY THIS AT HOME

It is not unreasonable to ask “why” did I use a “wrapper” class when a simple List(Of String) would accomplish the same thing. The String class also implements the Equals and CompareTo interfaces that we needed… so… it does appear unnecessary. However, this try it yourself may demonstrate why the wrapper is used and also demonstrate “why” you may get the invalid item error when the items are “apparently” the same.

So try this… create a new form and use the same code above, then, change the ComboItems variable from a list of ComboItem…

Dim ComboItems As List(Of ComboItem)

To a list of String

Dim ComboItems As List(Of String)

Obviously, you will need to change other parts of the code to use a String instead of a ComboItem. In addition, since we are no longer using a class with properties… we will need to comment out the comboCol.DisplayMember = “ItemName” as the String does not have that property. This will include changes to the form that adds items to the list. The idea is that we want to use a List(Of String) instead of a List(Of ComboItem) as a DataSource for the combo box column.

Once the changes have been made, then run the code. It may/should work as expected after adding items and changing items. However… I can almost guarantee that if you continue to add new items, change the combo boxes to those new items and do basic user testing with the combo boxes… that you will eventually get the grids DataError claiming the item does not belong to the combo boxes list of items. I never got this error when I used a “wrapper” class.