Results 1 to 7 of 7

Thread: DataGridViewComboBoxCell value is not valid

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    DataGridViewComboBoxCell value is not valid

    Hi,

    I had this problem in the past, but after searching high and low I can't find the solution. It was a long time ago and now I am stuck again.

    I have in the sql database two related tables. For simplicity let's just say Continents and Countries. One continent can have many countries.

    In the winform I have a datagridview with two columns which I have setup as DataGridViewComboBoxColumn for each one. For each of the 2 combo's I specified the respective "Data Source" and "Display member"

    All of this works fine. If I select a Continent from the 1st DataGridViewComboBoxColumn then the 2nd DataGridViewComboBoxColumn populates withe associated countries.

    BUT.... here is the problem. I will try to explain it as best as I can:

    If I decide to change the selected Continent, then at that moment in time when the Continent is changed the Countries in the 2nd DataGridViewComboBoxColumn is in 'conflict.' because it is still populated with the items from the (now) previous selection. Then "DataGridViewComboBoxCell value is not valid" exception is thrown.

    How can I update the Country items based on the new Continent selection?
    Last edited by schoemr; Mar 15th, 2023 at 09:12 AM.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: DataGridViewComboBoxCell value is not valid

    This is a tricky situation that I've seen a number of people come up against. I've never implemented it myself but I have theorised about a solution. Here's what I recommend. Bind the entire list of countries to the corresponding column, so no selection can be invalid. When the user selects a new continent, clear the selection in the corresponding country cell. When the user goes to edit the country cell, bind a filtered list of countries to the editing control specifically. That way, the user will only see valid selections for the current continent in the drop-down list. That's it. Basically, the full list is bound to the column and thus the cells, but the filtered list is bound to the editing control as and when needed. If this is too confusing, let me know and I'll put a demo together. It's probably about time I actually implemented it myself to confirm it works as I assume.

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: DataGridViewComboBoxCell value is not valid

    Thanks John, I will go down that path

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: DataGridViewComboBoxCell value is not valid

    Quote Originally Posted by jmcilhinney View Post
    This is a tricky situation that I've seen a number of people come up against. I've never implemented it myself but I have theorised about a solution. Here's what I recommend. Bind the entire list of countries to the corresponding column, so no selection can be invalid. When the user selects a new continent, clear the selection in the corresponding country cell. When the user goes to edit the country cell, bind a filtered list of countries to the editing control specifically. That way, the user will only see valid selections for the current continent in the drop-down list. That's it. Basically, the full list is bound to the column and thus the cells, but the filtered list is bound to the editing control as and when needed. If this is too confusing, let me know and I'll put a demo together. It's probably about time I actually implemented it myself to confirm it works as I assume.
    You will have to take one for the team here, JMC...

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: DataGridViewComboBoxCell value is not valid

    Consider it taken. This seems to work:
    vb.net Code:
    1. Public Class Form1
    2.  
    3.     Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    4.         Dim data = GetData()
    5.  
    6.         BindData(data)
    7.     End Sub
    8.  
    9.     Private Function GetData() As DataSet
    10.         Dim data As New DataSet
    11.         Dim grandparentTable = data.Tables.Add("Grandparent")
    12.  
    13.         With grandparentTable.Columns
    14.             .Add("GrandparentId", GetType(Integer)).AutoIncrement = True
    15.             .Add("ParentId", GetType(Integer))
    16.             .Add("ChildId", GetType(Integer))
    17.         End With
    18.  
    19.         Dim parentTable = data.Tables.Add("Parent")
    20.  
    21.         With parentTable.Columns
    22.             .Add("ParentId", GetType(Integer))
    23.             .Add("ParentName", GetType(String))
    24.         End With
    25.  
    26.         With parentTable.Rows
    27.             .Add(1, "Parent 1")
    28.             .Add(2, "Parent 2")
    29.             .Add(3, "Parent 3")
    30.         End With
    31.  
    32.         Dim childTable = data.Tables.Add("Child")
    33.  
    34.         With childTable.Columns
    35.             .Add("ChildId", GetType(Integer))
    36.             .Add("ParentId", GetType(Integer))
    37.             .Add("ChildName", GetType(String))
    38.         End With
    39.  
    40.         With childTable.Rows
    41.             .Add(1, 1, "Child 1.1")
    42.             .Add(2, 1, "Child 1.2")
    43.             .Add(3, 1, "Child 1.3")
    44.             .Add(4, 2, "Child 2.1")
    45.             .Add(5, 2, "Child 2.2")
    46.             .Add(6, 2, "Child 2.3")
    47.             .Add(7, 3, "Child 3.1")
    48.             .Add(8, 3, "Child 3.2")
    49.             .Add(9, 3, "Child 3.3")
    50.         End With
    51.  
    52.         Return data
    53.     End Function
    54.  
    55.     Private Sub BindData(data As DataSet)
    56.         filteredChildBindingSource.DataSource = New DataView(data.Tables("Child"))
    57.         childBindingSource.DataSource = data.Tables("Child")
    58.  
    59.         With childColumn
    60.             .DisplayMember = "ChildName"
    61.             .ValueMember = "ChildId"
    62.             .DataSource = childBindingSource
    63.         End With
    64.  
    65.         parentBindingSource.DataSource = data.Tables("Parent")
    66.  
    67.         With parentColumn
    68.             .DisplayMember = "ParentName"
    69.             .ValueMember = "ParentId"
    70.             .DataSource = parentBindingSource
    71.         End With
    72.  
    73.         gridBindingSource.DataSource = data.Tables("Grandparent")
    74.         DataGridView1.DataSource = gridBindingSource
    75.     End Sub
    76.  
    77.     Private Sub DataGridView1_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged
    78.         If e.ColumnIndex = 1 AndAlso e.RowIndex >= 0 Then
    79.             DataGridView1.Rows(e.RowIndex).Cells(2).Value = DBNull.Value
    80.         End If
    81.     End Sub
    82.  
    83.     Private Sub DataGridView1_EditingControlShowing(sender As Object, e As DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
    84.         Dim cellAddress = DataGridView1.CurrentCellAddress
    85.         Dim columnIndex = cellAddress.X
    86.  
    87.         If columnIndex = 2 Then
    88.             Dim parentIdValue = DataGridView1.CurrentRow.Cells(1).Value
    89.  
    90.             If parentIdValue IsNot DBNull.Value Then
    91.                 filteredChildBindingSource.Filter = "ParentId = " & CInt(parentIdValue)
    92.                 With DirectCast(e.Control, ComboBox)
    93.                     .DisplayMember = "ChildName"
    94.                     .ValueMember = "ChildId"
    95.                     .DataSource = filteredChildBindingSource
    96.                 End With
    97.             End If
    98.         End If
    99.     End Sub
    100.  
    101. End Class
    Here's the designer code for the form, so you can just paste that into the designer code file instead of building the form yourself.
    vb.net Code:
    1. <Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
    2. Partial Class Form1
    3.     Inherits System.Windows.Forms.Form
    4.  
    5.     'Form overrides dispose to clean up the component list.
    6.     <System.Diagnostics.DebuggerNonUserCode()> _
    7.     Protected Overrides Sub Dispose(ByVal disposing As Boolean)
    8.         Try
    9.             If disposing AndAlso components IsNot Nothing Then
    10.                 components.Dispose()
    11.             End If
    12.         Finally
    13.             MyBase.Dispose(disposing)
    14.         End Try
    15.     End Sub
    16.  
    17.     'Required by the Windows Form Designer
    18.     Private components As System.ComponentModel.IContainer
    19.  
    20.     'NOTE: The following procedure is required by the Windows Form Designer
    21.     'It can be modified using the Windows Form Designer.  
    22.     'Do not modify it using the code editor.
    23.     <System.Diagnostics.DebuggerStepThrough()> _
    24.     Private Sub InitializeComponent()
    25.         Me.components = New System.ComponentModel.Container()
    26.         Me.DataGridView1 = New System.Windows.Forms.DataGridView()
    27.         Me.gridBindingSource = New System.Windows.Forms.BindingSource(Me.components)
    28.         Me.parentBindingSource = New System.Windows.Forms.BindingSource(Me.components)
    29.         Me.childBindingSource = New System.Windows.Forms.BindingSource(Me.components)
    30.         Me.filteredChildBindingSource = New System.Windows.Forms.BindingSource(Me.components)
    31.         Me.idColumn = New System.Windows.Forms.DataGridViewTextBoxColumn()
    32.         Me.parentColumn = New System.Windows.Forms.DataGridViewComboBoxColumn()
    33.         Me.childColumn = New System.Windows.Forms.DataGridViewComboBoxColumn()
    34.         CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).BeginInit()
    35.         CType(Me.gridBindingSource, System.ComponentModel.ISupportInitialize).BeginInit()
    36.         CType(Me.parentBindingSource, System.ComponentModel.ISupportInitialize).BeginInit()
    37.         CType(Me.childBindingSource, System.ComponentModel.ISupportInitialize).BeginInit()
    38.         CType(Me.filteredChildBindingSource, System.ComponentModel.ISupportInitialize).BeginInit()
    39.         Me.SuspendLayout()
    40.         '
    41.         'DataGridView1
    42.         '
    43.         Me.DataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
    44.         Me.DataGridView1.Columns.AddRange(New System.Windows.Forms.DataGridViewColumn() {Me.idColumn, Me.parentColumn, Me.childColumn})
    45.         Me.DataGridView1.Location = New System.Drawing.Point(12, 12)
    46.         Me.DataGridView1.Name = "DataGridView1"
    47.         Me.DataGridView1.Size = New System.Drawing.Size(776, 426)
    48.         Me.DataGridView1.TabIndex = 0
    49.         '
    50.         'idColumn
    51.         '
    52.         Me.idColumn.DataPropertyName = "GrandparentId"
    53.         Me.idColumn.HeaderText = "ID"
    54.         Me.idColumn.Name = "idColumn"
    55.         '
    56.         'parentColumn
    57.         '
    58.         Me.parentColumn.DataPropertyName = "ParentId"
    59.         Me.parentColumn.HeaderText = "Parent"
    60.         Me.parentColumn.Name = "parentColumn"
    61.         '
    62.         'childColumn
    63.         '
    64.         Me.childColumn.DataPropertyName = "ChildId"
    65.         Me.childColumn.HeaderText = "Child"
    66.         Me.childColumn.Name = "childColumn"
    67.         '
    68.         'Form1
    69.         '
    70.         Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
    71.         Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
    72.         Me.ClientSize = New System.Drawing.Size(800, 450)
    73.         Me.Controls.Add(Me.DataGridView1)
    74.         Me.Name = "Form1"
    75.         Me.Text = "Form1"
    76.         CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).EndInit()
    77.         CType(Me.gridBindingSource, System.ComponentModel.ISupportInitialize).EndInit()
    78.         CType(Me.parentBindingSource, System.ComponentModel.ISupportInitialize).EndInit()
    79.         CType(Me.childBindingSource, System.ComponentModel.ISupportInitialize).EndInit()
    80.         CType(Me.filteredChildBindingSource, System.ComponentModel.ISupportInitialize).EndInit()
    81.         Me.ResumeLayout(False)
    82.  
    83.     End Sub
    84.  
    85.     Friend WithEvents DataGridView1 As DataGridView
    86.     Friend WithEvents gridBindingSource As BindingSource
    87.     Friend WithEvents parentBindingSource As BindingSource
    88.     Friend WithEvents childBindingSource As BindingSource
    89.     Friend WithEvents filteredChildBindingSource As BindingSource
    90.     Friend WithEvents idColumn As DataGridViewTextBoxColumn
    91.     Friend WithEvents parentColumn As DataGridViewComboBoxColumn
    92.     Friend WithEvents childColumn As DataGridViewComboBoxColumn
    93. End Class
    So the DataSet contains a Grandparent table that is bound to the grid and Parent and Child tables that are bound to the combo box columns. There's an extra BindingSource that is bound to a DataView create for the Child table and that gets filtered and bound to the editing control for the Child column.

  6. #6

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: DataGridViewComboBoxCell value is not valid

    Thanks for your reply John...

    I will check if I can translate what you did here over to my setup. The problem is (I suspect) once there is a foreign key constraint in the database - it just don't work because of the one-to-may relationship of the tables.

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: DataGridViewComboBoxCell value is not valid

    Quote Originally Posted by schoemr View Post
    The problem is (I suspect) once there is a foreign key constraint in the database - it just don't work because of the one-to-may relationship of the tables.
    That won't be a problem. I haven't created a foreign key explicitly in the DataSet in my example but there's still a 1:many relation between the Parent and Child tables because every Child record has a ParentId value.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width