robinobrien76 robinobrien76 -4 years ago 75
Vb.net Question

VB.NET: Set combobox text from column in DB, but set value from different column

I am new at this and I could not find the answer in my hours-long search, so...

Can anyone educate me on how I can get a combobox to show text from one column in a database, but pass a different value when selected - VB.NET?

eg - I have a combo box that shows a column of names, and in the same table there is a column of numbers. I want the name to be displayed in the combo box, but a value from a different column with numbers to be passed along to the query when selected.

This populates the combo box:

Private Sub GetTeamMembers()
SQL.ExecQuery("SELECT distinct ACCPIN.CPIN, (CFIRST + CLAST) as TMNAME " & _
" FROM ACCDTL LEFT OUTER JOIN " & _
" ACCPIN ON ACCDTL.CPIN = ACCPIN.CPIN " & _
" Order by TMNAME")

''Add to combo box
If String.IsNullOrEmpty(SQL.Exception) Then
For Each dr As DataRow In SQL.SQLDS.Tables(0).Rows
cbxTeamMembers.Items.Add(dr("CPIN").ToString)
Next
End If

End Sub


So, CPIN is the column that holds the value that I want passed into the next query. But I want to be able to show the column "TMNAME" in the combo box, but pass the value CPIN when I select the text in the combo box.

This runs using the current combobox selection:

Private Sub cbxTeamMembers_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbxTeamMembers.SelectedIndexChanged
GetTMAccess(cbxTeamMembers.Text)
End Sub


...And then:

Private Sub GetTMAccess(ByVal TeamMember As String)



''Run Query for access group pins
SQL.ExecQuery("SELECT ACCPIN.CFIRST AS FIRSTNAME " & _
", ACCPIN.CLAST AS LASTNAME " & _
", ACCPIN.CPIN AS PIN " & _
", ACCPIN.CSHIFT AS SHIFT " & _
", ACCPIN.CRANK AS TMRANK " & _
", ACCDTL.CACCESSID AS ACCESS " & _
"FROM ACCDTL INNER JOIN " & _
"ACCPIN ON ACCDTL.CPINID = ACCPIN.CPIN " & _
"WHERE (ACCDTL.CPIN = '" & TeamMember & "') " & _
" ORDER BY FIRSTNAME")



''Halt & Report on errors
If Not String.IsNullOrEmpty(SQL.Exception) Then MsgBox(SQL.Exception) : Exit Sub

''Fill The Datagridview with the query results
If SQL.RecordCount > 0 Then
dvgTMAccess.DataSource = SQL.SQLDS.Tables(0)

End If
End Sub

Answer Source

You bind the control and set the DisplayMember to the name of the column whose values you want displayed and you set the ValueMember to the name of the column whose values you want returned by the SelectedValue property of the ComboBox. In your case, that presumably means this:

With cbxTeamMembers
    .DisplayMember = "TMNAME"
    .ValueMember = "CPIN"
    .DataSource = SQL.SQLDS.Tables(0)
End With

Note that fact that the DataSource is set last, which is not essential but is advisable. This:

GetTMAccess(cbxTeamMembers.Text)

would then become this:

GetTMAccess(cbxTeamMembers.SelectedValue)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download