Getting selected items from a listbox using the index instead of value

Question

When I fill a listbox, I do something like this:

SqlDataAdapter adapter = new SqlDataAdapter("Select [DDLValue], [StoredValue] 
from [tmpDropdowns] WHERE DDLName = 'ddlHobbies' ORDER BY [SortOrder] ASC", conn);

adapter.Fill(DDLHobbies);

Hobbies.DataSource = DDLHobbies;
Hobbies.DataTextField = "DDLValue";
Hobbies.DataValueField = "StoredValue";
Hobbies.DataBind();

And when I want to retrieve the items, I do something like this:

var selectedHobbies = Hobbies.Items.Cast<ListItem>().Where(item => item.Selected);
string strHobbies = String.Join(",", selectedHobbies).TrimEnd();

Unfortunately, this gives me something like this:

strHobbies = "Fishing,Skiing,Pool,Birdwatching"

I say "unfortunately" because my objective is to use strHobbies in a "WHERE" clause in a SQL string. So, my SQL string should look something like:

SELECT * FROM MyTable WHERE Hobbies in (strHobbies)

So, there's 2 pieces to this problem.

1. How do I change that "var selectedHobbies" line to pull in the StoredValue rather than the DDLValue

2. How do I restructure my "string strHobbies" line so that it will put double quotes around each item, so I can use it in an 'IN' clause?

I'm thinking I probably want my final SQL string to look more like:

SELECT * FROM MyTable WHERE Hobbies in ("2", "5", "6", "9")

EDIT:

I've changed one thing:

List<string> selectedHobbies = Hobbies.Items.Cast<ListItem>()
    .Where(item => item.Selected).Select(item => item.Value).ToList();

string strHobbies = String.Join(",", selectedHobbies).TrimEnd();

This gives me the StoredValue instead of DDLValue when I hover over selectedHobbies, but when I type '?strHobbies' in the Immediate window, it says, "The name strHobbies does not exist in the current context". So, I've got #1 down, if someone could help me with #2 I'd appreciate it.

Answer 1

For #2 you want single quotes around each value, not double quotes. (Assuming Hobbies is not an integer in the DB)

string strHobbies = String.Join("','", selectedHobbies).TrimEnd();

Answer 2

you can put them in the Select, like so

List<string> selectedHobbies = Hobbies.Items.Cast<ListItem>()
.Where(item => item.Selected).Select(item => "'" + item.Value + "'").ToList();