none
Datatable.Select StackOverflow RRS feed

  • Question

  • Hi guys

    I have a large DataTable(3000 rows) with a column called ProductName.

    I have created a ListBox where the user can select a subset or all of these 3000 rows, for a specific column, it looks like this: Please not that any duplicates are removed, so a Bag could exist more than once.

    ProductName
    Apple
    Apron
    Bag
    Box
    .
    .
    Hat
    Shirt
    Tissue

    The user could select all the ProductNames from B-H(Roughly about 800 rows) by selecting them in the ListBox. I wanted to use a Select on the DataTable to find out exactly how many rows match and to display this number.

    The select statement expression I construct is of the following format:


    "ProductName like '*Bag*' OR ProductName like '*Box*' ........ until ProductName like '*Hat*'"


    The part that generates this expression string is a for each loop going through the list of items in the ListBox.  By debugging, I have found that this code works upto a maximum of 368 rows. Any more and I am hit with this:


    An unhandled exception of type 'System.StackOverflowException' occurred in System.Data.dll


    So, since the code works up to this amount of rows, it must be correct. Does anyone have any clues as to whether there is a limit to the amount of OR's you can stick in an expression? Or perhaps a better way to word the expression all together.

    Thanks for your help.

    Wednesday, March 19, 2008 11:49 PM

Answers

  • Unfortunately, you cannot use more than a few hundred OR expressions.

     

    Sometimes, it is possible to get around this by using a single IN expression instead.  See my solution in: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2981737&SiteID=1

     

    However, this does not appear like it will helpful to you because you are using the LIKE operator.

     

    You may be able to split your statement into multiple Selects and then combine the results (combining into a single array and removing duplicates), which would ultimately have the same effect as using a single Select.

     

     

    Thursday, March 20, 2008 1:40 AM