Datagridview - fill row from Combobox

Question

I have set a combobox to be visible in column1 of my Datagridview. Now I'm trying to fill same row of Datagridview where Combobox appears, from Combobox_Key_Down event. This is my code for showing combobox:

Private Sub My_DGV_CellMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles MY_DGV.CellMouseClick

 If e.RowIndex >= 0 Then

   With My_DGV
     If .Columns(.Rows(e.RowIndex).Cells(e.ColumnIndex).ColumnIndex).Name = "Column1" Then
        .CurrentCell = .Rows(.CurrentRow.Index).Cells(.CurrentCell.ColumnIndex)
        Show_Combobox(.CurrentRow.Index, .CurrentCell.ColumnIndex) 'function that shows my Combobox in that cells
        Combo.Visible = True

     Else
        Combo.Visible = False
     End If
   End With
 End If

End Sub

I tried many things, but I don't know how to determine in which row Combobox appears and how give that Datagridview row my Combobox values. Someone please give me a clue of what should I do. Thanks in advance !

Answer 1

The first problem with your approach is that the DGV can have only one DataSource: it can either show the m:m association table or the related elements. If you include columns from one of the tables into the query for display, the table becomes non updatable and users can be confused why they cannot edit something they can see. It seems of little value they way you describe it, since they cannot see the detail data until after they make a selection.

Next, it requires another datatable to supply the details for CboColB. Since you want the DGV bound to a DataTable easy updates, you end up having to poke data into cells over and over.

Finally, consider what the user is confronted with. Using a Country table (200+ countries/locales with ISO code and name) and a list of flag colors, a table for CountryFlagColors will have hundreds and hundreds of rows (at just 2 colors per flag).

A better display might be to filter the m:m table (flagcolor) to a selected item so the user is only confronted with the data subset they are currently interested in:

The datatable used in the DGV is built from the m:m table:

• The Country column is hidden.
• When they choose from the CBO at the top, that is used as a RowFilter to limit the rows to the relevant ones.
• In the RowValidating event, when the country cell is DBNull, copy the SelectedValue from the country combo to the DGV cell to fill in the blank
  • I would probably really make the user click a button and manually add a row so I could seed the country value then rather than depend on events.
• It uses a DataAdapter and after adding X number of flag definitions, da.Update(dtFlagColors) applies/saves all the changes.

Ok, so that provides the core functionality to assign N color selections to define the flag colors for a country. The missing element is the 'details' for the Color item.

I added a meaningless int and string item to the Color table, one way to display these would be to create an alias in the SQL with the important details. Displaying them as discrete elements can either make the query non updatable or invites the user to edit things they cannot edit here. My silly SQL:

"SELECT Id, Name, Concat(Name , ' (' , intItem , ' ' , stritem,')') As Info from FColor"

Then use 'Info' as the display member on the CBO column in the dgv:

dc = DirectCast(dgvCF.Columns(0), DataGridViewComboBoxColumn)
dc.DataSource = dtFlagColors
dc.DisplayMember = "info"
dc.ValueMember = "id"
dgvCF.DataSource = dtSample

The combo column has its own datasource of course, in order to display one thing and use another for as the Value to give back to you. Result (the values are silly):

It is not exactly what you want, but comes close and is much simpler. It also requires almost no code for driving the associative entity. Another alternative would be to use a DGV as the second picker so you can show the extended data and manually add rows to a DGV:

If you set the dropdown style to Nothing, it looks like a text column.