Listbox ValueMember

bellatriks

Hi, I'm writing a project in VisuaStudio 2012 Windows From Application. I get data from SQL Server into Listbox Control. I fill Listbox using DataAdapter and DataTable. My data has "ID" field in SQL Server. For example, ID=1 Text=John ID=2 Text=Richard ID=3 Text=Mel ... I don't want to see ID field in Listbox.

        cmdString = "SELECT \* FROM MyTable ORDER BY ID"
        Command = New SqlCommand(cmdString, Connection)
        DataAdp = New SqlDataAdapter(Command)
        Dim dataset = New DataSet()
        Dim rdr As SqlDataReader
        DataAdp.Fill(dataset)
        dt = New DataTable
        dt = dataset.Tables(0)
        rdr = Command.ExecuteReader

        For Each temDataRow As DataRow In dt.Rows
            ListBox3.Items.Add(temDataRow("Text"))
            ListBox3.ValueMember = temDataRow("ID")
            ListBox3.DisplayMember = temDataRow("Text")
        Next

I want to get ID in Listbox DoubleClick Event or when I get data from SQL Server. How can I do this? I tried DisplayMember and ValueMember value but I could not do it.

Thanks Emrah

Richard Deeming

Your code is quite confused - you're calling Command.ExecuteReader after you've already used a DataAdapter to execute the command; you're setting the ValueMember and DisplayMember to the value read from the DataTable rather than the name of the column; and you're adding each item as a string. You need to set the ValueMember to the name of the column which contains the value; the DisplayMember to the name of the column which you want to display; and the DataSource to the DataTable which contains the values you want to display. There's no need to add items to the list manually.

Dim dataset As New DataSet()

Using command As New SqlCommand("SELECT ID, Text FROM MyTable ORDER BY ID", Connection)
Dim adapter As New SqlDataAdapter(command)
adapter.Fill(dataset)
End Using

ListBox3.ValueMember = "ID"
ListBox3.DisplayMember = "Text"
ListBox3.DataSource = dataset.Tables(0)

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

bellatriks

Hi Richard, First of all thanks your answer. I used the code for your send but I can't still get the value of ID field in Listbox DoubleClick Event. I want to get the value of ID field in Listbox DoubleClick Event. For example ID=1 or ID=3 ... I just get "ID" when DoubleClick to Listbox.

MessageBox.Show(Listbox1.ValueMember.ToString())

This message give me "ID". Not "1" or "3"... Not value of ID Field. And

MessageBox.Show(Listbox1.DisplayMember.ToString())

This message give me "Text". Not "Software" or "Hardware" or "Printer" ... Not value of Text field. I want to see ID=1 Text=Software in MessageBox when I double click to listbox.

Thanks Emrah.

Lost User

See A Detailed Data Binding Tutorial[^].

Richard Deeming

Listbox1.ValueMember returns the name of the column which contains the value. You're looking for Listbox1.SelectedValue, which returns the value of the selected item.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

bellatriks

Hi Richard, Thanks for your answer. I can get value of data with Listbox1.SelectedValue. I get a new error in Listbox DoubleClick Event. My code is:

Private Sub ListBox1_MouseDoubleClick(sender As Object, e As MouseEventArgs) Handles ListBox1.MouseDoubleClick
If ListBox1.SelectedIndex <> -1 Then
ListBox2.Items.Add(ListBox1.SelectedItem)
ListBox1.Items.Remove(ListBox1.SelectedItem)
End If
End Sub

The error is: "Items collection cannot be modified when the DataSource property is set." How can I solve this problem? I tried some code example but I could not figure out it.

Thanks Emrah

Richard Deeming

This isn't something that's easy to do with a data-bound list. The simplest option is probably to go back to adding the items manually:

Dim dataset As New DataSet()

Using command As New SqlCommand("SELECT ID, Text FROM MyTable ORDER BY ID", Connection)
Dim adapter As New SqlDataAdapter(command)
adapter.Fill(dataset)
End Using

ListBox1.ValueMember = "ID"
ListBox1.DisplayMember = "Text"

For Each row As DataRow In dataset.Tables(0).Rows
ListBox1.Items.Add(row)
Next

Your ListBox1_MouseDoubleClick method should then work as expected.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

bellatriks

Hi Richard, Thanks for your answer. I tried your code. I get this error when double click to listbox:

MessageBox.Show(ListBox1.SelectedValue.ToString())

"Object reference not set to an instance of an object." By the way I see all items "System.Data.DataRow" in listbox when I use your code. I see normal data in listbox when I change your code

ListBox1.Items.Add(row)

to

ListBox1.Items.Add(row.Item(1).ToString())

How can I get value of ListBox1.SelectedValue?

Thanks, Emrah

Richard Deeming

OK, it looks like SelectedValue, DisplayMember and ValueMember only work with data-bound lists; when you add the items manually, the control displays the result of calling .ToString() on the item, and the SelectedValue property returns Nothing. It looks like you'll need to create your own class to wrap the values you're adding to the list. Something like this should work:

Public NotInheritable Class ListBoxItem(Of TValue)
Private ReadOnly _value As TValue
Private ReadOnly _text As String

Public Sub New(ByVal value As TValue, ByVal text As String)
    \_value = value
    \_text = text
End Sub

Public ReadOnly Property Value As TValue
    Get
        Return \_value
    End Get
End Property

Public ReadOnly Property Text As String
    Get
        Return \_text
    End Get
End Property

Public Overrides Function ToString() As String
    Return \_text
End Function

End Class

You would then create a new instance of this class for each DataRow, and add it to the list:

Dim dataset As New DataSet()

Using command As New SqlCommand("SELECT ID, Text FROM MyTable ORDER BY ID", Connection)
Dim adapter As New SqlDataAdapter(command)
adapter.Fill(dataset)
End Using

For Each row As DataRow In dataset.Tables(0).Rows
Dim item As New ListBoxItem(Of Integer)(row.Field(Of Integer)("ID"), row.Field(Of String)("Text"))
ListBox1.Items.Add(item)
Next

Your double-click method would then look something like this:

Private Sub ListBox1_MouseDoubleClick(sender As Object, e As MouseEventArgs) Handles ListBox1.MouseDoubleClick
If ListBox1.SelectedIndex <> -1 Then
Dim item As ListBoxItem(Of Integer) = TryCast(ListBox1.SelectedItem, ListBoxItem(Of Integer))
If item IsNot Nothing Then
MessageBox.Show(item.Value.ToString())

        ListBox2.Items.Add(item)
        ListBox1.Items.Remove(item)
    End If
End If

End Sub

NB: I've assumed that your ID field is an integer. If it's not, you'll need to substitute the correct type in the ListBoxItem(Of type) declarations and the row.Field(Of type)("ID") call.

"These

bellatriks

Hi Richard, Thanks for your answer. I used your code and it worked fine (I can get value of item in double click event) when I first doubleclick. When I second double click another item in listbox1, I can't get value of item and second item doesn't add to listbox2 and messagebox doesn't open second double click.

Thanks Emrah

Richard Deeming

It sounds like there isn't a selected item when you double-click a second time. Try a single click to select the item, then a double-click to move it. If that doesn't work, then you'll need to debug the event handler to see what's going wrong.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer