asked on

Datagridview combo display member

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

UnifiedIS

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

UnifiedIS

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

pdi656

ASKER

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.

UnifiedIS

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"

pdi656

ASKER

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.

ASKER CERTIFIED SOLUTION

pdi656

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

UnifiedIS

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.

pdi656

ASKER

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