8.1 Work with Data-Bound Multi-Select List Boxes Using Windows Forms
It is common to have to assign products to categories, which is a one-to-many
relationship. Sometimes you want to be able to do this in a somewhat bulk fashion. One
of the methods that works well is using the ListBox control. Using the ListBox control
for single selections is no big deal, but when it comes to using it in a multi-select method,
it starts getting trickier. This How-To shows you how to create an intuitive interface for
assigning products to categories using a couple of multi-select list boxes on a Windows
Form.
You can assign a category to each product, but you would like to have a method of
maintaining all the products for a category at one time. How do you take advantage of a
multi-select list box to perform this task?
Technique
Using the ListBox control in regular single selection mode is as straightforward in .NET
as in prior versions of Visual Basic. The same can be said in the case of using the multi-
select mode of list boxes. It is as confusing in .NET as it was in prior versions.
Using the ListBox control in single entry mode is pretty straightforward. You just need to
use the SelectedItem property with the index of 0. However, if you want to use the
ListBox control in multi-select mode, then you must perform some more work and access
some other properties (see Table 8.1).
Table 8.1. Properties Having to Do with Multi-Selection on ListBox Controls (In
Order of Use in This How-To's Steps)
Property/Object Description
SelectionMode Property of the ListBox control. The settings for this are None,
One, MultiSimple, or MultiExtended.
SelectedIndices
(index)
A collection of the ListBox control, this returns the indices
(location in the list) of all the selected items.
SelectedIndices.Count Property of the ListBox control. A count of the number of items
selected in the list box.
DataRowView Object type that the data provider provides.

ComboBox Name cboCategories
Label Text Unselected Products
ListBox Name lstUnSelected
SelectionMode MultiSimple
Label Text Selected Products
ListBox Name lstSelected
SelectionMode MultiSimple
Command Button Name btnSelect
Text >
Command Button Name btnUnSelect
Text <
CheckBox Name chkUnAssignedOnly
Text UnAssigned Products Only
2. As with some of the other chapters' projects, before creating the code that will be
attached to the Load event of the form, you need to create a support routine to
create the Connection string. Called BuildCnnStr, the function can been seen in
Listing 8.1. This function takes a server and database names passed to it and
creates a connection string.
Listing 8.1 modGeneralRoutines.vb: Creating a Connection String
Function BuildCnnStr(ByVal strServer As String,
ByVal strDatabase As String) As String

Dim strTemp As String
strTemp = "Provider=SQLOleDB; Data Source=" & strServer & ";"
strTemp &= "Initial Catalog=" & strDatabase & ";"
strTemp &= "Integrated Security=SSPI"

Return strTemp
End Function
Although you could create a routine that would pass back a Connection object, a

LoadUnSelectedProducts()
LoadSelectedProducts()

End Sub
4. Create the LoadUnSelectedProducts routine by entering the code shown in Listing
8.3 into the form you created for this example. This routine starts off by testing the
check box called chkUnAssignedOnly. Based on that value, a SQL string is
created that grabs the products that are not assigned to any product, if
chkUnAssignedOnly = True. All products that are not assigned to the chosen
category are retrieved. The SQL String is stored in the variable called strSQL.
Next, the DataAdapter object called odaUnselected is set to strSQL and the SQL
Server connection string. The DataTable object called dtUnSelected is then filled
and assigned to the list box called lstUnSelected. The DisplayMember and
ValueMember properties are then set. Last, the ClearSelected method is called to
make sure no entries remain selected.
Listing 8.3 frmHowTo8_1.vb: Populating the List Box Displaying Unselected
Products
Sub LoadUnSelectedProducts()

Dim odaUnSelected As OleDb.OleDbDataAdapter
Dim dtUnSelected As New DataTable()
Dim strSQL As String

'-- If the check box for Unassigned Only is checked, then
' grab the product items where the category is null; otherwise, load
' it up with those products not assigned to the current category.

If chkUnAssignedOnly.Checked Then
strSQL = "Select ProductID, ProductName From Products " & _ "
Where CategoryID IS NULL Order By ProductName"