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
A major problem with moving rows is that since they are DataRow
s, 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 DataView
s the rows/items look like they move to another table or control, but they do not, nor do they need to.