-
Mar 15th, 2023, 07:38 AM
#1
Thread Starter
Frenzied Member
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.
-
Mar 15th, 2023, 08:02 AM
#2
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.
-
Mar 15th, 2023, 08:07 AM
#3
Thread Starter
Frenzied Member
Re: DataGridViewComboBoxCell value is not valid
Thanks John, I will go down that path
-
Mar 17th, 2023, 12:49 AM
#4
Thread Starter
Frenzied Member
Re: DataGridViewComboBoxCell value is not valid
Originally Posted by jmcilhinney
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...
-
Mar 17th, 2023, 02:54 AM
#5
Re: DataGridViewComboBoxCell value is not valid
Consider it taken. This seems to work:
vb.net Code:
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim data = GetData()
BindData(data)
End Sub
Private Function GetData() As DataSet
Dim data As New DataSet
Dim grandparentTable = data.Tables.Add("Grandparent")
With grandparentTable.Columns
.Add("GrandparentId", GetType(Integer)).AutoIncrement = True
.Add("ParentId", GetType(Integer))
.Add("ChildId", GetType(Integer))
End With
Dim parentTable = data.Tables.Add("Parent")
With parentTable.Columns
.Add("ParentId", GetType(Integer))
.Add("ParentName", GetType(String))
End With
With parentTable.Rows
.Add(1, "Parent 1")
.Add(2, "Parent 2")
.Add(3, "Parent 3")
End With
Dim childTable = data.Tables.Add("Child")
With childTable.Columns
.Add("ChildId", GetType(Integer))
.Add("ParentId", GetType(Integer))
.Add("ChildName", GetType(String))
End With
With childTable.Rows
.Add(1, 1, "Child 1.1")
.Add(2, 1, "Child 1.2")
.Add(3, 1, "Child 1.3")
.Add(4, 2, "Child 2.1")
.Add(5, 2, "Child 2.2")
.Add(6, 2, "Child 2.3")
.Add(7, 3, "Child 3.1")
.Add(8, 3, "Child 3.2")
.Add(9, 3, "Child 3.3")
End With
Return data
End Function
Private Sub BindData(data As DataSet)
filteredChildBindingSource.DataSource = New DataView(data.Tables("Child"))
childBindingSource.DataSource = data.Tables("Child")
With childColumn
.DisplayMember = "ChildName"
.ValueMember = "ChildId"
.DataSource = childBindingSource
End With
parentBindingSource.DataSource = data.Tables("Parent")
With parentColumn
.DisplayMember = "ParentName"
.ValueMember = "ParentId"
.DataSource = parentBindingSource
End With
gridBindingSource.DataSource = data.Tables("Grandparent")
DataGridView1.DataSource = gridBindingSource
End Sub
Private Sub DataGridView1_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged
If e.ColumnIndex = 1 AndAlso e.RowIndex >= 0 Then
DataGridView1.Rows(e.RowIndex).Cells(2).Value = DBNull.Value
End If
End Sub
Private Sub DataGridView1_EditingControlShowing(sender As Object, e As DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
Dim cellAddress = DataGridView1.CurrentCellAddress
Dim columnIndex = cellAddress.X
If columnIndex = 2 Then
Dim parentIdValue = DataGridView1.CurrentRow.Cells(1).Value
If parentIdValue IsNot DBNull.Value Then
filteredChildBindingSource.Filter = "ParentId = " & CInt(parentIdValue)
With DirectCast(e.Control, ComboBox)
.DisplayMember = "ChildName"
.ValueMember = "ChildId"
.DataSource = filteredChildBindingSource
End With
End If
End If
End Sub
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:
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class Form1
Inherits System.Windows.Forms.Form
'Form overrides dispose to clean up the component list.
<System.Diagnostics.DebuggerNonUserCode()> _
Protected Overrides Sub Dispose(ByVal disposing As Boolean)
Try
If disposing AndAlso components IsNot Nothing Then
components.Dispose()
End If
Finally
MyBase.Dispose(disposing)
End Try
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
<System.Diagnostics.DebuggerStepThrough()> _
Private Sub InitializeComponent()
Me.components = New System.ComponentModel.Container()
Me.DataGridView1 = New System.Windows.Forms.DataGridView()
Me.gridBindingSource = New System.Windows.Forms.BindingSource(Me.components)
Me.parentBindingSource = New System.Windows.Forms.BindingSource(Me.components)
Me.childBindingSource = New System.Windows.Forms.BindingSource(Me.components)
Me.filteredChildBindingSource = New System.Windows.Forms.BindingSource(Me.components)
Me.idColumn = New System.Windows.Forms.DataGridViewTextBoxColumn()
Me.parentColumn = New System.Windows.Forms.DataGridViewComboBoxColumn()
Me.childColumn = New System.Windows.Forms.DataGridViewComboBoxColumn()
CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).BeginInit()
CType(Me.gridBindingSource, System.ComponentModel.ISupportInitialize).BeginInit()
CType(Me.parentBindingSource, System.ComponentModel.ISupportInitialize).BeginInit()
CType(Me.childBindingSource, System.ComponentModel.ISupportInitialize).BeginInit()
CType(Me.filteredChildBindingSource, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'DataGridView1
'
Me.DataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
Me.DataGridView1.Columns.AddRange(New System.Windows.Forms.DataGridViewColumn() {Me.idColumn, Me.parentColumn, Me.childColumn})
Me.DataGridView1.Location = New System.Drawing.Point(12, 12)
Me.DataGridView1.Name = "DataGridView1"
Me.DataGridView1.Size = New System.Drawing.Size(776, 426)
Me.DataGridView1.TabIndex = 0
'
'idColumn
'
Me.idColumn.DataPropertyName = "GrandparentId"
Me.idColumn.HeaderText = "ID"
Me.idColumn.Name = "idColumn"
'
'parentColumn
'
Me.parentColumn.DataPropertyName = "ParentId"
Me.parentColumn.HeaderText = "Parent"
Me.parentColumn.Name = "parentColumn"
'
'childColumn
'
Me.childColumn.DataPropertyName = "ChildId"
Me.childColumn.HeaderText = "Child"
Me.childColumn.Name = "childColumn"
'
'Form1
'
Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
Me.ClientSize = New System.Drawing.Size(800, 450)
Me.Controls.Add(Me.DataGridView1)
Me.Name = "Form1"
Me.Text = "Form1"
CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).EndInit()
CType(Me.gridBindingSource, System.ComponentModel.ISupportInitialize).EndInit()
CType(Me.parentBindingSource, System.ComponentModel.ISupportInitialize).EndInit()
CType(Me.childBindingSource, System.ComponentModel.ISupportInitialize).EndInit()
CType(Me.filteredChildBindingSource, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
Friend WithEvents DataGridView1 As DataGridView
Friend WithEvents gridBindingSource As BindingSource
Friend WithEvents parentBindingSource As BindingSource
Friend WithEvents childBindingSource As BindingSource
Friend WithEvents filteredChildBindingSource As BindingSource
Friend WithEvents idColumn As DataGridViewTextBoxColumn
Friend WithEvents parentColumn As DataGridViewComboBoxColumn
Friend WithEvents childColumn As DataGridViewComboBoxColumn
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.
Last edited by jmcilhinney; Mar 17th, 2023 at 02:58 AM.
-
Mar 17th, 2023, 06:17 AM
#6
Thread Starter
Frenzied Member
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.
-
Mar 17th, 2023, 07:45 AM
#7
Re: DataGridViewComboBoxCell value is not valid
Originally Posted by schoemr
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|