Moving Data From Databound Listbox To Unbound Listbox And Back VB.NET

Question

So I have been browsing about and there are a lot of explanations on how to move data from listbox to listbox. I have a listbox bound to a source from my SQL server and another unbound. My aim is to move the data from the first (LBsearch) to the second (LBselect) and back. I have seen people say use LBselect.Items.Add(LBsearch.SelectedItem) however it doesn't return data and instead shows System.Data.DataRowView. I've tried many different suffixes and all show this apart from LBsearch.text. Then to remove the data from the first one I've been removing the databindingsource (PersonBindingSource) with PersonBindingSource.Remove(LBsearch.SelectedItem) but my issue is adding the data back again.

PersonBindingSource.Add(LBselect.SelectedItem) gives an error:

System.InvalidOperationException: Objects added to a BindingSource's list must all be of the same type.
   at System.Windows.Forms.BindingSource.Add(Object value)
   at Project_Program.Participants.btnremoveselect_Click(Object sender, EventArgs e) in E:\Documents\Visual Studio\Project Program\Project Program\Participants.vb:line 39

PersonBindingSource.Add(PersonBindingSource.Item(LBsearch.SelectedIndex)) gives an error:

System.ArgumentException: Cannot add external objects to this list.
   at System.Data.DataView.System.Collections.IList.Add(Object value)
   at System.Windows.Forms.BindingSource.Add(Object value)
   at Project_Program.Participants.btnremoveselect_Click(Object sender, EventArgs e) in E:\Documents\Visual Studio\Project Program\Project Program\Participants.vb:line 38

Any help would be appreciated. Thanks

---

Private Sub btnaddselect_Click(sender As Object, e As EventArgs) Handles btnaddselect.Click
    If LBsearch.Items.Count > 0 Then
        MsgBox(LBsearch.Text)
        ' PersonBindingSource.Remove(PersonBindingSource.Item(LBsearch.SelectedIndex))
        LBselect.Items.Add(LBsearch.Text)
        PersonBindingSource.Remove(LBsearch.SelectedItem)

        ' filter()
    End If
End Sub

Private Sub btnremoveselect_Click(sender As Object, e As EventArgs) Handles btnremoveselect.Click
    If LBselect.Items.Count > 0 Then
        Try
            'PersonBindingSource.Add(PersonBindingSource.Item(LBsearch.SelectedIndex))
            PersonBindingSource.Add(LBselect.SelectedItem)

            MsgBox(LBselect.SelectedItem.ToString())
            LBselect.Items.Remove(LBselect.SelectedItem)
        Catch ex As Exception
            TextBox1.Text = (ex.ToString)
        End Try
        'filter()
    End If
End Sub

Answer 1

A major problem with moving rows is that since they are DataRows, they will not display well in the unbound control. If you pull out something useful like a name, you will have to recreate the DataRow to return it to the original/bound/source control.

This is a problem because, now it is a new row, so a DataAdpter might add it to the database again! One way to avoid that is to clone the table. Also, if/when you move them back, they will appear at the bottom of the list and not in their original position.

There is a better way than duplicating the table data and moving anything anywhere.

Since the act of being selected can be represented with a simple Boolean, it can be coded so that the Selected ones show in one control, the unSelected ones in the other. For this, a new Selected column is needed. If possible, add one using your SQL:

' MS Access syntax 
Dim SQL = "SELECT a, b, c,..., False As Selected FROM tblFoo"

This will create the new column in your datatable with all the values initialized to False. You can also add the column manually.

' form level vars
Private dvSource As DataView
Private dvDest As DataView
...
' set up:
' *** Add a column manually if you cant use SQL
dtSample.Columns.Add("Selected", GetType(Boolean))

' we need to loop and set the initial value for an added column
For Each r As DataRow In dtSample.Rows
    r("Selected") = False
Next
' *** end of code for adding col manually

' when the column is added from SQL, you will need:
dtSample.Columns("Selected").ReadOnly = False


' create Source DV as Selected = False
dvSource = New DataView(dtSample,"Selected=False", "",
                                DataViewRowState.CurrentRows)
' create Dest DV as Selected = True
dvDest = New DataView(dtSample, "Selected=True", "",
                                DataViewRowState.CurrentRows)
' assign DS
lbSource.DataSource = dvSource
lbSource.DisplayMember = "Name"
lbSource.ValueMember = "Id"

lbDest.DataSource = dvDest
lbDest.DisplayMember = "Name"
lbDest.ValueMember = "Id"

Then in the click events:

' select
CType(lbSource.SelectedItem, DataRowView).Row("Selected") = True

' deselect:
CType(lbSource.SelectedItem, DataRowView).Row("Selected") = False

The two DataView objects will filter on Selected inversely. When you change the state of a row, it is instantly removed from one and appears in the other without actually being added/removed from any table (or collection or control). If you move it back to the Source it will appear in the same position as before.

In this case, the RowState of any item moved will be Modified, which of course it is (unlike the move-to-table approach where they would be new rows (Added) which they are not).

It is hard to illustrate without 5 or 6 images, but the idea:

It is actually a pretty simple method and more economical than one invovling actually moving rows. Using DataViews the rows/items look like they move to another table or control, but they do not, nor do they need to.