locked
Filter Gridview based on listbox multiple selection RRS feed

  • Question

  • User-956807734 posted

    Hi,

    I have application which requires listbox control to select multiple items and display result on to gridview control. how can i do this

     

    Thx

    Saturday, June 20, 2009 1:53 PM

Answers

  • User626880745 posted

     this discussion already has an example: http://forums.asp.net/p/1138124/1823534.aspx#1823534

     and, here's a working example (sample database used):

                <asp:SqlDataSource ID="PopListBoxDS" runat="server" ConnectionString="<%$ ConnectionStrings:sqlExpressConnString%>"
                    SelectCommand="SELECT [ProductID],[Name],[ProductNumber] FROM [AdventureWorks].[Production].[Product]">
                </asp:SqlDataSource>
                <asp:ListBox ID="ProductsListBox" runat="server" DataSourceID="PopListBoxDS" DataTextField="Name"
                    DataValueField="ProductID" SelectionMode="Multiple"></asp:ListBox>
                <asp:Button ID="Button1" runat="server" Text="Search" />
                <asp:SqlDataSource ID="PopGridViewDS" runat="server" ConnectionString="<%$ ConnectionStrings:sqlExpressConnString%>"
                    SelectCommand="SELECT [ProductID],[Name],[ProductNumber] FROM [AdventureWorks].[Production].[Product] WHERE CHARINDEX(',' + CAST(ProductID as VARCHAR(10)) + ',',',' + @ProductID + ',') > 0">
                    <SelectParameters>
                        <asp:FormParameter Name="ProductID" FormField="ProductsListBox" Type="String"/>
                    </SelectParameters>
                </asp:SqlDataSource>
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="PopGridViewDS">
                    <Columns>
                        <asp:BoundField DataField="ProductID" HeaderText="Product ID" SortExpression="ProductID" />
                        <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                        <asp:BoundField DataField="ProductNumber" HeaderText="Product Number" SortExpression="ProductNumber" />
                    </Columns>
                </asp:GridView>

    notice the SelectCommand used; reason is that if you simply use something like SELECT [ProductID],[Name],[ProductNumber] FROM [AdventureWorks].[Production].[Product] WHERE (ProductID IN (@ProductID)) you might get an error like: Conversion failed when converting the nvarchar value '3,4' to data type int depending on your actual values reason being the comma-separated value in T-SQL is not parsed against an INT column in the database.
    so, you either need to use the kind of casting I've shown (notice VARCHAR(10)) OR create a function in your database and call it like:

     SelectCommand="SELECT [ProductID],[Name],[ProductNumber] FROM [AdventureWorks].[Production].[Product] WHERE ProductID IN (SELECT value FROM fn_split(@ProductID,','))">

    here's the code to the function, should you decide to go this way:

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE FUNCTION [fn_Split](@text varchar(8000), @delimiter varchar(20) = ' ')
    RETURNS @Strings TABLE
    (  
      position int IDENTITY PRIMARY KEY,
      value varchar(8000) 
    )
    AS
    BEGIN
    DECLARE @index int
    SET @index = -1
    WHILE (LEN(@text) > 0)
     BEGIN
        SET @index = CHARINDEX(@delimiter , @text)
        IF (@index = 0) AND (LEN(@text) > 0)
          BEGIN 
            INSERT INTO @Strings VALUES (@text)
              BREAK
          END
        IF (@index > 1)
          BEGIN 
            INSERT INTO @Strings VALUES (LEFT(@text, @index - 1)) 
            SET @text = RIGHT(@text, (LEN(@text) - @index))
          END
        ELSE
          SET @text = RIGHT(@text, (LEN(@text) - @index))
        END
      RETURN
    END


    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

     
    you could also have used the selected event of the SqlDataSource and parsed the values etc...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 21, 2009 3:53 PM

All replies

  • User-1675817941 posted

     

    <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>                        Dim dt As New<o:p></o:p> DataTable dt = new datatable;<o:p></o:p><o:p> </o:p>        dt.Columns.Add["ID"];<o:p></o:p>DataRow dr ;<o:p></o:p><o:p> </o:p>        dr = dt.NewRow<o:p></o:p>
                     int *selIndex = new int[numberSelected];<o:p></o:p>
                     GetSelItems( numberSelected, selIndex );<o:p></o:p>
    <o:p> </o:p>
                     for (int i=0; i < numberSelected; i++)<o:p></o:p>
                     {<o:p></o:p>
            Dr["id"]= CheckBoxList1.Items[i].Selected<o:p></o:p>
    dt.Rows.Add[dr];<o:p></o:p>
    dt.AcceptChanges()<o:p></o:p>
    <o:p> </o:p>
                     }<o:p></o:p>

    gridview.datasource=dt.defalueview;

    gridview.databind();

     Thanks

    All The best

     

    Saturday, June 20, 2009 4:16 PM
  • User-956807734 posted

    i am still having problem displaying data  in gridview. I am trying to add on you code on button click event . It is only displaying first selelcted value data into grid view..

    Sunday, June 21, 2009 3:09 PM
  • User626880745 posted

     this discussion already has an example: http://forums.asp.net/p/1138124/1823534.aspx#1823534

     and, here's a working example (sample database used):

                <asp:SqlDataSource ID="PopListBoxDS" runat="server" ConnectionString="<%$ ConnectionStrings:sqlExpressConnString%>"
                    SelectCommand="SELECT [ProductID],[Name],[ProductNumber] FROM [AdventureWorks].[Production].[Product]">
                </asp:SqlDataSource>
                <asp:ListBox ID="ProductsListBox" runat="server" DataSourceID="PopListBoxDS" DataTextField="Name"
                    DataValueField="ProductID" SelectionMode="Multiple"></asp:ListBox>
                <asp:Button ID="Button1" runat="server" Text="Search" />
                <asp:SqlDataSource ID="PopGridViewDS" runat="server" ConnectionString="<%$ ConnectionStrings:sqlExpressConnString%>"
                    SelectCommand="SELECT [ProductID],[Name],[ProductNumber] FROM [AdventureWorks].[Production].[Product] WHERE CHARINDEX(',' + CAST(ProductID as VARCHAR(10)) + ',',',' + @ProductID + ',') > 0">
                    <SelectParameters>
                        <asp:FormParameter Name="ProductID" FormField="ProductsListBox" Type="String"/>
                    </SelectParameters>
                </asp:SqlDataSource>
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="PopGridViewDS">
                    <Columns>
                        <asp:BoundField DataField="ProductID" HeaderText="Product ID" SortExpression="ProductID" />
                        <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                        <asp:BoundField DataField="ProductNumber" HeaderText="Product Number" SortExpression="ProductNumber" />
                    </Columns>
                </asp:GridView>

    notice the SelectCommand used; reason is that if you simply use something like SELECT [ProductID],[Name],[ProductNumber] FROM [AdventureWorks].[Production].[Product] WHERE (ProductID IN (@ProductID)) you might get an error like: Conversion failed when converting the nvarchar value '3,4' to data type int depending on your actual values reason being the comma-separated value in T-SQL is not parsed against an INT column in the database.
    so, you either need to use the kind of casting I've shown (notice VARCHAR(10)) OR create a function in your database and call it like:

     SelectCommand="SELECT [ProductID],[Name],[ProductNumber] FROM [AdventureWorks].[Production].[Product] WHERE ProductID IN (SELECT value FROM fn_split(@ProductID,','))">

    here's the code to the function, should you decide to go this way:

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE FUNCTION [fn_Split](@text varchar(8000), @delimiter varchar(20) = ' ')
    RETURNS @Strings TABLE
    (  
      position int IDENTITY PRIMARY KEY,
      value varchar(8000) 
    )
    AS
    BEGIN
    DECLARE @index int
    SET @index = -1
    WHILE (LEN(@text) > 0)
     BEGIN
        SET @index = CHARINDEX(@delimiter , @text)
        IF (@index = 0) AND (LEN(@text) > 0)
          BEGIN 
            INSERT INTO @Strings VALUES (@text)
              BREAK
          END
        IF (@index > 1)
          BEGIN 
            INSERT INTO @Strings VALUES (LEFT(@text, @index - 1)) 
            SET @text = RIGHT(@text, (LEN(@text) - @index))
          END
        ELSE
          SET @text = RIGHT(@text, (LEN(@text) - @index))
        END
      RETURN
    END


    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

     
    you could also have used the selected event of the SqlDataSource and parsed the values etc...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 21, 2009 3:53 PM