Populating listbox

[Broomy7]

Hello!

I am fairly new to VB, but I am picking things up quickly and very much enjoying coding with VB 2005.

I have a problem populating a Listbox... I wish to populate this listbox using a sql command. I have a feeling it maybe the event which is wrong but being a newb I'm sure someone will correct me.

    Private Sub lstEngineer_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstEngineer.SelectedIndexChanged

        sql = "select * from Engineers where Engineers.Busy = '0'"

        Dim commandUpdate As New OdbcCommand(sql)
        Using connection As New OdbcConnection(constring)

            commandUpdate.Connection = connection
            connection.Open()
            commandUpdate.ExecuteNonQuery()

        End Using

Thanks very much!

[Hack]

Try it from the click event instead.

[Broomy7]

Hmmm, I tried it as a click although I wish this to be populated when the form is loaded so will I need add something in the loading of the form?

I'd ideally like to have this populated once the form loads as it will be showing the status of 'engineers' and their availability.

Thanks in advance...

[jmcilhinney]

I don't see any mention of a ListBox there at all, so there's no wonder the ListBox doesn't get populated. Follow the Data Access Examples link in my signature and take a look at the example for displaying data without updating. Once you can populate a DataTable with your data then you can simply bind it to your ListBox, e.g.

myListBox.DisplayMember = "name of column to display here"
myListBox.ValueMember = "name of ID column here"
myListBox.DataSource = myDataTable

[robertx]

I wrote the following procedure which sorts a data table, filters the data table according to specific criteria and populates a list box control with the resulting view as its data source.

 
Public Sub FillListBox(ByVal myListBox As DevExpress.XtraEditors.ListBoxControl, _
                    ByVal myDataTable As DataTable, ByVal sDisplayField As String, _
                    ByVal sItemDataField As String, ByVal sDataViewSortString As String, _
                    ByVal sDataViewRowFilter As String)

        'sorts the data table according to sDataViewSortString
        'filters the data table according to sDataViewRowFilter
        With myDataTable.DefaultView
            .Sort = sDataViewSortString
            .RowFilter = sDataViewRowFilter
        End With

        With myListBox
            .Items.Clear()
            .DisplayMember = sDisplayField
            .ValueMember = sItemDataField
            .DataSource = myDataTable.DefaultView
        End With

    End Sub

It works OK when there is only one list box control on the form but I start getting strange results when I have two list boxes on the same form and I try to populate them in succession with different row filters applied to the same data table. Both list boxes are populated with the same data even though the passed row filter is different and the ListBoxControl1.Items.Count property returns zero even though the list box is populated with data.

Note that my list box controls are of type DevExpress.XtraEditors.ListBoxControl which probably makes no difference but JM I notice that you are now promoting these products.

[robertx]

There is something aspect of the Data View object that I am missing as this amended version gives me the correct output.

Using the list box control's ItemCount property rather than its Items.Count property gives me the correct number of items in the list box.

Public Sub FillListBox(ByVal myListBox As DevExpress.XtraEditors.ListBoxControl, _
                    ByVal myDataTable As DataTable, ByVal sDisplayField As String, _
                    ByVal sItemDataField As String, ByVal sDataViewSortString As String, _
                    ByVal sDataViewRowFilter As String)

        'sorts the data table according to sDataViewSortString
        'filters the data table according to sDataViewRowFilter
        Dim myDataView As DataView = myDataTable.DefaultView

        With myDataView
            .Sort = sDataViewSortString
            .RowFilter = sDataViewRowFilter
        End With

        With myListBox
            .Items.Clear()
            .DisplayMember = sDisplayField
            .ValueMember = sItemDataField
            .DataSource = myDataView.ToTable
        End With

    End Sub

[jmcilhinney]

The aspect you're missing is the fact that if you bind the same DataView to two controls then both controls will show the same data. A DataTable's DefaultView is just one DataView object. If you filter the DefaultView and bind it, then filter it again and bind it again, the control you bound to first will update its display to reflect the new filter. One DataView can only show one set of data at a time.

That new code is creating a whole new DataTable from that DataView. Two DataTables means two sets of data.

What you should be doing is not mucking about with DataViews at all. That's for .NET 1.x. In .NET 2.0 you should be binding a different BindingSource to each set of controls, then binding the one DataTable to each BindingSource. You then set the Filter property of each BindingSource to a different value and each set of controls will reflect it. Voila.

[robertx]

OK. I rewrote the procedure using a locally declared BindingSource object but now I am back to where I started. It works with one List Box but not two. Does the BindingSource object have to persist beyond the procedure?

Public Sub FillListBox(ByVal myListBox As DevExpress.XtraEditors.ListBoxControl, _
                    ByVal myDataTable As DataTable, ByVal sDisplayField As String, _
                    ByVal sItemDataField As String, ByVal sDataViewSortString As String, _
                    ByVal sDataViewRowFilter As String)

        'sorts the data table according to sDataViewSortString
        'filters the data table according to sDataViewRowFilter
        Dim myBindingSource As New BindingSource

        With myBindingSource
            .DataSource = myDataTable
            If sDataViewSortString.Length > 0 Then .Sort = sDataViewSortString
            If sDataViewRowFilter.Length > 0 Then .Filter = sDataViewRowFilter
        End With

        With myListBox
            .Items.Clear()
            .DisplayMember = sDisplayField
            .ValueMember = sItemDataField
            .DataSource = myBindingSource
        End With

        myBindingSource = Nothing

    End Sub

[jmcilhinney]

You have two ListBoxes added to your form in the designer no doubt. Why would you not add your BindingSources in the designer too?

[robertx]

I added two BindingSource controls to the designer and passed each to a FillListBox procedure (one for each list box) but I am still getting the same result.

Public Sub FillListBox(ByVal myListBox As DevExpress.XtraEditors.ListBoxControl, _
                    ByVal myBindingSource As BindingSource, _
                    ByVal myDataTable As DataTable, ByVal sDisplayField As String, _
                    ByVal sItemDataField As String, ByVal sDataViewSortString As String, _
                    ByVal sDataViewRowFilter As String)

        'sorts the data table according to sDataViewSortString
        'filters the data table according to sDataViewRowFilter
        With myBindingSource
            .DataSource = myDataTable
            If sDataViewSortString.Length > 0 Then .Sort = sDataViewSortString
            If sDataViewRowFilter.Length > 0 Then .Filter = sDataViewRowFilter
        End With

        With myListBox
            .Items.Clear()
            .DisplayMember = sDisplayField
            .ValueMember = sItemDataField
            .DataSource = myBindingSource
        End With

    End Sub

[jmcilhinney]

OK, my humblest apologies for feeding you incorrect information. I was thinking I had done it this way before but I mustn't have because, as you're seeing, it just doesn't work like that. I've been using business objects myself lately so I haven't bound any DataTables directly for a while. Again, sorry for wasting your time.

Even with BindingSources, if you want more than one view of a DataTable then you need to create at least one DataView explicitly. I'd suggest using the DefaultView if you only want one view but creating all views explicitly if you want more than one. You should keep the BindingSources though. Just change this:

.DataSource = myDataTable

to this:

.DataSource = New DataView(myDataTable)

and it will work properly.

[robertx]

The following works. I don't need to add a BindingSource control to the form and pass it to my procedure - I can create it on the fly.

Public Sub FillListBox(ByVal myListBox As DevExpress.XtraEditors.ListBoxControl, _
                    ByVal myDataTable As DataTable, ByVal sDisplayField As String, _
                    ByVal sItemDataField As String, ByVal sSortString As String, _
                    ByVal sRowFilter As String)

        'sorts the data table according to sSortString
        'filters the data table according to sRowFilter
        Dim myBindingSource As New BindingSource

        With myBindingSource
            .DataSource = New DataView(myDataTable)
            If sSortString.Length > 0 Then .Sort = sSortString
            If sRowFilter.Length > 0 Then .Filter = sRowFilter
        End With

        With myListBox
            .Items.Clear()
            .DisplayMember = sDisplayField
            .ValueMember = sItemDataField
            .DataSource = myBindingSource
        End With

        myBindingSource = Nothing

    End Sub

I can then retrieve the item data for the selected item with:

Dim sItemData As String = ListBoxControl1.SelectedValue

This is a lot tidier compared to what I have used in the past to achieve the same thing.