Data bound datagridview combobox question regarding display member(s)

I am using VB 2010 and SQL Server 2008 R2 - both are Express versions.

I want to replicate something that I have seen in a number of programs but have not been able to achieve my goal and cannot find anything on the web that has helped me. I want to select an item from a dropdown list in the combobox that has been tied to one of the columns in an SQL Server table. The combobox is part of a data bound datagridview. After making the choice and leaving the combobox cell, I want to display a corresponding item from another column in the same table in that DGV cell. Both items share a common primary key. I hope this makes sense.

One of the programs that deploys this feature is Microsoft's "Money Plus Sunset Deluxe" which is available as a free download from the Softonic web site. I have also seen it in a commercial program written in VS 2010.

I can obviously make a single DisplayMember work for the combobox but am getting increasingly frustrated as whatever I try fails. Any help will be appreciated.

Here is what I have tried so far:

Code:

---


Public Class Form9

    Private Sub TestBindingNavigatorSaveItem_Click(sender As System.Object, e As System.EventArgs) Handles TestBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.TestBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.TestlDataSet)

    End Sub

    Private Sub Form9_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'CategoriesDataSet.Categories' table. You can move, or remove it, as needed.
        Me.CategoriesTableAdapter.Fill(Me.CategoriesDataSet.Categories)
        'TODO: This line of code loads data into the 'TestlDataSet.Test' table. You can move, or remove it, as needed.
        Me.TestTableAdapter.Fill(Me.TestlDataSet.Test)

    End Sub

    Private cboEditingCategory As DataGridViewComboBoxEditingControl = Nothing
    Private txtCategory As DataGridViewComboBoxCell = Nothing

    Private Sub TestDataGridView_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles TestDataGridView.EditingControlShowing

        If TypeOf (e.Control) Is DataGridViewComboBoxEditingControl Then

            'The combobox column is set in the designer to look like this:
            'DataPropertyName: CATID
            'DataSource: CategoriesBindingSource
            'DisplayMember: SortCategory
            'ValueMember: CATID

            If TestDataGridView.CurrentCell.ColumnIndex = 2 Then 'colCategory
                cboEditingCategory = DirectCast(e.Control, DataGridViewComboBoxEditingControl)

                'Change the datasource to use the column from which the user chooses an item
                cboEditingCategory.DataSource = Nothing
                cboEditingCategory.DataSource = CategoriesBindingSource
                cboEditingCategory.DisplayMember = "Category"
                cboEditingCategory.ValueMember = "CATID"

                'Set drop down properties for Category combobox
                cboEditingCategory.DropDownStyle = ComboBoxStyle.DropDown
                cboEditingCategory.AutoCompleteMode = AutoCompleteMode.SuggestAppend
                cboEditingCategory.AutoCompleteSource = AutoCompleteSource.ListItems

                If cboEditingCategory IsNot Nothing Then

                    AddHandler cboEditingCategory.SelectedIndexChanged, AddressOf cboEditingCategory_SelectedIndexChanged

                End If
            End If

        End If

    End Sub

    Private Sub cboEditingCategory_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)

        Try

            Dim dr As DataRow = CategoriesDataSet.Categories.Select("CATID = " & cboEditingCategory.SelectedValue).First

            cboEditingCategory.DisplayMember = "SortCategory"

            TextBox1.Text = dr("SortCategory")

            txtCategory.Value = dr("SortCategory")

        Catch ex As Exception

        End Try

    End Sub

    'Remove the EventHandler and release the controls       
    Private Sub TestDataGridView_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles TestDataGridView.CellEndEdit

        If cboEditingCategory IsNot Nothing Then
            RemoveHandler cboEditingCategory.SelectedIndexChanged, AddressOf cboEditingCategory_SelectedIndexChanged

            cboEditingCategory = Nothing

        End If

    End Sub

End Class

---

If you have a question to ask then don't post in the CodeBank, which the description states is for working code samples. I've asked the mods to move this to the VB.NET forum.

Just let me make sure that I have this right. You have a combo box cell in a DataGridView and you want the user to select something from a drop-down list and then display something else related in that same cell. Is that right?

Thread moved from the 'CodeBank VB.Net' forum (which is for you to post working code examples, not questions) to the 'VB.Net' forum

(thanks for letting us know jmcilhinney :thumb: )

That is correct. The dropdown list will be from one column in the database table and the displayed item will be from another column in the same table. Both items will have the same primary key

My apologies for using the code bank. I got a bit confused as I seldom use this site.

I have only just seen the icon to insert images. Here is a DGV with the database table - first image - showing all the columns. What I want to do is choose from the column "Category" and then display the item with the same primary key from the "SortCategory" as seen in the second DGV image:
Attachment 136163Attachment 136165

The dropdown image was obtained using Microsoft's Print Screen so the quality is a bit poor.

The textbox (Textbox1) at the bottom of the second image displays the text that I want to show in the combobox. You will note that this is provided by the code. I just cannot get that to display in the combobox on leaving the cell the first time round. When the form is loaded and I select an item from the dropdown list, it always displays "Consumables" as it is the first item in the column. If I select the same row and select a sub or child item from the dropdown list, it works as required. If I then move to the next DGV row and select an item from the dropdown list, it displays the previous correct item. This happens with every successive row.

Perhaps this will help to solve the problem that I am experiencing.

Here is some code I use in a project that does something similar,

Code:

---

    Private Sub LOT_CHRGDataGridView_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles LOT_CHRGDataGridView.EditingControlShowing

        Dim cb As ComboBox = TryCast(e.Control, ComboBox)
        If cb IsNot Nothing Then
            Dim editingComboBox As ComboBox = DirectCast(e.Control, ComboBox)
            ' Remove an existing event-handler, if present, to avoid
            ' adding multiple handlers when the editing control is reused.
            RemoveHandler editingComboBox.SelectedIndexChanged, _
                New EventHandler(AddressOf editingComboBox_SelectedIndexChanged)
            ' Add the event handler.
            AddHandler editingComboBox.SelectedIndexChanged, _
                New EventHandler(AddressOf editingComboBox_SelectedIndexChanged)
        End If

    End Sub
    Private Sub editingComboBox_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim dr As DataRow()
        Dim dgvc As DataGridViewCell = TryCast(Me.LOT_CHRGDataGridView.CurrentCell, DataGridViewCell)

        Dim comboBox1 As ComboBox = CType(sender, ComboBox)
        Dim cb As ComboBox = TryCast(comboBox1, ComboBox)
        If cb IsNot Nothing Then
            If dgvc IsNot Nothing Then
                If comboBox1.SelectedValue IsNot Nothing Then
                    dr = Me.MillingDataDataSet.CHARGES.Select("desc='" & comboBox1.SelectedValue.ToString & "'")
                    If dr.GetUpperBound(0) <> -1 Then
                        Me.LOT_CHRGDataGridView.CurrentRow.Cells("code").Value = dr(0)("code").ToString
                        GetChargePoundsAndAmount(dr(0)("code").ToString)
                    End If
                End If
            End If
        End If
    End Sub

---

You should be able to adjust your code to work, your code looks close to what you need. If you can't figure it out, then post your corrent code and explain what's not working right. Don't just say "you can't get it to work"

Thanks for the code. I will give that a try during the day and post a comment.

I have tried the code but even after changing the names of the DGV, columns, etc., I could not make it work so let's start again from the beginning.

I provided the entire form's code in my original post.

The aim is to do the following:


The screen capture - lower image - shows the combobox column displaying the same data as the "Category" column in the upper image. This display format is used to make it easy on the eye for the end user to choose a category. The column consists of a main category which is left justified in the column. Main categories can have sub-categories which are indented by 2 spaces. An example being the main category "Utilities" has 3 sub categories. These are Electricity, Gas and Water. Let us suppose that the end user chooses sub-category "Gas" from main category "Utilities". Upon leaving the cell, the combobox column must now display its corresponding item from the "SortCategory" column in the table i.e. "Utilities:Gas". Note that both items have a common primary key value of 37.

As you have not explained how your database table is constructed, it is difficult to translate the code for my use. Having looked at your code in more detail, are you selecting a description from the database table and then displaying "PoundAndAmount" in another column?

Quote:

---

As you have not explained how your database table is constructed, it is difficult to translate the code for my use. Having looked at your code in more detail, are you selecting a description from the database table and then displaying "PoundAndAmount" in another column?

---

It's actually calculating charges depending on what is selected from the dgv combobox and displays the results in the adjacent columns. What it does isn't really important, what's important is recognizing this is the place where you would lookup the SortCategory information.

Quote:

---

Upon leaving the cell, the combobox column must now display its corresponding item from the "SortCategory" column in the table i.e. "Utilities:Gas"

---

I don't understand this. You want to use the dgv combobox to list the categories, but display the SortCategory?

I realised that you are selecting an item from a drop down list and displaying other relevant information for that primary key in other columns. My requirement is slightly different.

The last sentence in your reply is correct. I want to choose an item using the Category column and display the SortCategory where both have the same primary key. When the user chooses an item, the drop down list comes from the Category database column. After choosing an item, the same combobox must display the corresponding item from the SortCatgory database column.

I know that it can be done as I have seen it in Microsoft's "Money Plus Sunset Deluxe" which is available as a free download from the Softonic web site. This functionality is also deployed in other programs that I have used which look like they were written in VB2 and Visual Studio 2010.

While it's not trivial, it seems to me that the best option here would be to create your own column type that inherits the DataGridViewComboBoxColumn class and for its cell template create your own cell type that inherits the DataGridViewComboBoxCell class. You could then add a property that lets you specify another column/property name, much like DisplayMember and ValueMember. The Displaymember could be used as normal to determine what to display in the editing control but the new property could be used in the GetFormattedValue method to get the value you want instead of using the DisplayMember to get the value to display.

Thanks JMC

As you said, it is no trivial task. I need to learn more about creating a class like this but do not know where to look for tutorials. I have not been able to adapt those that I have found on the web. Do you have any suggestions on where to get suiable tutorials so that I can improve my knowledge

What value do you want (Category or SortCategory) stored in the the record.

I want to choose from Category as dropdown list and store the corresponding item from SortCategory. This way all the records or rows above the one in use will display the text from SortCategory.

Quote:

---

Originally Posted by MikeNewman

I want to choose from Category as dropdown list and store the corresponding item from SortCategory. This way all the records or rows above the one in use will display the text from SortCategory.

---

I understand what you want to display when you choose a dropdown item. That doesn't necessarily mean that's what you want to save in the record. I'll just go on that assumption.

Well, I got to give up for a while, I'm out of time today. Had a couple of ideas but not luck. JMC's idea may be the best but I don't have time to research it fully right now. I did find several links when I Google "create custom datagridviewcolumn" or "datagridviewcomboboxcolumn" or "datagridviewcomboboxcell".

Good luck

This is in C# but it deals with your situaton, https://nickstips.wordpress.com/2010...rop-down-list/

Thanks for all your time. It is appreciated.

I have used Google for months trying to find a lead that might help but without success. When I enter any of the recommended phrases into the Google search bar, I get dozens of recommended links that are now purple showing that the site has been visited.

I have only a smattering of knowledge when it comes to C# but do know that there are very good free converters available on the web. The only thing that I do not grasp in the C# link you provided is that the combobox data appears to be hard coded into a single column. Am I wrong? The line of code that I refer to is "this.clmAnimal.Items.Add(new Animal() { Type = "Dog", Description = "Fury animal that barks" });". Would "Type" and "Description" represent 2 separate columns in a database table?

Quote:

---

Originally Posted by MikeNewman

The line of code that I refer to is "this.clmAnimal.Items.Add(new Animal() { Type = "Dog", Description = "Fury animal that barks" });". Would "Type" and "Description" represent 2 separate columns in a database table?

---

Type and Description are properties of the Animal type. The equivalent VB would be:

vb.net Code:

Me.clmAnimal.Items.Add(New Animal() With { .Type = "Dog", .Description = "Fury animal that barks" })

As you can see, it's almost identical to the C#. That syntax is called an object initialiser and is simply a way to create an object and set its properties on one line. Before type initialisers that would have looked like this:

vb.net Code:

Dim item As New Animal
 
With item
    .Type = "Dog"
    .Description = "Fury animal that barks"
End With
 
Me.clmAnimal.Items.Add(item)

Thanks for the explanation.

This is what it would look like using Visual Basic and a DataTable

This is what I did

Create a class

Code:

---

Public Class Crops
    Private pCrop As String, pCropGroup As String
    Public Property Crop() As String
        Get
            Return pCrop
        End Get
        Set(ByVal value As String)
            pCrop = value
        End Set
    End Property

    Public Property CropGroup() As String
        Get
            Return pCropGroup
        End Get
        Set(ByVal value As String)
            pCropGroup = value
        End Set
    End Property
End Class

---

Add a form, put a datagridview on it
Add a dataset to the form
add a TableAdapter for the DataTable you want to use
(I used a Type Dataset and TableAdapter but you could use an UnTyped Dataset and a Dataadapter)

Code:

---

Public Class Form4

    Private Sub Form4_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Me.CropsTableAdapter1.Fill(Me.WaterDataSet1.Crops)

        Dim clmCrop As DataGridViewComboBoxColumn = DirectCast(Me.DataGridView1.Columns(0), DataGridViewComboBoxColumn)

        For Each row As DataRow In Me.WaterDataSet1.Crops.Rows
            clmCrop.Items.Add(New Crops() With {.Crop = row("crop").ToString, .CropGroup = row("group").ToString})
        Next

        clmCrop.DisplayMember = "Crop"

    End Sub


    Private Sub DataGridView1_EditingControlShowing(sender As Object, e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
        Dim cb As ComboBox = TryCast(e.Control, ComboBox)
        If cb IsNot Nothing Then
            AddHandler cb.DropDown, _
                New EventHandler(AddressOf cb_DropDown)
            AddHandler cb.DropDownClosed, _
                New EventHandler(AddressOf cb_DropDownClosed)
        End If
    End Sub

    Private Sub cb_DropDown(sender As Object, e As System.EventArgs)
        Dim changeList As DataGridViewComboBoxEditingControl = DirectCast(sender, DataGridViewComboBoxEditingControl)
        changeList.DisplayMember = "CropGroup"

    End Sub

    Private Sub cb_DropDownClosed(sender As Object, e As System.EventArgs)
        'Dim changeList As DataGridViewComboBoxColumn = DirectCast(Me.DataGridView1.Columns(0), DataGridViewComboBoxColumn)
        Dim changeList As DataGridViewComboBoxEditingControl = DirectCast(sender, DataGridViewComboBoxEditingControl)
        changeList.DisplayMember = "Crop"
    End Sub
End Class

---

This worked but I didn't play with it for to long.

Thank you. That looks as though it could provide the solution to my problem.

Rather than completely mess up the project that I have been working on for months, I will start with a brand new database and project to use the code that you provided. That way it should be easier to rectify any bugs that I encounter.

One last request. I assume that you have used a relational database. In this database you have a table with columns for Crop and cropgroup besides the primary key. Can you confirm please and, if possible, show the database table structure with a few entries? I am on the wrong side of 70 years of age and pictures work far better than just words.

I have had a busy day and it just after 7pm in the UK I so will attack the problem again in the morning.

My curiosity got the better of me and I decided to press on with it tonight. Result: SUCCESS!!!!!!

I had to edit the DataGridView Category column in the design view to make it a comboboxcolumn but did not do any other configuration there. I left the DataSource and DisplayMember blank and relied on the code to do the work.

Thank you so much. I have been fighting with this for years.

As a follow up, I have been worried about the fact that there are no "RemoveHandler" in the code. I don't know for sure that it will cause a problem. If you want to add them, you can do it like this.

Code:

---

    Private Sub GroupsDataGridView_EditingControlShowing(sender As Object, e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles GroupsDataGridView.EditingControlShowing
        Dim cb As ComboBox = TryCast(e.Control, ComboBox)
        If cb IsNot Nothing Then
            Dim editingComboBox As ComboBox = DirectCast(e.Control, ComboBox)
            ' Remove an existing event-handler, if present, to avoid
            ' adding multiple handlers when the editing control is reused.
            RemoveHandler editingComboBox.SelectedIndexChanged, _
                New EventHandler(AddressOf editingComboBox_SelectedIndexChanged)
            RemoveHandler editingComboBox.DropDown, _
                New EventHandler(AddressOf editingComboBox_DropDown)
            RemoveHandler editingComboBox.DropDownClosed, _
                New EventHandler(AddressOf editingComboBox_DropDownClosed)

            ' Add the event handler.
            AddHandler editingComboBox.SelectedIndexChanged, _
                New EventHandler(AddressOf editingComboBox_SelectedIndexChanged)

            AddHandler editingComboBox.DropDown, _
                New EventHandler(AddressOf editingComboBox_DropDown)
            'AddHandler editingComboBox.SelectionChangeCommitted, _
            '    New EventHandler(AddressOf editingComboBox_SelectionChangeCommitted)
            AddHandler editingComboBox.DropDownClosed, _
                New EventHandler(AddressOf editingComboBox_DropDownClosed)
        End If
    End Sub

---

Good idea. Thank you. I know that multiple handlers can cause problems although that has not happened with the brief tests I have done so far.

Quote:

---

Originally Posted by MikeNewman

Good idea. Thank you. I know that multiple handlers can cause problems although that has not happened with the brief tests I have done so far.

---

I know that it has been over a year since I received help on this subject but I would now like to take it one step further. I have have added an entry called "Split transaction" to the Category database table. This row is automatically included in the list of categories in the Form_Load event handler and is what I need. However, I need this entry to only be placed in the Category column using code from another form. I do not want the end user to have it as a choice in the drop down list. How would I do something similar to a WHERE clause in a dataset to exclude "Split transaction" on DropDown? I cannot figure out if it goes into the Form_Load to create another list for Category or in the Private Sub cb_DropDown. Even if I did know this, my attempts to resolve this problem have all failed. I have tried using a DataView but although that stopped the entry "Split transaction" from showing in the DropDown list, it stopped showing the correct DisplayMember as well.

Some help please before I lose the few strands of hair that are left on my head.

Quote:

---

Originally Posted by MikeNewman

I know that it has been over a year since I received help on this subject but I would now like to take it one step further. I have have added an entry called "Split transaction" to the Category database table. This row is automatically included in the list of categories in the Form_Load event handler and is what I need. However, I need this entry to only be placed in the Category column using code from another form. I do not want the end user to have it as a choice in the drop down list. How would I do something similar to a WHERE clause in a dataset to exclude "Split transaction" on DropDown? I cannot figure out if it goes into the Form_Load to create another list for Category or in the Private Sub cb_DropDown. Even if I did know this, my attempts to resolve this problem have all failed. I have tried using a DataView but although that stopped the entry "Split transaction" from showing in the DropDown list, it stopped showing the correct DisplayMember as well.

Some help please before I lose the few strands of hair that are left on my head.

---

I had a quick look back at previous posts and it appears that you are using a typed DataSet. In that case, you have two choices:

1. Add a new query to your table adapter and call the corresponding method instead of Fill to retrieve the data.
2. Filter the data as you bind it.

I'd probably go with the second option. You should bind your DataTable to your combo box column via a BindingSource and use that to filter the data, e.g.

vb.net Code:

myTableAdapter.Fill(myDataTable)
myBindingSource.Filter = "SomeColumn <> 'SomeValue'"
myBindingSource.DataSource = myDataTable
myComboBoxColumn.DisplayMember = "SomeColumn"
myComboBoxColumn.ValueMember = "SomeOtherColumn"
myComboBoxColumn.DataSource = myBindingSource