Data bound datagridview combobox question regarding display member(s)

[MikeNewman]

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:

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