Multiple Selected Values of ListBox as Parameters to SELECT SQL Query

Question

Hello,

I want to pass the Multiple selected values from ListBox as parameters to my Select SQL Query.

I am using VB.NET, how can I achieve this ?...

Answer 1

Probably the best way to start is to iterate through the listbox and get only the items that are selected. Assuming you are using Web forms, here is an example:

For Each myItem As ListItem In myListBox.Items
    If myItem.Selected Then
        'Add the item to the list of parameters
    End If
Next

Answer 2

Select your items by iterating through the list boxes items and add them to a collection of some sort and pass them on.

For i = 0 To ListBox1.Items.Count - 1
   If ListBox1.Items(i).Selected Then
       ' Add to collection
   End If
Next

In situations like this, it might be a good idea to wrap your SQL statement in a stored procedure as it will give you flexibility in terms of how you send your parameters.

Another trick I have seen used is, when people have a varying amount of parameters based on selections is to append the selections against an always true condition like:

Base SQL = Select * From MyTable where 1=1

Then based on the list items(collection), you can selectively "ADD" Ands

Answer 3

Set the selection mode to SelectionMode.MultiExtended or SelectionMode.MultiSimple. Consume the SelectedItems property.

UPDATE

I dont know what your select statement is, so in C# (sorry no VB)

string sql = "select [columns] from [table] where [column] in (";
string delimiter = ",";
foreach(var selected in lb1.SelectedItems)
{
  sql = String.Concat(sql, selected.text, delimiter);
}
sql = sql.Substring(0, sql.Length-1);
sql = String.Concat(sql, @");");