[RESOLVED] DataGridView ComboBox Columns

[mickey_pt]

Hello

Once again i'm stuck here with the DGV, this time with DataGridViewComboBox Columns.

So i have a datagridview with two combos, when the form it's created i fill the first one with all the MainValues, and the second one with all possible values for the MainValues (called SecondValues) (1..N).
Now my issue, when the user selects one value from the first combo i want to fill the second combo with all values related to the selected MainValue, i can do this for example by filling the datatable again with a parameter that filters the data, but if the previous rows have different main values, the dgv throws errors because it doesn't find the binded ones in the new filtered datatable.

How can i handle this? One datatable for each line?!

Other thing, i'm using the SelectionChangeCommited event for handling the values change (in the EditingControlShowing), but if the user uses the keyboard to select the values the event doesn't behave in the same way or the way that i need.
The values in the combo are numeric values (1001,1020,2010,3010), when the user select one of the values, i need to fill another cell in the same row with the number selected plus some string, example combo:1001 cell:1001XPTO. So if the user presses 1 in the keyboard the combobox selects the 1001 and fills the cell with 1001XPTO, but if i keep pressing the keyboard to select the 1020, by pressing 0 and 2, i get the 1020 selected in the combo, but the cell remains with the 1001XPTO...
I already tried the IndexChange, ValueChange, but these events are called several times in the same keyPress, and some of the calls doesn't have any values and i don't want to check every possible option.

Thanks

[jmcilhinney]

You don;t need to do anything on any event of the first editing control. You might handle the CellValueChanged event and then clear the second combo box cell if its current value is not valid. As for the second combo box, it doesn't exist until the user starts editing a cell in the second column, so you don't need to do anything until then, i.e. when the EditingControlShowing event is raised for that column. At that point AND NOT BEFORE you bind only the appropriate data to only that ComboBox.

[mickey_pt]

Thanks once again jmcilhinney

I don't understand well what i have to do in the filtered combo box...
You say that i need to handle the EditingControlShowing and inside the event i set the datasource/options? I though that the EditingControlShowing event was raised when the cell entered in edit mode, and not when i set the datasource for the dgv...

[jmcilhinney]

Thanks once again jmcilhinney

I don't understand well what i have to do in the filtered combo box...
You say that i need to handle the EditingControlShowing and inside the event i set the datasource/options? I though that the EditingControlShowing event was raised when the cell entered in edit mode, and not when i set the datasource for the dgv...

That's correct. What's the problem here? You need a different data source for each row, right? If you need a different data source for each row then what's the point of setting the DataSource for the column? There's isn't one. You should either set the DataSource of each cell individually or just set the DataSource of the editing control as and when required.

[mickey_pt]

Thanks

I'll try this tomorrow.

[mickey_pt]

Or i'm missing something or this doesn't work for me...

I forget to tell, but the complete DGV has a datasource. So when i create the form, i fill the datasource and bind it to the DGV, the ComboBox Columns will replace two of the original datasource columns.
And like i though the EditingControlShowing only works when the user enters the cell, so when the dgv it's painted/displayed with all the saved values, the event doesn't trigger, if i only have to add new rows i think this could work...

[mickey_pt]

I'm struggling here with the datasource changed event to set the datasource for each line that already exists in the database, but i don't know why the items doesn't show up in the combo, i check in debug and i see that i have items in the datasource, but once the dgv it's displayed the combo it's empty.

I have created a small application just to test this.

'... The vars are all declared 
 Public Sub New()
 
        ' This call is required by the Windows Form Designer.
        InitializeComponent()
 
        ' Add any initialization after the InitializeComponent() call.
        adRegistos = New SqlClient.SqlDataAdapter(sqlRegistos)
        adRegistos.Fill(dt)
 
        'Column
        Dim colPF As New DataGridViewComboBoxColumn
        With colPF
            .Name = "PF2"
            '.DataSource = DataTablePFs Removed
            '.DataPropertyName = "PF"
        End With
        
        DataGridView1.Columns.Add(colPF)
        DataGridView1.DataSource = dt     
    End Sub

Then the rest of code it's in the DataSourceChangeEvent:

Private Sub DataGridView1_DataSourceChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataGridView1.DataSourceChanged
      For Each Row As DataGridViewRow In DataGridView1.Rows
            If Row.IsNewRow Then Exit For
 
            sql = New SqlClient.SqlCommand("dbo.PontoPF_Test", sqlCon)
            sql.CommandType = CommandType.StoredProcedure
            sql.Parameters.Clear()
            sql.Parameters.AddWithValue("@Var1", 9)
            sql.Parameters.AddWithValue("@Var2", Row.Cells("ReferenceCol").Value)
            ad = New SqlClient.SqlDataAdapter(sql)
            ad.Fill(DataTablePFs)
            Dim dgvCB As DataGridViewComboBoxCell = CType(Row.Cells("PF2"), DataGridViewComboBoxCell)
            dgvCB.DisplayMember = "NomePF"
            dgvCB.ValueMember = "idPF"
            dgvCB.DataSource = DataTablePFs.Copy()
           
 
        Next
 
    End Sub

[mickey_pt]

No one?

I have tried a new way.

Fill the two datatables use two bindingsources and set the datasource of each of them to the datatables, then set them as datasource of the columns and declare an auxiliar bindingSource to act as the filter (filterBindingSource), pointing to the table that fills the second combo.
After this in the editingControlShowing event i check if the user is editing the second combo if that's it's true i set the filter property of the filterBindingSource and use it for the current combo datasource, using the value selected in the first comobo as filter.
This almost works, but sometimes when adding a new line, editing some value in the same line, selecting some value in the first combo, the text displayed in the second combo disappears.

Private Sub DataGridViewRegistos_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles DataGridViewRegistos.EditingControlShowing
        If DataGridViewRegistos.CurrentCell.ColumnIndex = DataGridViewRegistos.Rows(DataGridViewRegistos.CurrentRow.Index).Cells("PF2").ColumnIndex Then
            Dim control As DataGridViewComboBoxEditingControl = e.Control
            Dim bs As BindingSource = CType(control.DataSource, BindingSource)
 
            If bs IsNot Nothing Then
                CType(e.Control, ComboBox).DataSource = filterBindingSource
                Dim obra As Object = DataGridViewRegistos.Rows(DataGridViewRegistos.CurrentCell.RowIndex).Cells("OBRA2").Value
 
                If obra Is Nothing OrElse obra Is DBNull.Value Then
                    filterBindingSource.Filter = "obra=-1"
                Else
                    filterBindingSource.Filter = String.Format("obra={0}", obra)
                End If
 
                If DataGridView1.CurrentCell IsNot Nothing AndAlso DataGridView1.CurrentCell.Value IsNot DBNull.Value Then
                    control.SelectedValue = DataGridView1.CurrentCell.Value
                End If
 
            End If
        End If
    End Sub

[mickey_pt]

@JMC

Sample added.
This is one of the ways that i implemented trying to solve this.

[mickey_pt]

No one?

[mickey_pt]

Just to close this thread and post the solution.

I used two dataviews (dvTotal,dvFilter), set the datasource for the combo box column the complete datatable.

When filling the DataTable, i instantiate the dataviews:

dvTotal = New DataView(DataTableName)
dvFilter = New DataView(DataTableName)

Then in the CellBeginEdit event, check if it's the combobox column, if it's the combo then just set the datasource for the current cell:

If YourDataGridView.Columns(e.ColumnIndex).Name = "YourComboColumnName" Then
            Dim valueToFilter As Object = YourDataGridView.Rows(e.RowIndex).Cells("FilterColumn").Value
            Dim cbCell As DataGridViewComboBoxCell = CType(YourDataGridView(e.ColumnIndex, e.RowIndex), DataGridViewComboBoxCell)
            cbCell.DataSource = dvFilter
            If valueToFilter Is Nothing OrElse IsDBNull(valueToFilter ) Then
                dvFilter.RowFilter = "FilterColumn=-1"
            Else
                dvFilter.RowFilter = String.Format("FilterColumn={0}", valueToFilter )
            End If
        End If

Then in the CellEndEdit you need to remove the filter:

If YourDataGridView.Columns(e.ColumnIndex).Name = "CentroCusto" Then
            Dim cbCell As DataGridViewComboBoxCell = CType(YourDataGridView(e.ColumnIndex, e.RowIndex), DataGridViewComboBoxCell)
            cbCell.DataSource = dvTotal 
            dvFilter.RowFilter = String.Empty
        End If