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 Nelson, Feb 28, 2007
    #1
    1. Advertisements

  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. Advertisements

  3. Nick Nelson

    Ram Guest

    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 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: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


     
    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


     
    chiefsss, Aug 24, 2009
    #9
    1. Advertisements

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.