Listbox Multiselect as filter for Gridview

Discussion in 'ASP .Net Web Controls' started by Nick Nelson, Feb 28, 2007.

  1. Nick Nelson

    Nick Nelson Guest

    I'm using VS2005 Pro and C#. I want to use the items selected in a multi
    select listbox as the filter for a gridview populated from a SQL Server
    table. The listbox contains organisation types (NGO, Governmental, Charitable
    etc) and I want users to be able to filter a gridview to show, for example,
    all NGO's and Charitable organisations.

    Any ideas on how to achieve this would be gratefully received.

    Thanks.
    --
    Nick
     
    Nick Nelson, Feb 28, 2007
    #1
    1. Advertising

  2. Hello Nick,

    From your description, you have an ASP.NET web page which contains GridView
    that use DataSource control to populate data, you also want to use a
    Multi-select ListBox as parameter source to filter the DataSource
    resultset, correct?

    Based on my experience, normally DataSource control's filterexpression only
    support DataSet resultset, just like add some select filter expression on
    an ADO.NET DataTAble. I'm wondering what's your current filter expression
    look like, and how did you define the parameter in the expression.
    Considered that the parameter value may require some customization on the
    selected value from ListBox(and multi-select mode), I think you will
    probably need to use the SqlDataSource.Filtering event do do some
    customization, such as get the selected items in ListBox and supply them as
    parameter into the SqlDataSource's filtering parameter collection.

    #SqlDataSource.Filtering Event
    http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldataso
    urce.filtering(VS.80).aspx

    If you feel this is an workable means and have any further questions on
    this, please feel free to post here.


    Sincerely,

    Steven Cheng

    Microsoft MSDN Online Support Lead



    ==================================================

    Get notification to my posts through email? Please refer to
    http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
    ications.



    Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
    where an initial response from the community or a Microsoft Support
    Engineer within 1 business day is acceptable. Please note that each follow
    up response may take approximately 2 business days as the support
    professional working with you may need further investigation to reach the
    most efficient resolution. The offering is not appropriate for situations
    that require urgent, real-time or phone-based interactions or complex
    project analysis and dump analysis issues. Issues of this nature are best
    handled working with a dedicated Microsoft Support Engineer by contacting
    Microsoft Customer Support Services (CSS) at
    http://msdn.microsoft.com/subscriptions/support/default.aspx.

    ==================================================



    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Steven Cheng[MSFT], Feb 28, 2007
    #2
    1. Advertising

  3. Nick Nelson

    Ram Guest

    On Feb 28, 2:26 pm, (Steven Cheng[MSFT])
    wrote:
    > Hello Nick,
    >
    > From your description, you have an ASP.NET web page which contains GridView
    > that use DataSource control to populate data, you also want to use a
    > Multi-select ListBox as parameter source to filter the DataSource
    > resultset, correct?
    >
    > Based on my experience, normally DataSource control's filterexpression only
    > support DataSet resultset, just like add some select filter expression on
    > an ADO.NET DataTAble. I'm wondering what's your current filter expression
    > look like, and how did you define the parameter in the expression.
    > Considered that the parameter value may require some customization on the
    > selected value from ListBox(and multi-select mode), I think you will
    > probably need to use the SqlDataSource.Filtering event do do some
    > customization, such as get the selected items in ListBox and supply them as
    > parameter into the SqlDataSource's filtering parameter collection.
    >
    > #SqlDataSource.Filtering Event http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sq...
    > urce.filtering(VS.80).aspx
    >
    > If you feel this is an workable means and have any further questions on
    > this, please feel free to post here.
    >
    > Sincerely,
    >
    > Steven Cheng
    >
    > Microsoft MSDN Online Support Lead
    >
    > ==================================================
    >
    > Get notification to my posts through email? Please refer tohttp://msdn.microsoft.com/subscriptions/managednewsgroups/default.asp...
    > ications.
    >
    > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
    > where an initial response from the community or a Microsoft Support
    > Engineer within 1 business day is acceptable. Please note that each follow
    > up response may take approximately 2 business days as the support
    > professional working with you may need further investigation to reach the
    > most efficient resolution. The offering is not appropriate for situations
    > that require urgent, real-time or phone-based interactions or complex
    > project analysis and dump analysis issues. Issues of this nature are best
    > handled working with a dedicated Microsoft Support Engineer by contacting
    > Microsoft Customer Support Services (CSS) athttp://msdn.microsoft.com/subscriptions/support/default.aspx.
    >
    > ==================================================
    >
    > This posting is provided "AS IS" with no warranties, and confers no rights.


    The same thing can be achieved using object data source
     
    Ram, Mar 1, 2007
    #3
  4. Hi Nick,

    Have you got any further ideas on this? If there is anything else we can
    help, please feel free to post here.

    Sincerely,

    Steven Cheng

    Microsoft MSDN Online Support Lead


    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Steven Cheng[MSFT], Mar 5, 2007
    #4
  5. Nick Nelson

    Nick Nelson Guest

    Steven,

    Apologies for the delay in replying, I had to do my annual accounts! You
    have described the issue accurately, but I had wondered if there was a
    recommended method for doing this before I started working on it.
    I 'm aware that a SQLDataSource can accept in multiple parameters via the
    FilterParameters and FilterExpression properties (I have done this using
    single parameters from a number of controls - dropdown lists) but cannot see
    how this works for multiple parameters from a single control.
    I'm happy to set up and use TableAdapters if this is required for a solution.

    Thanks for your help

    --
    Nick


    "Steven Cheng[MSFT]" wrote:

    > Hi Nick,
    >
    > Have you got any further ideas on this? If there is anything else we can
    > help, please feel free to post here.
    >
    > Sincerely,
    >
    > Steven Cheng
    >
    > Microsoft MSDN Online Support Lead
    >
    >
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    >
    >
     
    Nick Nelson, Mar 6, 2007
    #5
  6. Hello Nick,

    Thanks for your reply.

    For your scenario, you can simply add some code in the
    SqlDataSource.Filtering event, then construct a Filter expression string
    and assign it to the SqlDataSource control. e.g.

    ========aspx page===========
    <form id="form1" runat="server">
    <div>
    <asp:ListBox ID="lstSelected" runat="server"
    SelectionMode="Multiple">
    <asp:ListItem>name1</asp:ListItem>
    <asp:ListItem>name2</asp:ListItem>
    <asp:ListItem>name3</asp:ListItem>
    <asp:ListItem>name4</asp:ListItem>
    </asp:ListBox>
    <asp:Button ID="btnSelect" runat="server" Text="Select Button"
    OnClick="btnSelect_Click" />

    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:ASPNETTestDBConnectionString2 %>"
    SelectCommand="SELECT [id], [name], [description] FROM
    [RVTable]"
    FilterExpression="name='name1'OR name='name2'"
    OnFiltering="SqlDataSource1_Filtering"
    ></asp:SqlDataSource>

    <asp:GridView ID="GridView1" runat="server"
    AutoGenerateColumns="False" DataKeyNames="id" DataSourceID="SqlDataSource1">
    <Columns>
    <asp:BoundField DataField="id" HeaderText="id"
    InsertVisible="False" ReadOnly="True"
    SortExpression="id" />
    <asp:BoundField DataField="name" HeaderText="name"
    SortExpression="name" />
    <asp:BoundField DataField="description"
    HeaderText="description" SortExpression="description" />
    </Columns>
    </asp:GridView>

    </div>
    </form>
    ...............
    =================================
    =======code behind=====
    protected void SqlDataSource1_Filtering(object sender,
    SqlDataSourceFilteringEventArgs e)
    {
    string exp = string.Empty;
    foreach (ListItem item in lstSelected.Items)
    {
    if (item.Selected)
    {
    if (exp != string.Empty)
    {
    exp += " OR ";
    }
    exp += "name='" + item.Value + "'";
    }
    }

    if(exp != string.Empty)
    SqlDataSource1.FilterExpression = exp;

    }

    protected void btnSelect_Click(object sender, EventArgs e)
    {
    GridView1.DataBind();
    }
    ========================

    Hope this helps.

    Sincerely,

    Steven Cheng

    Microsoft MSDN Online Support Lead


    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Steven Cheng[MSFT], Mar 7, 2007
    #6
  7. Nick Nelson

    Nick Nelson Guest

    Steven,

    This worked perfectly. Many thanks for your help.
    --
    Nick


    "Steven Cheng[MSFT]" wrote:

    > Hello Nick,
    >
    > Thanks for your reply.
    >
    > For your scenario, you can simply add some code in the
    > SqlDataSource.Filtering event, then construct a Filter expression string
    > and assign it to the SqlDataSource control. e.g.
    >
    > ========aspx page===========
    > <form id="form1" runat="server">
    > <div>
    > <asp:ListBox ID="lstSelected" runat="server"
    > SelectionMode="Multiple">
    > <asp:ListItem>name1</asp:ListItem>
    > <asp:ListItem>name2</asp:ListItem>
    > <asp:ListItem>name3</asp:ListItem>
    > <asp:ListItem>name4</asp:ListItem>
    > </asp:ListBox>
    > <asp:Button ID="btnSelect" runat="server" Text="Select Button"
    > OnClick="btnSelect_Click" />
    >
    > <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    > ConnectionString="<%$ ConnectionStrings:ASPNETTestDBConnectionString2 %>"
    > SelectCommand="SELECT [id], [name], [description] FROM
    > [RVTable]"
    > FilterExpression="name='name1'OR name='name2'"
    > OnFiltering="SqlDataSource1_Filtering"
    > ></asp:SqlDataSource>

    > <asp:GridView ID="GridView1" runat="server"
    > AutoGenerateColumns="False" DataKeyNames="id" DataSourceID="SqlDataSource1">
    > <Columns>
    > <asp:BoundField DataField="id" HeaderText="id"
    > InsertVisible="False" ReadOnly="True"
    > SortExpression="id" />
    > <asp:BoundField DataField="name" HeaderText="name"
    > SortExpression="name" />
    > <asp:BoundField DataField="description"
    > HeaderText="description" SortExpression="description" />
    > </Columns>
    > </asp:GridView>
    >
    > </div>
    > </form>
    > ...............
    > =================================
    > =======code behind=====
    > protected void SqlDataSource1_Filtering(object sender,
    > SqlDataSourceFilteringEventArgs e)
    > {
    > string exp = string.Empty;
    > foreach (ListItem item in lstSelected.Items)
    > {
    > if (item.Selected)
    > {
    > if (exp != string.Empty)
    > {
    > exp += " OR ";
    > }
    > exp += "name='" + item.Value + "'";
    > }
    > }
    >
    > if(exp != string.Empty)
    > SqlDataSource1.FilterExpression = exp;
    >
    > }
    >
    > protected void btnSelect_Click(object sender, EventArgs e)
    > {
    > GridView1.DataBind();
    > }
    > ========================
    >
    > Hope this helps.
    >
    > Sincerely,
    >
    > Steven Cheng
    >
    > Microsoft MSDN Online Support Lead
    >
    >
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    >
    >
     
    Nick Nelson, Mar 7, 2007
    #7
  8. You're welcome ;-)

    Sincerely,

    Steven Cheng

    Microsoft MSDN Online Support Lead
     
    Steven Cheng[MSFT], Mar 8, 2007
    #8
  9. Nick Nelson

    chiefsss Guest

    The filter works fine if the fields are in the ending result set, I have a
    problem where the field I want to filter by is not in the end result set
    (select query has a group by clause).

    So for example, if I have a list of addresses that I want to get summarized
    counts filtered by state and state is a field in the result set, it works
    fine, but if I want to filter on zip and zip is not in the final set as a
    field, it only filters by the last zip obtained as a result of the group-by
    for each resulting record (always the same?), not the various zip(s) chosen
    in the listbox.

    Is there a way to fix this?
    Thanks


    "Nick Nelson" wrote:

    > Steven,
    >
    > This worked perfectly. Many thanks for your help.
    > --
    > Nick
    >
    >
    > "Steven Cheng[MSFT]" wrote:
    >
    > > Hello Nick,
    > >
    > > Thanks for your reply.
    > >
    > > For your scenario, you can simply add some code in the
    > > SqlDataSource.Filtering event, then construct a Filter expression string
    > > and assign it to the SqlDataSource control. e.g.
    > >
    > > ========aspx page===========
    > > <form id="form1" runat="server">
    > > <div>
    > > <asp:ListBox ID="lstSelected" runat="server"
    > > SelectionMode="Multiple">
    > > <asp:ListItem>name1</asp:ListItem>
    > > <asp:ListItem>name2</asp:ListItem>
    > > <asp:ListItem>name3</asp:ListItem>
    > > <asp:ListItem>name4</asp:ListItem>
    > > </asp:ListBox>
    > > <asp:Button ID="btnSelect" runat="server" Text="Select Button"
    > > OnClick="btnSelect_Click" />
    > >
    > > <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    > > ConnectionString="<%$ ConnectionStrings:ASPNETTestDBConnectionString2 %>"
    > > SelectCommand="SELECT [id], [name], [description] FROM
    > > [RVTable]"
    > > FilterExpression="name='name1'OR name='name2'"
    > > OnFiltering="SqlDataSource1_Filtering"
    > > ></asp:SqlDataSource>

    > > <asp:GridView ID="GridView1" runat="server"
    > > AutoGenerateColumns="False" DataKeyNames="id" DataSourceID="SqlDataSource1">
    > > <Columns>
    > > <asp:BoundField DataField="id" HeaderText="id"
    > > InsertVisible="False" ReadOnly="True"
    > > SortExpression="id" />
    > > <asp:BoundField DataField="name" HeaderText="name"
    > > SortExpression="name" />
    > > <asp:BoundField DataField="description"
    > > HeaderText="description" SortExpression="description" />
    > > </Columns>
    > > </asp:GridView>
    > >
    > > </div>
    > > </form>
    > > ...............
    > > =================================
    > > =======code behind=====
    > > protected void SqlDataSource1_Filtering(object sender,
    > > SqlDataSourceFilteringEventArgs e)
    > > {
    > > string exp = string.Empty;
    > > foreach (ListItem item in lstSelected.Items)
    > > {
    > > if (item.Selected)
    > > {
    > > if (exp != string.Empty)
    > > {
    > > exp += " OR ";
    > > }
    > > exp += "name='" + item.Value + "'";
    > > }
    > > }
    > >
    > > if(exp != string.Empty)
    > > SqlDataSource1.FilterExpression = exp;
    > >
    > > }
    > >
    > > protected void btnSelect_Click(object sender, EventArgs e)
    > > {
    > > GridView1.DataBind();
    > > }
    > > ========================
    > >
    > > Hope this helps.
    > >
    > > Sincerely,
    > >
    > > Steven Cheng
    > >
    > > Microsoft MSDN Online Support Lead
    > >
    > >
    > > This posting is provided "AS IS" with no warranties, and confers no rights.
    > >
    > >
     
    chiefsss, Aug 24, 2009
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. JIK

    multiselect dropdown

    JIK, Feb 6, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    8,117
    PeterBrunone
    May 13, 2009
  2. Lenard Gunda
    Replies:
    3
    Views:
    1,271
    =?Utf-8?B?S2FubmFuLlYgW01DU0QubmV0XQ==?=
    Aug 17, 2005
  3. Jeff Thur
    Replies:
    2
    Views:
    211
    Kostadin Kostov
    Feb 14, 2005
  4. Martin

    Unexpected behaviour of Listbox MultiSelect

    Martin, Oct 18, 2005, in forum: ASP .Net Web Controls
    Replies:
    5
    Views:
    346
    Steven Cheng[MSFT]
    Jan 24, 2006
  5. Jay Pondy

    ObjectDataSource / MultiSelect ListBox

    Jay Pondy, Sep 22, 2007, in forum: ASP .Net Web Controls
    Replies:
    1
    Views:
    524
    Jay Pondy
    Sep 22, 2007
Loading...

Share This Page