Winforms -- datagirdview -- combobox master - combobox detail

Question

In a windows forms, I have a datagridview. On each row of the dataview there is a combobox for the Components. The componet should be used to filter the next column for the sub components. A foreign key relationship has been set up in the database to filter the subcomponts on the componentid. The componentid exits in both the component and subcomponent tables. On loading the datagridview, these erors are appearing:

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. 

 Conversion from string "Failed to enable constraints. On" to type 'Double' is not valid.  The id's are set to integer.

I have not been able to find an example for a datagridview with 2 comboboxes with a master - detail relationship.

The filter works by using the datagridview cell validated event.  If the relationship is set to "Both Relation and Foreign Key Constraint" it the form loads. If the "Relational" or "Foreign Key Constraint Only" are used, the form fails to load.

Any suggestions or links to a solution?

Thank you in advance.

Bill

---

Bill

Answer 1

Hi Bill,
This issue occurs if there are errors with the referential integrity between data tables in a typed data set.  The problem is that although it raises an exception, it is difficult to see what the actual problem was (it could be nullable fields, key violations or data type violations).  
It seem that your error is caused by data type violations, could you please show us your sqlcommand, please make sure that you pass with the correct type of value.
Could you please give the table structure and code fragment, so we can give detail suggestions?
Best Regards,

---

Bob Wu [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Bob,

Thanks for you reply. I have found that I needed to rearrange my code to make it all work.

Here is my review for getting the sub combo box to be filtered off of the main combo box in the datagridview:

Declare global parameters:

 Dim dvSubComponent As New DataView
    Dim filteredSub As New BindingSource
    Dim rRow As Integer
    Dim Loaded As Boolean = False
    Dim dvEstimator As New DataView() '(Me.DsEstimator2.EstimatingSheet)   
    Dim Rowfilter1 As String = ""
    Dim dgcb As New DataGridViewComboBoxCell

In the load event after the support and main tables have been loaded add:

 ' Set filters
        dvSubComponent.Table = Me.DsEstimator2.SubComponent
        filteredSub.DataSource = dvSubComponent

In the sheet  BindingNavigatorSaveItem_Click remove the filter before saving:

Try
            SubComponentBindingSource.RemoveFilter()
            Me.Validate()
            Me.EstimatingSheetBindingSource.EndEdit()
        Catch ex As Exception
            lblMsg.Text = "End edit failed "
        End Try

In the datagridview _CellBeginEdit event:

 Private Sub dgvEstimator2_CellBeginEdit(sender As Object, e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles dgvEstimator2.CellBeginEdit
        Dim rRow As Integer = dgvEstimator2.NewRowIndex

        If e.ColumnIndex = DataGridViewTextBoxColumn4.Index Then

            'Dim dgcb As New DataGridViewComboBoxCell
            dgcb = dgvEstimator2(e.ColumnIndex, e.RowIndex)
            dgcb.DataSource = filteredSub

            ' Set the filter
            If Not IsDBNull(Me.dgvEstimator2(e.ColumnIndex - 1, e.RowIndex).Value) Then

                Dim rID As Integer = Me.dgvEstimator2(e.ColumnIndex - 1, e.RowIndex).Value
                If rID > 0 Then
                    filteredSub.Filter = "ComponentID = " & rID.ToString()
                End If

            End If


        End If

    End Sub

The datagridview_DataError event needs to handle the datagridview errors -- even if it is an empty event.

This is now working for me. The challenge is to know which code to put into which event. Is there a good resource you can refer me to for developing winforms? I have found some strange behaviors such as after diagrammatically inserting default values into a new row in the datagridview -- the user click on the next cell in the row and all the visible values disappear. 

Yes this is leading to another thread. Thanks again for the response.

Bill

 

---

Bill

Answer 3

Hi Bill,
I’m glad to hear that you solved the issue.
I think you might can read the document on MSDN library and watch “How Do I” videos.
Best Regards,

---

Bob Wu [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Bob, 

Thank you for the reference to the documents. There is a wealth of information there. Beth Massi's videos on master - details relationships has been very helpful. She explained very clearly how to wire the forms to the data. It is hard for me to remember where to start and the path to take when running the wires. She makes it very clear and she is easy to follow. 

Bill

---

Bill