Listbox Multiselect as filter for Gridview

N

Nick Nelson

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

Steven Cheng[MSFT]

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

Ram

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
 
S

Steven Cheng[MSFT]

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

Nick Nelson

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
 
S

Steven Cheng[MSFT]

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

Nick Nelson

Steven,

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


Steven Cheng said:
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.
 
S

Steven Cheng[MSFT]

You're welcome ;-)

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead
 
C

chiefsss

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 said:
Steven,

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


Steven Cheng said:
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.
 

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. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top