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

Habib · Accepted Answer · 2013-04-16 09:41:51Z

4

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 + "'"));

edited Apr 16, 2013 at 9:41

answered Apr 16, 2013 at 9:35

Habib

224k30 gold badges420 silver badges446 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sergey Berezovskiy Over a year ago

+1 for laconic LINQ, but you should add filter by selected items

Stuart Over a year ago

This is perfect! Thank you very much @Habib :) What is laconic LINQ? how is it different from regular LINQ?

Habib Over a year ago

@Stuart, nothing it is regular LINQ, its just the writing style :) see define: laconic

Silvermind · Accepted Answer · 2013-04-16 09:37:56Z

1

You could use String.Join with some Linq

For clearity I've put the code in variables.

if (lstSalesGroup.SelectedItem != null)
{
    var queryStr = "AND SALES_GROUP IN ({0})";
    var selectedItemValues = (from itm in lstSalesGroup.Items.Cast<ListItem>()
                              where itm.Selected
                              select String.Format("'{0}'", itm));
    selectQuery.Append(String.Format(queryStr, String.Join(",", selectedItemValues)));
}

edited Apr 16, 2013 at 9:37

answered Apr 16, 2013 at 9:31

Silvermind

5,9943 gold badges27 silver badges47 bronze badges

2 Comments

Sergey Berezovskiy Over a year ago

You are missing quotes around values. And I don't sure if itm will not be of object type

Silvermind Over a year ago

@lazyberezovsky Thanks, I missed that so I've edited it. Should be alright now.

user707727 · Accepted Answer · 2013-04-16 09:39:32Z

1

Try using linq

selectQuery.Append("AND SALES_GROUP IN (");

selectQuery.Append(string.Join(",", lstSalesGroup.Items.Select(i => "'" + i.Value + "'")));    

selectQuery.Append(")");

This will solve your problem, but you have a problem with SQL injection here. I would strongly advice you to use parameters in your query.

edited Apr 16, 2013 at 9:39

answered Apr 16, 2013 at 9:33

user707727

1,2877 silver badges16 bronze badges

3 Comments

Stuart Over a year ago

Thanks for pointing this out. I thought it was safe from injection if I'm programatically populating the select options?

user707727 Over a year ago

This is a common mistake but there is a potential risk for SQL injection here. Imagine that someone somehow managed to get the value "') Drop table SomeTable;--" into your list. Can you see what your final query would look like? =)

Stuart Over a year ago

...something like my P45!! I'll put all the final values into parameters. thanks!

Zak · Accepted Answer · 2017-03-26 12:39:07Z

0

Dim s As String = "'"
    For i = 0 To ListBox1.Items.Count - 1
        s = s & ListBox1.Items.Item(i) & "','"
    Next

answered Mar 26, 2017 at 12:39

Zak

1

2 Comments

d4Rk Over a year ago

This doesn't really look like C# ;-) And please provide some explanation, to improve your answer.

Eric Leschinski Over a year ago

Question is about C#, this is Visual Basic.

richardr · Accepted Answer · 2017-03-26 13:09:15Z

0

Try this:

if (lstSalesGroup.SelectedItem != null)
{
    selectQuery.Append("AND SALES_GROUP IN (");
    var local = lstSalesGroup.Items.Where(c => c.Selected)
                       .Select(c => "'"+c.Value+"'")
                       .Aggregate((c,n) => c+ ", "+n);

   selectQuery.Append(local);            
   selectQuery.Append(")");
}

Look at this example for more info on the .Aggragate(...) method

edited Mar 26, 2017 at 13:09

richardr

5,2223 gold badges21 silver badges14 bronze badges

answered Apr 16, 2013 at 9:30

Jens Kloster

11.3k5 gold badges44 silver badges55 bronze badges

4 Comments

Sergey Berezovskiy Over a year ago

That will produce correct result, but I think that aggregating strings without StringBuilder will produce many unnecessary strings. Simple String.Join will be better here

Jens Kloster Over a year ago

@lazyberezovsky I agree. string.Join is simpler. I just didn't thought of it :) My mind is "linq'ed"

Sergey Berezovskiy Over a year ago

Here is a hint how to stay with LINQ, but avoid messing memory with strings Aggregate(new StringBuilder(), (sb,s) => sb.Append(s), sb => sb.ToString()) :)

Silvermind Over a year ago

@lazyberezovsky Darn interesting approach, never investigated that overload in Aggregate. +1

Collectives™ on Stack Overflow

Building SQL IN statement from listbox control

5 Answers 5

3 Comments

2 Comments

3 Comments

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

2 Comments

3 Comments

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related