[RESOLVED] Remove item from listbox if exists in another listbox

[TUSSFC]

I have 2 listboxes on my form.

Listbox 1 - populated with "available" field names
Listbox 2 - populated with "selected" field names.

There cannot be the same field name in both listboxes - it's either one of the other.

The logic I was going to apply was to populate Listbox 1 with every field name ... and then as I populated Listbox with the field names which the user has already selected, remove the corresponding item from Listbox 1.

However I cannot for the life of me get the remove to work!

How do I remove based on the text string of the item - rather than the index position??

[Pradeep1210]

This would move the selected item from ListBox1 to ListBox2. Is this what you are looking for?

Dim li As Object = ListBox1.SelectedItem
ListBox2.Items.Add(li)
ListBox1.Items.Remove(li)

[TUSSFC]

Not quite ... my use of the word "selected" may have misled.

Both listboxes are populated based on data pulled from an MS Access backend.

tbl_Fields contains a list of all fields available.
tbl_User_Fields contains a list of all fields a user has 'selected/chosen'.

So, upon clicking a command button - I need:

Listbox 1 to populate with all fields from tbl_Fields
Listbox 2 to populate with all fields the user has chosen (stored in tbl_User_Fields)
Remove each corresponding item in Listbox 1 where it appears in Listbox 2

I guess the logic works both ways ... and you could populate either Listbox first. However, its removing an item from the other listbox using the Item text that I can't work out :-s

[Pradeep1210]

Maybe like this:

Dim items = From li In ListBox1.Items Where ListBox2.Items.Contains(li)
For Each item In items.ToList
    ListBox1.Items.Remove(item)
Next

But why are you doing this way anyways?? You populate a listbox and then remove some of the items. So why add them in the first place? Can't you just remove from list before populating the listbox? That should be faster performancewise too.

[TUSSFC]

I guess that would mean a different SQL query. My code so far is as follows:

            ' populate available fields listbox
            oConnect("SELECT * FROM tbl_Fields ORDER BY Field_Name", "Read")
            Do While oRS.Read()
                Me.lst_Mails_Fields_Available.Items.Add(New ValueDescriptionPair(oRS("Field_ID"), oRS("Field_Name")))
            Loop
            oDisconnect()

            ' populate selected fields listbox
            oConnect("SELECT tbl_Mail_Fields.*, tbl_Fields.Field_Name, * FROM tbl_Fields INNER JOIN tbl_Mail_Fields ON tbl_Fields.Field_ID = tbl_Mail_Fields.Field_ID WHERE Mail_ID = " & CType(Me.cbo_Mails_Menu_Edit.SelectedItem, ValueDescriptionPair).Value & " ORDER BY Field_Name", "Read")
            Do While oRS.Read()
                Me.lst_Mails_Fields_Selected.Items.Add(New ValueDescriptionPair(oRS("tbl_Fields.Field_ID"), oRS("Field_Name")))
                Me.lst_Mails_Fields_Available.Items.Remove(oRS("tbl_Fields.Field_ID"))
            Loop
            oDisconnect()

Its this line which doesn't work:
Me.lst_Mails_Fields_Available.Items.Remove(oRS("tbl_Fields.Field_ID"))

I guess what you are suggesting is amending the first SQL statement from:
SELECT * FROM tbl_Fields ORDER BY Field_Name

to somesort of unmatched duplicates query?

[TUSSFC]

[QUOTE=Pradeep1210;3718244]Maybe like this:

Dim items = From li In ListBox1.Items Where ListBox2.Items.Contains(li)
For Each item In items.ToList
    ListBox1.Items.Remove(item)
Next

This looks nice but doesn't work in .NET 2.0 (sorry, forgot to mention - unavoidable) as it seems to require Linq. Is there another way to acheive the same?

[cicatrix]

[QUOTE=TUSSFC;3718270]

Maybe like this:

Dim items = From li In ListBox1.Items Where ListBox2.Items.Contains(li)
For Each item In items.ToList
    ListBox1.Items.Remove(item)
Next

This looks nice but doesn't work in .NET 2.0 (sorry, forgot to mention - unavoidable) as it seems to require Linq. Is there another way to acheive the same?

Something like this. I have .NET 3.5, but it should work in 2.0:

Dim item As Object  ' Don't remember if I can narrow the type here
 
For Each item In ListBox1.Items
    If ListBox2.Contains(item) Then
        ListBox1.Items.Remove(item)
    End If
Next

[Pradeep1210]

Use this query:

SELECT * FROM tbl_Mail_Fields
LEFT JOIN tbl_Fields ON tbl_Fields.Field_ID = tbl_Mail_Fields.Field_ID

When you want to populate all fields box, don't set any filter.
When you want to populate the selected fields set filter tbl_Fields.Field_ID Is Null

[TUSSFC]

Thanks chaps ... marking this as resolved. I actually went with something totally different and made use of an array.

            ' count how many mail fields there are for the selected mail
            Dim strSelFieldCount As Integer
            oConnect("SELECT * FROM tbl_Mail_Fields WHERE Mail_ID = " & CType(Me.cbo_Mails_Menu_Edit.SelectedItem, ValueDescriptionPair).Value & "", "Update")
            oCmd.CommandText = "SELECT COUNT(*) FROM tbl_Mail_Fields WHERE Mail_ID = " & CType(Me.cbo_Mails_Menu_Edit.SelectedItem, ValueDescriptionPair).Value & ""
            strSelFieldCount = oCmd.ExecuteScalar() - 1
            oDisconnect()

            ' populate selected fields listbox
            ' store selected field IDs in an array
            Dim ar_Sel(strSelFieldCount) As Integer
            Dim i_Sel As Integer = 0
            oConnect("SELECT tbl_Mail_Fields.*, tbl_Fields.Field_Name, * FROM tbl_Fields INNER JOIN tbl_Mail_Fields ON tbl_Fields.Field_ID = tbl_Mail_Fields.Field_ID WHERE Mail_ID = " & CType(Me.cbo_Mails_Menu_Edit.SelectedItem, ValueDescriptionPair).Value & " ORDER BY Field_Name", "Read")
            Do While oRS.Read()
                Me.lst_Mails_Fields_Selected.Items.Add(New ValueDescriptionPair(oRS("tbl_Fields.Field_ID"), oRS("Field_Name")))
                ar_Sel(i_Sel) = oRS("tbl_Fields.Field_ID")
                i_Sel = i_Sel + 1
            Loop
            oDisconnect()

            ' populate available fields listbox if not found in array
            oConnect("SELECT * FROM tbl_Fields ORDER BY Field_Name", "Read")
            Do While oRS.Read()
                ' only add to listbox if doesn't exist in array
                If IsInArray(oRS("Field_ID"), ar_Sel) = False Then
                    Me.lst_Mails_Fields_Available.Items.Add(New ValueDescriptionPair(oRS("Field_ID"), oRS("Field_Name")))
                End If
            Loop
            oDisconnect()

May be a little messy, but I get the desired results and as the recordsets I'm doing to be dealing with are quite small - performance is OK.