Combobox - returning "hidden" value

[Lobster1071]

Fairly new to the VB.NET world, but a long time Access and FoxPro programmer.
I'm trying to do something that was extremely easy in Access, but can't believe it is this difficult in VB.NET.

I populated a combobox with values from a mySQL table. I want to return a hidden value from the combobox (Username), but the values that are being displayed are the full names (Firstname and Lastname). So the user selects the full name, but never sees the Username.

I've searched many different sites, and have seen examples that involve creating classes, and other complicated ways of doing it.

This site explains what I am trying to do, where you select a City Name from the combobox, and it returns the zipcode.
http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/701a38fd-47e2-47a8-81a0-0884e6fbb1c7
However, the ValueMember/SelectedValue doesn't seem to work for me, or I am not understanding how it is used.

Anyone with any help, or even a link showing my how this is done? I am just not finding the right info on my own.

Thank you so very much.

Private Sub FillComboBox()
        Dim sqlQRY As String = "SELECT Lastname, Firstname, Username FROM user"
        'Create connection
        Dim conn As MySqlConnection = New MySqlConnection(Global_ConnectionString)
        Try
            ' Open connection
            conn.Open()

            Dim cmd As MySqlCommand = New MySqlCommand(sqlQRY, conn)

            'create data reader
            Dim rdr As MySqlDataReader = cmd.ExecuteReader

            While (rdr.Read)
                cboUsernames.Items.Add(rdr("Lastname") & ", " & rdr("Firstname"))
                cboUsernames.ValueMember.Equals(rdr("Username"))
            End While

        Catch ex As MySqlException
            MessageBox.Show("Error: " & ex.ToString)
        Finally
            ' Close connection
            conn.Close()
        End Try
    End Sub

    Private Sub cboUsernames_SelectionChangeCommitted(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboUsernames.SelectionChangeCommitted
        TextBox1.Text = cboUsernames.SelectedValue
    End Sub

[codeorder]

Why Not load 2 ArrayLists, 1 with FullName and other w/the Username.
Load your ComboBox from FullName ArrayList and on ComboBox1_SelectedIndexChanged, display the result of the Username Arraylist.

[Lobster1071]

Why Not load 2 ArrayLists, 1 with FullName and other w/the Username. Load your ComboBox from FullName ArrayList and on ComboBox1_SelectedIndexChanged, display the result of the Username Arraylist.

Not 100% sure what you mean by 2 ArrayLists, however, if you mean something like a 2 dimensional array, yes that makes sense. I actually didn't know you could add 2 dimensional arrays to a combobox, but I'm going to play with it.

I did finally find a semi-easy solution for it in the mean time. It was from this site: http://www.codeguru.com/forum/showthread.php?t=216260
it involves creating a class, and it worked fairly well.

Thanks a lot for the response. I'll definitely give your suggestion a try.

[Lobster1071]

Wow, never even thought of that as a solution, but your suggestion definitely works.
I just created a simple application to try it out, and it works quite well.
It's sloppy code, but it was just to test to see that it works. I select the Fullname from the combobox, and it returns the Username to the textbox. Super.

Imports mysql.Data.MySqlClient
Public Class Form1
    Dim values(20, 1) As String

    Dim conn As MySqlConnection
    Dim da As MySqlDataAdapter
    Dim ds As DataSet = New DataSet
    Dim sqlQRY As String
    Dim myCommand As New MySqlCommand

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        FillComboBox()
    End Sub
    Private Sub FillComboBox()

        'Dim sqlQRY As String = "SELECT Username FROM user"
        Dim sqlQRY As String = "SELECT Username, CONCAT(Firstname, ' ' , Lastname) as Fullname FROM user ORDER BY Username ASC"

        'Create connection
        Dim conn As MySqlConnection = New MySqlConnection(Global_ConnectionString)

        Try
            ' Open connection
            conn.Open()

            Dim cmd As MySqlCommand = New MySqlCommand(sqlQRY, conn)

            'create data reader
            Dim rdr As MySqlDataReader = cmd.ExecuteReader

            Dim x As Integer = 0
            While (rdr.Read)
                values(x, 0) = rdr("UserName")
                values(x, 1) = rdr("Fullname")
                x += 1
            End While

            For y As Integer = 0 To x - 1
                cboUsernames.Items.Add(values(y, 1))
            Next
        Catch ex As MySqlException
            MessageBox.Show("Error: " & ex.ToString & vbCrLf)
        Finally
            ' Close connection
            conn.Close()
        End Try
    End Sub

    Private Sub cboUsernames_SelectionChangeCommitted(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboUsernames.SelectionChangeCommitted
        TextBox1.Text = values(cboUsernames.SelectedIndex, 0)
    End Sub
End Class

Thanks again for your suggestion.

[Unhnd_Exception]

You can just fill a data table and set it as the combobox's data source. Then set the DisplayMember to FullName and ValueMember to UserName. Thats about it.

Private Sub ButtonFill_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonFill.Click

    Dim Connection As New SqlServerCe.SqlCeConnection("Yours")
    Dim Command As SqlServerCe.SqlCeCommand = Connection.CreateCommand
    Dim Adapter As New SqlServerCe.SqlCeDataAdapter(Command)
    Dim Table As New DataTable

    'Don't know how MySql will handle the combining of the fields into
    'FullName.  Works on sqlce
    Command.CommandText = "Select UserName, (FirstName + ' ' + LastName) as FullName" & vbCrLf & _
                          "From Users"

    Try
        Connection.Open()
        Adapter.Fill(Table)

    Catch ex As Exception

    Finally
        Connection.Dispose()
        Command.Dispose()
        Adapter.Dispose()
    End Try

    ComboBox1.DataSource = Table
    ComboBox1.DisplayMember = "FullName"
    ComboBox1.ValueMember = "UserName"

End Sub

Private Sub ComboBox1_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectionChangeCommitted
        If ComboBox1.SelectedValue IsNot Nothing Then
            MsgBox(ComboBox1.SelectedValue.ToString)
        End If
    End Sub

[codeorder]

>>Not 100% sure what you mean by 2 ArrayLists.
Simple.

Public Class Form1
    Private arlFullNames, arlUserNames As New ArrayList

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        arlFullNames.Add("some full name")
        arlUserNames.Add("some full name's username")
        arlFullNames.Add("another full name")
        arlUserNames.Add("another full name's username")
        ComboBox1.Items.AddRange(arlFullNames.ToArray)
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        MsgBox(arlUserNames(ComboBox1.SelectedIndex))
    End Sub
End Class

[Lobster1071]

Thanks to the two new suggestions. Nice to learn different ways to do the same thing. I'll give them a try as well.

[raventara]

You can just fill a data table and set it as the combobox's data source. Then set the DisplayMember to FullName and ValueMember to UserName. Thats about it.

This works for the value (ComboBox1.SelectedValue.ToString) but now I can't get the displayed text??
ComboBox1.SelectedText.ToString does not work. Just returns empty...

EDIT:: Never mind... you just have to use the ComboBox1.Text... weird disparity between the two required to use: SelectedValue and Text...