Building SQL IN statement from listbox control

Question

I'm trying to build a SQL query using StringBuilder and I've become stuck trying to do part of the WHERE clause.

I have a list box with a bunch of values and allows multiple selection. I need to iterate through the selected items and put then in an IN statement like...

WHERE SOME_FIELD IN ('Value','NextValue','AnotherValue')

so far I've written the code like this...

if (lstSalesGroup.SelectedItem != null)
{
    selectQuery.Append("AND SALES_GROUP IN (");
    foreach (ListItem item in lstSalesGroup.Items)
    {
        if (item.Selected)
            selectQuery.Append("'" + item.Value + "',");
    }
    selectQuery.Append(")");
}

I need to test if the item is the last in the loop so that it doesn't put on the "," before the closing ")".

How can I do this? Or if there's a better way to build this part of the query please do suggest, I am still learning, we all have to start somewhere! :)

Eventually this will be a query for a part search.

Thanks in advance

Answer 1

Couple of ways for doing that.

You can use string.TrimEnd to remove the extra comma from the string or you can create a new string using string.Join like

string InPartQuery = string.Join(",", lstSalesGroup.Items
                                          .Cast<ListItem>()
                                          .Where(t => t.Selected)
                                          .Select(r => "'" + r.Value + "'"));