We help IT Professionals succeed at work.

Datagridview combo display member

318 Views
Last Modified: 2014-08-09
I'm attempting to fill a combo box in a windows form datagridview. I am having issues with the value member and display member. The field in the datagridview holds an ID number (Request_Type). When I load the combobox values, I expect the datagridview to display the Description (Display member), but the Request_Type ID still shows up. I've been messing with this for a few hours and still can't figure it out. Any ideas?

   Private Sub frmMaintain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Load_Datagrids()
    End Sub

'----------------------------------------------------------------------------------------
 Private Sub Load_Datagrids()
        Dim cn As New SqlConnection(CS)
        Dim cmd As New SqlCommand
        Dim rst1 As SqlDataReader
        Dim strSQL As String

        Try
            cn.Open()

            strSQL = "SELECT TOP (100) Percent Action_Type_ID, Action_Description, Request_Type" & _
                     " FROM tblAction_Type" & _
                     " ORDER BY Action_Description"

            cmd = New SqlCommand(strSQL, cn)
            rst1 = cmd.ExecuteReader()
            dtActions.Clear()
            dtActions.Load(rst1)
            dgvActions.AutoGenerateColumns = False
            rst1.Close()

            'Adjusts the columns dgvActions
            For Each col As DataGridViewColumn In dgvActions.Columns
                Select Case col.Name
                    Case Is = "Request_Type"
                        Dim colCbo As DataGridViewComboBoxColumn
                        colCbo = dgvActions.Columns("Request_Type")
                        colCbo.ValueMember = "Request_Type_ID"
                        colCbo.DisplayMember = "Description"
                        colCbo.DataSource = Get_Combo_Data(1)
			colCbo.DataPropertyName= "Request_Type"
                End Select
            Next col
            dgvActions.DataSource = dtActions
       

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cmd = Nothing
            rst1 = Nothing
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
            cn.Dispose()
        End Try
    End Sub

'----------------------------------------------------------------------------------------
 Private Function Get_Combo_Data(ByVal byIn As Byte) As DataView
        Dim cn As New SqlConnection(CS)
        Dim cmd As New SqlCommand
        Dim rst1 As SqlDataReader
        Dim strSQL As String
        Dim dtTmp As DataTable
        Dim ds As New DataSet
        Dim dv As New DataView


        'This function will get the data required for the combo lookups
        Try
            cn.Open()

            Select Case byIn

                Case Is = 1
                    'Requset Types
                    strSQL = "SELECT TOP (100) Percent Request_Type_ID, Description" & _
                             " FROM tblRequest_Type" & _
                             " ORDER BY Description"
            End Select

            cmd = New SqlCommand(strSQL, cn)
            rst1 = cmd.ExecuteReader()
            dtTmp = New DataTable("Temp") 'Define a new temp datatable
            dtTmp.Load(rst1) 'Load the data into a temp datatable
            ds.Tables.Add(dtTmp)

            dv.Table = ds.Tables("Temp").Copy

            Get_Combo_Data = dv


        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cmd = Nothing
            rst1 = Nothing
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
            cn.Dispose()
            dtTmp = Nothing
            ds = Nothing
        End Try


    End Function
'----------------------------------------------------------------------------------------

Open in new window

Comment
Watch Question

Nothing jumps out at me but it would be easier to follow if you simplified some things.
The whole looping through the columns thing can be eliminated, just assign them directly to the combo control.

Get rid of this:
'Adjusts the columns dgvActions
            For Each col As DataGridViewColumn In dgvActions.Columns
                Select Case col.Name
                    Case Is = "Request_Type"
                        Dim colCbo As DataGridViewComboBoxColumn
                        colCbo = dgvActions.Columns("Request_Type")
                        colCbo.ValueMember = "Request_Type_ID"
                        colCbo.DisplayMember = "Description"
                        colCbo.DataSource = Get_Combo_Data(1)
                  colCbo.DataPropertyName= "Request_Type"
                End Select
            Next col


And just set the properties like this:
with dgvActions.Columns("Request_Type")
.ValueMember = "Request_Type_ID"
.DisplayMember = "Description"
.DataSource = Get_Combo_Data(1)
.DataPropertyName= "Request_Type"
end with
Actually, you should be able to reference the control directly
with Request_Type
.ValueMember = "Request_Type_ID"
.DisplayMember = "Description"
.DataSource = Get_Combo_Data(1)
.DataPropertyName= "Request_Type"
end with

Author

Commented:
The reason for looping the columns is that I do other things to other columns. For the sake of brevity, I removed those items from the code sample I submitted. In any event, I replaced my code with the last version you suggested and it didn't seem to solve my problem - the ID still visibly shows up on the screen, not the display value. When I drop down the combo, the item is actually highlighted that would correspond with the ID - it just displays the ID again when I move the cursor off of the field.
Fair enough, I would still skip the cbocol variable and go straight to the control.

Within the dataset returned from the strsql SQL statement, does "Request_Type" match Request_Type_ID or Description?
 strSQL = "SELECT TOP (100) Percent Action_Type_ID, Action_Description, Request_Type" & _
                     " FROM tblAction_Type" & _
                     " ORDER BY Action_Description"

Author

Commented:
I didn't know you could reference a control on a datagrid without referencing the datagrid as a parent object. I've already made the improved corrections.

Request_Type = Request_Type_ID. Description is the verbal description of the Request_Type_ID.  The fact that Request_Type doesn't have the _ID in tblAction_Type is just a legacy issue that I haven't corrected - they are the same however. Sorry for the confusion.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
That is difficult to believe.  I'm kind of glad to hear it though because I have always resisted using the tinyint although without any real reason. Now I have a reason.

Author

Commented:
I determined that there was a data issue that I corrected myself.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.