473,699 Members | 2,404 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Filtering data inside the Gridview Control?

Hello,

Does anyone have an idea on how I can filter the data in the gridview
control that was returned by an sql query?

I have a gridview that works fine when I populate it with data. Now I want
to look at that data and filter it based on what is in it.

I know that this could have been done with data sets and data views in
asp.net 1.1 but how is this done now in asp.net 2.0?

Is there a way to do this?

Thanks,

J
Dec 15 '05 #1
7 14806
Hi J,

Welcome to ASPNET newsgroup.
As for the filtering data displayed in the asp.net GridView control
question, based on my understanding, we should do this at DataSource
level... I think you're using a SqlDataSource to provide the data records
to the GridView , yes? If so, you can have alook at the following msdn
reference about using filtering function of the SqlDataSource.. ..:

#How to: Enable Filtering for SqlDataSource Controls (Visual Studio)
http://msdn2.microsoft.com/en-us/library/ms178306.aspx

e.g:

<asp:CheckBox ID="Discontinue d" runat="server" AutoPostBack="T rue" />
<asp:SqlDataSou rce ID="SqlDataSour ce1" runat="server" ConnectionStrin g="<%$
ConnectionStrin gs:LocalNorthWi nd %>"
SelectCommand=" SELECT [ProductID], [ProductName],
[Discontinued], [ReorderLevel], [CategoryID] FROM [Products]"
FilterExpressio n="Discontinu ed = {0}"
OnSelected="Sql DataSource1_Sel ected">
<FilterParamete rs>
<asp:ControlPar ameter ControlID="Disc ontinued"
Type="boolean"
Name="disc" PropertyName="C hecked" />
</FilterParameter s>
</asp:SqlDataSour ce>
The above sql datasource setting the filter as "Discontinu ed = {0}" , and
the parameter is defined in the <Filterparamete rs> collection... The
filter experssion is just the same as the Filter Expression we set for
DataView class programmaticall y. And this filtering is done through the
DataSet returned by the DataSourceContr ol rather than filtering at database
level ( the "Where" statement
of the DataSoureContro l is used to done database level filtering .....

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)


--------------------
| Reply-To: <wa********@new sgroups.nospam>
| From: <wa********@new sgroups.nospam>
| Subject: Filtering data inside the Gridview Control?
| Date: Wed, 14 Dec 2005 19:25:24 -0800
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| Message-ID: <e0************ *@TK2MSFTNGP09. phx.gbl>
| Newsgroups: microsoft.publi c.dotnet.framew ork.aspnet
| NNTP-Posting-Host: ip-206.159.118.137 .hdiss.net 206.159.118.137
| Path: TK2MSFTNGXA02.p hx.gbl!TK2MSFTN GP08.phx.gbl!TK 2MSFTNGP09.phx. gbl
| Xref: TK2MSFTNGXA02.p hx.gbl
microsoft.publi c.dotnet.framew ork.aspnet:3650 02
| X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.aspnet
|
| Hello,
|
| Does anyone have an idea on how I can filter the data in the gridview
| control that was returned by an sql query?
|
| I have a gridview that works fine when I populate it with data. Now I
want
| to look at that data and filter it based on what is in it.
|
| I know that this could have been done with data sets and data views in
| asp.net 1.1 but how is this done now in asp.net 2.0?
|
| Is there a way to do this?
|
| Thanks,
|
| J
|
|
|

Dec 15 '05 #2
Steven,

Cool that helped me with filtering the rows but I have one other question.

How do I search through the gridview? Would I do this at the sqldatasource
level?

I figured that I sould search with the datatable.selec t but how do I access
the datatable of the sqldatasource?

Thanks for your help

J
"Steven Cheng[MSFT]" <st*****@online .microsoft.com> wrote in message
news:5w******** ******@TK2MSFTN GXA02.phx.gbl.. .
Hi J,

Welcome to ASPNET newsgroup.
As for the filtering data displayed in the asp.net GridView control
question, based on my understanding, we should do this at DataSource
level... I think you're using a SqlDataSource to provide the data records
to the GridView , yes? If so, you can have alook at the following msdn
reference about using filtering function of the SqlDataSource.. ..:

#How to: Enable Filtering for SqlDataSource Controls (Visual Studio)
http://msdn2.microsoft.com/en-us/library/ms178306.aspx

e.g:

<asp:CheckBox ID="Discontinue d" runat="server" AutoPostBack="T rue" />
<asp:SqlDataSou rce ID="SqlDataSour ce1" runat="server"
ConnectionStrin g="<%$
ConnectionStrin gs:LocalNorthWi nd %>"
SelectCommand=" SELECT [ProductID], [ProductName],
[Discontinued], [ReorderLevel], [CategoryID] FROM [Products]"
FilterExpressio n="Discontinu ed = {0}"
OnSelected="Sql DataSource1_Sel ected">
<FilterParamete rs>
<asp:ControlPar ameter ControlID="Disc ontinued"
Type="boolean"
Name="disc" PropertyName="C hecked" />
</FilterParameter s>
</asp:SqlDataSour ce>
The above sql datasource setting the filter as "Discontinu ed = {0}" , and
the parameter is defined in the <Filterparamete rs> collection... The
filter experssion is just the same as the Filter Expression we set for
DataView class programmaticall y. And this filtering is done through the
DataSet returned by the DataSourceContr ol rather than filtering at
database
level ( the "Where" statement
of the DataSoureContro l is used to done database level filtering .....

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)


--------------------
| Reply-To: <wa********@new sgroups.nospam>
| From: <wa********@new sgroups.nospam>
| Subject: Filtering data inside the Gridview Control?
| Date: Wed, 14 Dec 2005 19:25:24 -0800
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| Message-ID: <e0************ *@TK2MSFTNGP09. phx.gbl>
| Newsgroups: microsoft.publi c.dotnet.framew ork.aspnet
| NNTP-Posting-Host: ip-206.159.118.137 .hdiss.net 206.159.118.137
| Path: TK2MSFTNGXA02.p hx.gbl!TK2MSFTN GP08.phx.gbl!TK 2MSFTNGP09.phx. gbl
| Xref: TK2MSFTNGXA02.p hx.gbl
microsoft.publi c.dotnet.framew ork.aspnet:3650 02
| X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.aspnet
|
| Hello,
|
| Does anyone have an idea on how I can filter the data in the gridview
| control that was returned by an sql query?
|
| I have a gridview that works fine when I populate it with data. Now I
want
| to look at that data and filter it based on what is in it.
|
| I know that this could have been done with data sets and data views in
| asp.net 1.1 but how is this done now in asp.net 2.0?
|
| Is there a way to do this?
|
| Thanks,
|
| J
|
|
|

Dec 15 '05 #3
Hi J,

Thanks for the response. For searching, I'm afraid we have to do it at
datasource level since the GridView dosn't maintain orginal data records
after databinding (only displaying databound data......). Also, the
DataSource control is mainly used to provide statically data retrieveing
and work with databound controls, so we can manually call its methods to
retrieve the Selected data, but can not affect the way DataBound control
use it (we can only apply some parameters ...... in it...)

So for your scenario, you can try manually call the SqlDataSource control's
Select method with proper parameters then the returned object is the
datasouce (by default it is a DataView since the DataSourceMode is set to
"DataSet" ). e.g:

DataView dv = SqlDataSource1. Select(DataSour ceSelectArgumen ts.Empty) as
DataView;
Response.Write( "<br>Return ed: " + dv.Table.Select (.....) );

Also, for such scenario, it is prefered that you not use DataSourceContr ol,
just programmaticall y retrieve the DataSet/DataTable your self and stored
in Cache, so that you can reuse it for searching later ......

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

--------------------
| Reply-To: <wa********@new sgroups.nospam>
| From: <wa********@new sgroups.nospam>
| References: <e0************ *@TK2MSFTNGP09. phx.gbl>
<5w************ **@TK2MSFTNGXA0 2.phx.gbl>
| Subject: Re: Filtering data inside the Gridview Control?
| Date: Thu, 15 Dec 2005 01:48:11 -0800
| Lines: 110
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| Message-ID: <ep************ **@TK2MSFTNGP10 .phx.gbl>
| Newsgroups: microsoft.publi c.dotnet.framew ork.aspnet
| NNTP-Posting-Host: ip-206.159.118.137 .hdiss.net 206.159.118.137
| Path: TK2MSFTNGXA02.p hx.gbl!TK2MSFTN GP08.phx.gbl!TK 2MSFTNGP10.phx. gbl
| Xref: TK2MSFTNGXA02.p hx.gbl
microsoft.publi c.dotnet.framew ork.aspnet:3650 43
| X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.aspnet
|
| Steven,
|
| Cool that helped me with filtering the rows but I have one other question.
|
| How do I search through the gridview? Would I do this at the
sqldatasource
| level?
|
| I figured that I sould search with the datatable.selec t but how do I
access
| the datatable of the sqldatasource?
|
| Thanks for your help
|
| J
|
|
| "Steven Cheng[MSFT]" <st*****@online .microsoft.com> wrote in message
| news:5w******** ******@TK2MSFTN GXA02.phx.gbl.. .
| > Hi J,
| >
| > Welcome to ASPNET newsgroup.
| > As for the filtering data displayed in the asp.net GridView control
| > question, based on my understanding, we should do this at DataSource
| > level... I think you're using a SqlDataSource to provide the data
records
| > to the GridView , yes? If so, you can have alook at the following msdn
| > reference about using filtering function of the SqlDataSource.. ..:
| >
| > #How to: Enable Filtering for SqlDataSource Controls (Visual Studio)
| > http://msdn2.microsoft.com/en-us/library/ms178306.aspx
| >
| > e.g:
| >
| > <asp:CheckBox ID="Discontinue d" runat="server" AutoPostBack="T rue" />
| > <asp:SqlDataSou rce ID="SqlDataSour ce1" runat="server"
| > ConnectionStrin g="<%$
| > ConnectionStrin gs:LocalNorthWi nd %>"
| > SelectCommand=" SELECT [ProductID], [ProductName],
| > [Discontinued], [ReorderLevel], [CategoryID] FROM [Products]"
| > FilterExpressio n="Discontinu ed = {0}"
| > OnSelected="Sql DataSource1_Sel ected">
| > <FilterParamete rs>
| > <asp:ControlPar ameter ControlID="Disc ontinued"
| > Type="boolean"
| > Name="disc" PropertyName="C hecked" />
| > </FilterParameter s>
| > </asp:SqlDataSour ce>
| >
| >
| > The above sql datasource setting the filter as "Discontinu ed = {0}" ,
and
| > the parameter is defined in the <Filterparamete rs> collection... The
| > filter experssion is just the same as the Filter Expression we set for
| > DataView class programmaticall y. And this filtering is done through the
| > DataSet returned by the DataSourceContr ol rather than filtering at
| > database
| > level ( the "Where" statement
| > of the DataSoureContro l is used to done database level filtering .....
| >
| > Thanks,
| >
| > Steven Cheng
| > Microsoft Online Support
| >
| > Get Secure! www.microsoft.com/security
| > (This posting is provided "AS IS", with no warranties, and confers no
| > rights.)
| >
| >
| >
| >
| > --------------------
| > | Reply-To: <wa********@new sgroups.nospam>
| > | From: <wa********@new sgroups.nospam>
| > | Subject: Filtering data inside the Gridview Control?
| > | Date: Wed, 14 Dec 2005 19:25:24 -0800
| > | Lines: 18
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | X-RFC2646: Format=Flowed; Original
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | Message-ID: <e0************ *@TK2MSFTNGP09. phx.gbl>
| > | Newsgroups: microsoft.publi c.dotnet.framew ork.aspnet
| > | NNTP-Posting-Host: ip-206.159.118.137 .hdiss.net 206.159.118.137
| > | Path: TK2MSFTNGXA02.p hx.gbl!TK2MSFTN GP08.phx.gbl!TK 2MSFTNGP09.phx. gbl
| > | Xref: TK2MSFTNGXA02.p hx.gbl
| > microsoft.publi c.dotnet.framew ork.aspnet:3650 02
| > | X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.aspnet
| > |
| > | Hello,
| > |
| > | Does anyone have an idea on how I can filter the data in the gridview
| > | control that was returned by an sql query?
| > |
| > | I have a gridview that works fine when I populate it with data. Now I
| > want
| > | to look at that data and filter it based on what is in it.
| > |
| > | I know that this could have been done with data sets and data views in
| > | asp.net 1.1 but how is this done now in asp.net 2.0?
| > |
| > | Is there a way to do this?
| > |
| > | Thanks,
| > |
| > | J
| > |
| > |
| > |
| >
|
|
|

Dec 16 '05 #4
Steven,

I was told by someone that I should just loop through the gridview and get
the data that way. Is this incorrect?

The way that you explain makes sense. Is the select method expression just
like SQL scripts? Because what I really need is to be able to do something
like, "SELECT DISTINCT column FROM table WHERE column='column' ". For the
column that I want the data from, there are going to be many of the same
values. All I want is to know what ones are there. Therefore I dont need
duplicates. So if there were:

[column1]
hat
car
car
house
hat
hat
hat
car

It would return back to me:

hat
car
house

I am then going to take these values and put them into a datalist control
that will display these as hyperlinks. Then when the user clicks on the
hyperlink I can use that value as criteria for doing an sql query to the DB
thus repopulating the gridview and repopulating the datalist that represent
the values to filter by based on what was returned by the last sql query to
the DB.

I hope that makes sense.

I will try what you suggested and let you know how it works.

Thanks,

J

"Steven Cheng[MSFT]" <st*****@online .microsoft.com> wrote in message
news:dU******** ******@TK2MSFTN GXA02.phx.gbl.. .
Hi J,

Thanks for the response. For searching, I'm afraid we have to do it at
datasource level since the GridView dosn't maintain orginal data records
after databinding (only displaying databound data......). Also, the
DataSource control is mainly used to provide statically data retrieveing
and work with databound controls, so we can manually call its methods to
retrieve the Selected data, but can not affect the way DataBound control
use it (we can only apply some parameters ...... in it...)

So for your scenario, you can try manually call the SqlDataSource
control's
Select method with proper parameters then the returned object is the
datasouce (by default it is a DataView since the DataSourceMode is set to
"DataSet" ). e.g:

DataView dv = SqlDataSource1. Select(DataSour ceSelectArgumen ts.Empty) as
DataView;
Response.Write( "<br>Return ed: " + dv.Table.Select (.....) );

Also, for such scenario, it is prefered that you not use
DataSourceContr ol,
just programmaticall y retrieve the DataSet/DataTable your self and stored
in Cache, so that you can reuse it for searching later ......

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

--------------------
| Reply-To: <wa********@new sgroups.nospam>
| From: <wa********@new sgroups.nospam>
| References: <e0************ *@TK2MSFTNGP09. phx.gbl>
<5w************ **@TK2MSFTNGXA0 2.phx.gbl>
| Subject: Re: Filtering data inside the Gridview Control?
| Date: Thu, 15 Dec 2005 01:48:11 -0800
| Lines: 110
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| Message-ID: <ep************ **@TK2MSFTNGP10 .phx.gbl>
| Newsgroups: microsoft.publi c.dotnet.framew ork.aspnet
| NNTP-Posting-Host: ip-206.159.118.137 .hdiss.net 206.159.118.137
| Path: TK2MSFTNGXA02.p hx.gbl!TK2MSFTN GP08.phx.gbl!TK 2MSFTNGP10.phx. gbl
| Xref: TK2MSFTNGXA02.p hx.gbl
microsoft.publi c.dotnet.framew ork.aspnet:3650 43
| X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.aspnet
|
| Steven,
|
| Cool that helped me with filtering the rows but I have one other
question.
|
| How do I search through the gridview? Would I do this at the
sqldatasource
| level?
|
| I figured that I sould search with the datatable.selec t but how do I
access
| the datatable of the sqldatasource?
|
| Thanks for your help
|
| J
|
|
| "Steven Cheng[MSFT]" <st*****@online .microsoft.com> wrote in message
| news:5w******** ******@TK2MSFTN GXA02.phx.gbl.. .
| > Hi J,
| >
| > Welcome to ASPNET newsgroup.
| > As for the filtering data displayed in the asp.net GridView control
| > question, based on my understanding, we should do this at DataSource
| > level... I think you're using a SqlDataSource to provide the data
records
| > to the GridView , yes? If so, you can have alook at the following
msdn
| > reference about using filtering function of the SqlDataSource.. ..:
| >
| > #How to: Enable Filtering for SqlDataSource Controls (Visual Studio)
| > http://msdn2.microsoft.com/en-us/library/ms178306.aspx
| >
| > e.g:
| >
| > <asp:CheckBox ID="Discontinue d" runat="server" AutoPostBack="T rue" />
| > <asp:SqlDataSou rce ID="SqlDataSour ce1" runat="server"
| > ConnectionStrin g="<%$
| > ConnectionStrin gs:LocalNorthWi nd %>"
| > SelectCommand=" SELECT [ProductID], [ProductName],
| > [Discontinued], [ReorderLevel], [CategoryID] FROM [Products]"
| > FilterExpressio n="Discontinu ed = {0}"
| > OnSelected="Sql DataSource1_Sel ected">
| > <FilterParamete rs>
| > <asp:ControlPar ameter ControlID="Disc ontinued"
| > Type="boolean"
| > Name="disc" PropertyName="C hecked" />
| > </FilterParameter s>
| > </asp:SqlDataSour ce>
| >
| >
| > The above sql datasource setting the filter as "Discontinu ed = {0}" ,
and
| > the parameter is defined in the <Filterparamete rs> collection... The
| > filter experssion is just the same as the Filter Expression we set for
| > DataView class programmaticall y. And this filtering is done through
the
| > DataSet returned by the DataSourceContr ol rather than filtering at
| > database
| > level ( the "Where" statement
| > of the DataSoureContro l is used to done database level filtering .....
| >
| > Thanks,
| >
| > Steven Cheng
| > Microsoft Online Support
| >
| > Get Secure! www.microsoft.com/security
| > (This posting is provided "AS IS", with no warranties, and confers no
| > rights.)
| >
| >
| >
| >
| > --------------------
| > | Reply-To: <wa********@new sgroups.nospam>
| > | From: <wa********@new sgroups.nospam>
| > | Subject: Filtering data inside the Gridview Control?
| > | Date: Wed, 14 Dec 2005 19:25:24 -0800
| > | Lines: 18
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | X-RFC2646: Format=Flowed; Original
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | Message-ID: <e0************ *@TK2MSFTNGP09. phx.gbl>
| > | Newsgroups: microsoft.publi c.dotnet.framew ork.aspnet
| > | NNTP-Posting-Host: ip-206.159.118.137 .hdiss.net 206.159.118.137
| > | Path:
TK2MSFTNGXA02.p hx.gbl!TK2MSFTN GP08.phx.gbl!TK 2MSFTNGP09.phx. gbl
| > | Xref: TK2MSFTNGXA02.p hx.gbl
| > microsoft.publi c.dotnet.framew ork.aspnet:3650 02
| > | X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.aspnet
| > |
| > | Hello,
| > |
| > | Does anyone have an idea on how I can filter the data in the
gridview
| > | control that was returned by an sql query?
| > |
| > | I have a gridview that works fine when I populate it with data. Now
I
| > want
| > | to look at that data and filter it based on what is in it.
| > |
| > | I know that this could have been done with data sets and data views
in
| > | asp.net 1.1 but how is this done now in asp.net 2.0?
| > |
| > | Is there a way to do this?
| > |
| > | Thanks,
| > |
| > | J
| > |
| > |
| > |
| >
|
|
|

Dec 18 '05 #5
Hi J,

Thanks for your response.
Of course manually loop the gridView and extract values from each row is
one possible appraoch, but that's not a good idea, either hard to code also
have poor performance....
As for the further question you mentioned, I think it's better that you
move that query into a separate new datasource object, so that we can cache
that query to improve performance(I think those query of DISTINCT values
should be cachable....). We can use the Select statement and Select
parameters to define parameter based query like:

=============== ========
<asp:SqlDataSou rce ID="SqlDataSour ce2" runat="server" ConnectionStrin g="<%$
ConnectionStrin gs:LocalNorthWi nd %>"
SelectCommand=" SELECT [Discontinued], [ProductID],
[ProductName], [UnitPrice] FROM [Products] WHERE ([Discontinued] =
@Discontinued)" >
<SelectParamete rs>
<asp:Paramete r DefaultValue="t rue" Name="Discontin ued"
Type="Boolean" />
</SelectParameter s>
</asp:SqlDataSour ce>
<asp:GridView ID="GridView2" runat="server"
AutoGenerateCol umns="False" DataKeyNames="P roductID"
DataSourceID="S qlDataSource2">
<Columns>
<asp:CheckBoxFi eld DataField="Disc ontinued"
HeaderText="Dis continued" SortExpression= "Discontinu ed" />
<asp:BoundFie ld DataField="Prod uctID"
HeaderText="Pro ductID" InsertVisible=" False"
ReadOnly="True" SortExpression= "ProductID" />
<asp:BoundFie ld DataField="Prod uctName"
HeaderText="Pro ductName" SortExpression= "ProductNam e" />
<asp:BoundFie ld DataField="Unit Price"
HeaderText="Uni tPrice" SortExpression= "UnitPrice" />
</Columns>
</asp:GridView>
=============== =======

And we can also programmaticall y call the datasource.sele ct method with the
certain parameter instances as I've mentioned in the former message....

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

--------------------
| Reply-To: <wa********@new sgroups.nospam>
| From: <wa********@new sgroups.nospam>
| References: <e0************ *@TK2MSFTNGP09. phx.gbl>
<5w************ **@TK2MSFTNGXA0 2.phx.gbl>
<ep************ **@TK2MSFTNGP10 .phx.gbl>
<dU************ **@TK2MSFTNGXA0 2.phx.gbl>
| Subject: Re: Filtering data inside the Gridview Control?
| Date: Sun, 18 Dec 2005 14:48:48 -0800
| Lines: 231
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| Message-ID: <u$************ **@TK2MSFTNGP09 .phx.gbl>
| Newsgroups: microsoft.publi c.dotnet.framew ork.aspnet
| NNTP-Posting-Host: ip-206.159.118.137 .hdiss.net 206.159.118.137
| Path: TK2MSFTNGXA02.p hx.gbl!TK2MSFTN GP08.phx.gbl!TK 2MSFTNGP09.phx. gbl
| Xref: TK2MSFTNGXA02.p hx.gbl
microsoft.publi c.dotnet.framew ork.aspnet:3656 06
| X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.aspnet
|
| Steven,
|
| I was told by someone that I should just loop through the gridview and
get
| the data that way. Is this incorrect?
|
| The way that you explain makes sense. Is the select method expression
just
| like SQL scripts? Because what I really need is to be able to do
something
| like, "SELECT DISTINCT column FROM table WHERE column='column' ". For the
| column that I want the data from, there are going to be many of the same
| values. All I want is to know what ones are there. Therefore I dont
need
| duplicates. So if there were:
|
| [column1]
| hat
| car
| car
| house
| hat
| hat
| hat
| car
|
| It would return back to me:
|
| hat
| car
| house
|
| I am then going to take these values and put them into a datalist control
| that will display these as hyperlinks. Then when the user clicks on the
| hyperlink I can use that value as criteria for doing an sql query to the
DB
| thus repopulating the gridview and repopulating the datalist that
represent
| the values to filter by based on what was returned by the last sql query
to
| the DB.
|
| I hope that makes sense.
|
| I will try what you suggested and let you know how it works.
|
| Thanks,
|
| J
|
|
|
|
|
| "Steven Cheng[MSFT]" <st*****@online .microsoft.com> wrote in message
| news:dU******** ******@TK2MSFTN GXA02.phx.gbl.. .
| > Hi J,
| >
| > Thanks for the response. For searching, I'm afraid we have to do it at
| > datasource level since the GridView dosn't maintain orginal data records
| > after databinding (only displaying databound data......). Also, the
| > DataSource control is mainly used to provide statically data retrieveing
| > and work with databound controls, so we can manually call its methods to
| > retrieve the Selected data, but can not affect the way DataBound control
| > use it (we can only apply some parameters ...... in it...)
| >
| > So for your scenario, you can try manually call the SqlDataSource
| > control's
| > Select method with proper parameters then the returned object is the
| > datasouce (by default it is a DataView since the DataSourceMode is set
to
| > "DataSet" ). e.g:
| >
| > DataView dv = SqlDataSource1. Select(DataSour ceSelectArgumen ts.Empty) as
| > DataView;
| > Response.Write( "<br>Return ed: " + dv.Table.Select (.....) );
| >
| > Also, for such scenario, it is prefered that you not use
| > DataSourceContr ol,
| > just programmaticall y retrieve the DataSet/DataTable your self and
stored
| > in Cache, so that you can reuse it for searching later ......
| >
| > Thanks,
| >
| > Steven Cheng
| > Microsoft Online Support
| >
| > Get Secure! www.microsoft.com/security
| > (This posting is provided "AS IS", with no warranties, and confers no
| > rights.)
| >
| >
| >
| > --------------------
| > | Reply-To: <wa********@new sgroups.nospam>
| > | From: <wa********@new sgroups.nospam>
| > | References: <e0************ *@TK2MSFTNGP09. phx.gbl>
| > <5w************ **@TK2MSFTNGXA0 2.phx.gbl>
| > | Subject: Re: Filtering data inside the Gridview Control?
| > | Date: Thu, 15 Dec 2005 01:48:11 -0800
| > | Lines: 110
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | X-RFC2646: Format=Flowed; Original
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | Message-ID: <ep************ **@TK2MSFTNGP10 .phx.gbl>
| > | Newsgroups: microsoft.publi c.dotnet.framew ork.aspnet
| > | NNTP-Posting-Host: ip-206.159.118.137 .hdiss.net 206.159.118.137
| > | Path: TK2MSFTNGXA02.p hx.gbl!TK2MSFTN GP08.phx.gbl!TK 2MSFTNGP10.phx. gbl
| > | Xref: TK2MSFTNGXA02.p hx.gbl
| > microsoft.publi c.dotnet.framew ork.aspnet:3650 43
| > | X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.aspnet
| > |
| > | Steven,
| > |
| > | Cool that helped me with filtering the rows but I have one other
| > question.
| > |
| > | How do I search through the gridview? Would I do this at the
| > sqldatasource
| > | level?
| > |
| > | I figured that I sould search with the datatable.selec t but how do I
| > access
| > | the datatable of the sqldatasource?
| > |
| > | Thanks for your help
| > |
| > | J
| > |
| > |
| > | "Steven Cheng[MSFT]" <st*****@online .microsoft.com> wrote in message
| > | news:5w******** ******@TK2MSFTN GXA02.phx.gbl.. .
| > | > Hi J,
| > | >
| > | > Welcome to ASPNET newsgroup.
| > | > As for the filtering data displayed in the asp.net GridView control
| > | > question, based on my understanding, we should do this at DataSource
| > | > level... I think you're using a SqlDataSource to provide the data
| > records
| > | > to the GridView , yes? If so, you can have alook at the following
| > msdn
| > | > reference about using filtering function of the SqlDataSource.. ..:
| > | >
| > | > #How to: Enable Filtering for SqlDataSource Controls (Visual Studio)
| > | > http://msdn2.microsoft.com/en-us/library/ms178306.aspx
| > | >
| > | > e.g:
| > | >
| > | > <asp:CheckBox ID="Discontinue d" runat="server" AutoPostBack="T rue"
/>
| > | > <asp:SqlDataSou rce ID="SqlDataSour ce1" runat="server"
| > | > ConnectionStrin g="<%$
| > | > ConnectionStrin gs:LocalNorthWi nd %>"
| > | > SelectCommand=" SELECT [ProductID], [ProductName],
| > | > [Discontinued], [ReorderLevel], [CategoryID] FROM [Products]"
| > | > FilterExpressio n="Discontinu ed = {0}"
| > | > OnSelected="Sql DataSource1_Sel ected">
| > | > <FilterParamete rs>
| > | > <asp:ControlPar ameter ControlID="Disc ontinued"
| > | > Type="boolean"
| > | > Name="disc" PropertyName="C hecked" />
| > | > </FilterParameter s>
| > | > </asp:SqlDataSour ce>
| > | >
| > | >
| > | > The above sql datasource setting the filter as "Discontinu ed = {0}"
,
| > and
| > | > the parameter is defined in the <Filterparamete rs> collection...
The
| > | > filter experssion is just the same as the Filter Expression we set
for
| > | > DataView class programmaticall y. And this filtering is done through
| > the
| > | > DataSet returned by the DataSourceContr ol rather than filtering at
| > | > database
| > | > level ( the "Where" statement
| > | > of the DataSoureContro l is used to done database level filtering
.....
| > | >
| > | > Thanks,
| > | >
| > | > Steven Cheng
| > | > Microsoft Online Support
| > | >
| > | > Get Secure! www.microsoft.com/security
| > | > (This posting is provided "AS IS", with no warranties, and confers
no
| > | > rights.)
| > | >
| > | >
| > | >
| > | >
| > | > --------------------
| > | > | Reply-To: <wa********@new sgroups.nospam>
| > | > | From: <wa********@new sgroups.nospam>
| > | > | Subject: Filtering data inside the Gridview Control?
| > | > | Date: Wed, 14 Dec 2005 19:25:24 -0800
| > | > | Lines: 18
| > | > | X-Priority: 3
| > | > | X-MSMail-Priority: Normal
| > | > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | > | X-RFC2646: Format=Flowed; Original
| > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | > | Message-ID: <e0************ *@TK2MSFTNGP09. phx.gbl>
| > | > | Newsgroups: microsoft.publi c.dotnet.framew ork.aspnet
| > | > | NNTP-Posting-Host: ip-206.159.118.137 .hdiss.net 206.159.118.137
| > | > | Path:
| > TK2MSFTNGXA02.p hx.gbl!TK2MSFTN GP08.phx.gbl!TK 2MSFTNGP09.phx. gbl
| > | > | Xref: TK2MSFTNGXA02.p hx.gbl
| > | > microsoft.publi c.dotnet.framew ork.aspnet:3650 02
| > | > | X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.aspnet
| > | > |
| > | > | Hello,
| > | > |
| > | > | Does anyone have an idea on how I can filter the data in the
| > gridview
| > | > | control that was returned by an sql query?
| > | > |
| > | > | I have a gridview that works fine when I populate it with data.
Now
| > I
| > | > want
| > | > | to look at that data and filter it based on what is in it.
| > | > |
| > | > | I know that this could have been done with data sets and data
views
| > in
| > | > | asp.net 1.1 but how is this done now in asp.net 2.0?
| > | > |
| > | > | Is there a way to do this?
| > | > |
| > | > | Thanks,
| > | > |
| > | > | J
| > | > |
| > | > |
| > | > |
| > | >
| > |
| > |
| > |
| >
|
|
|

Dec 19 '05 #6
Ok,

I figured it out. Here is what I did:

1. I bound both the grid view control and the list boxes to a dataset that
I created which pulls the data from the DB based on an SQL Statement.
2. I created a custom class that finds only distinct rows based on a table
and column. I call this class as the datasource of the data list controls
that I have on the page displaying the filtering options.
3. The SQL SELECT statement gets pulled from the querystring values in the
url. In other words the querystring values get dynamically ordered and
built into an select statement.
4. Every time a new query is ran the list boxes display values based on the
results returned. Thus, this filters your results.

Using the dataset really helps with the overhead because each control is
pulling from the same object.

Thanks,

J

"Steven Cheng[MSFT]" <st*****@online .microsoft.com> wrote in message
news:Ig******** ******@TK2MSFTN GXA02.phx.gbl.. .
Hi J,

Thanks for your response.
Of course manually loop the gridView and extract values from each row is
one possible appraoch, but that's not a good idea, either hard to code
also
have poor performance....
As for the further question you mentioned, I think it's better that you
move that query into a separate new datasource object, so that we can
cache
that query to improve performance(I think those query of DISTINCT values
should be cachable....). We can use the Select statement and Select
parameters to define parameter based query like:

=============== ========
<asp:SqlDataSou rce ID="SqlDataSour ce2" runat="server"
ConnectionStrin g="<%$
ConnectionStrin gs:LocalNorthWi nd %>"
SelectCommand=" SELECT [Discontinued], [ProductID],
[ProductName], [UnitPrice] FROM [Products] WHERE ([Discontinued] =
@Discontinued)" >
<SelectParamete rs>
<asp:Paramete r DefaultValue="t rue" Name="Discontin ued"
Type="Boolean" />
</SelectParameter s>
</asp:SqlDataSour ce>
<asp:GridView ID="GridView2" runat="server"
AutoGenerateCol umns="False" DataKeyNames="P roductID"
DataSourceID="S qlDataSource2">
<Columns>
<asp:CheckBoxFi eld DataField="Disc ontinued"
HeaderText="Dis continued" SortExpression= "Discontinu ed" />
<asp:BoundFie ld DataField="Prod uctID"
HeaderText="Pro ductID" InsertVisible=" False"
ReadOnly="True" SortExpression= "ProductID" />
<asp:BoundFie ld DataField="Prod uctName"
HeaderText="Pro ductName" SortExpression= "ProductNam e" />
<asp:BoundFie ld DataField="Unit Price"
HeaderText="Uni tPrice" SortExpression= "UnitPrice" />
</Columns>
</asp:GridView>
=============== =======

And we can also programmaticall y call the datasource.sele ct method with
the
certain parameter instances as I've mentioned in the former message....

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

--------------------
| Reply-To: <wa********@new sgroups.nospam>
| From: <wa********@new sgroups.nospam>
| References: <e0************ *@TK2MSFTNGP09. phx.gbl>
<5w************ **@TK2MSFTNGXA0 2.phx.gbl>
<ep************ **@TK2MSFTNGP10 .phx.gbl>
<dU************ **@TK2MSFTNGXA0 2.phx.gbl>
| Subject: Re: Filtering data inside the Gridview Control?
| Date: Sun, 18 Dec 2005 14:48:48 -0800
| Lines: 231
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| Message-ID: <u$************ **@TK2MSFTNGP09 .phx.gbl>
| Newsgroups: microsoft.publi c.dotnet.framew ork.aspnet
| NNTP-Posting-Host: ip-206.159.118.137 .hdiss.net 206.159.118.137
| Path: TK2MSFTNGXA02.p hx.gbl!TK2MSFTN GP08.phx.gbl!TK 2MSFTNGP09.phx. gbl
| Xref: TK2MSFTNGXA02.p hx.gbl
microsoft.publi c.dotnet.framew ork.aspnet:3656 06
| X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.aspnet
|
| Steven,
|
| I was told by someone that I should just loop through the gridview and
get
| the data that way. Is this incorrect?
|
| The way that you explain makes sense. Is the select method expression
just
| like SQL scripts? Because what I really need is to be able to do
something
| like, "SELECT DISTINCT column FROM table WHERE column='column' ". For
the
| column that I want the data from, there are going to be many of the same
| values. All I want is to know what ones are there. Therefore I dont
need
| duplicates. So if there were:
|
| [column1]
| hat
| car
| car
| house
| hat
| hat
| hat
| car
|
| It would return back to me:
|
| hat
| car
| house
|
| I am then going to take these values and put them into a datalist
control
| that will display these as hyperlinks. Then when the user clicks on the
| hyperlink I can use that value as criteria for doing an sql query to the
DB
| thus repopulating the gridview and repopulating the datalist that
represent
| the values to filter by based on what was returned by the last sql query
to
| the DB.
|
| I hope that makes sense.
|
| I will try what you suggested and let you know how it works.
|
| Thanks,
|
| J
|
|
|
|
|
| "Steven Cheng[MSFT]" <st*****@online .microsoft.com> wrote in message
| news:dU******** ******@TK2MSFTN GXA02.phx.gbl.. .
| > Hi J,
| >
| > Thanks for the response. For searching, I'm afraid we have to do it at
| > datasource level since the GridView dosn't maintain orginal data
records
| > after databinding (only displaying databound data......). Also, the
| > DataSource control is mainly used to provide statically data
retrieveing
| > and work with databound controls, so we can manually call its methods
to
| > retrieve the Selected data, but can not affect the way DataBound
control
| > use it (we can only apply some parameters ...... in it...)
| >
| > So for your scenario, you can try manually call the SqlDataSource
| > control's
| > Select method with proper parameters then the returned object is the
| > datasouce (by default it is a DataView since the DataSourceMode is set
to
| > "DataSet" ). e.g:
| >
| > DataView dv = SqlDataSource1. Select(DataSour ceSelectArgumen ts.Empty)
as
| > DataView;
| > Response.Write( "<br>Return ed: " + dv.Table.Select (.....) );
| >
| > Also, for such scenario, it is prefered that you not use
| > DataSourceContr ol,
| > just programmaticall y retrieve the DataSet/DataTable your self and
stored
| > in Cache, so that you can reuse it for searching later ......
| >
| > Thanks,
| >
| > Steven Cheng
| > Microsoft Online Support
| >
| > Get Secure! www.microsoft.com/security
| > (This posting is provided "AS IS", with no warranties, and confers no
| > rights.)
| >
| >
| >
| > --------------------
| > | Reply-To: <wa********@new sgroups.nospam>
| > | From: <wa********@new sgroups.nospam>
| > | References: <e0************ *@TK2MSFTNGP09. phx.gbl>
| > <5w************ **@TK2MSFTNGXA0 2.phx.gbl>
| > | Subject: Re: Filtering data inside the Gridview Control?
| > | Date: Thu, 15 Dec 2005 01:48:11 -0800
| > | Lines: 110
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | X-RFC2646: Format=Flowed; Original
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | Message-ID: <ep************ **@TK2MSFTNGP10 .phx.gbl>
| > | Newsgroups: microsoft.publi c.dotnet.framew ork.aspnet
| > | NNTP-Posting-Host: ip-206.159.118.137 .hdiss.net 206.159.118.137
| > | Path:
TK2MSFTNGXA02.p hx.gbl!TK2MSFTN GP08.phx.gbl!TK 2MSFTNGP10.phx. gbl
| > | Xref: TK2MSFTNGXA02.p hx.gbl
| > microsoft.publi c.dotnet.framew ork.aspnet:3650 43
| > | X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.aspnet
| > |
| > | Steven,
| > |
| > | Cool that helped me with filtering the rows but I have one other
| > question.
| > |
| > | How do I search through the gridview? Would I do this at the
| > sqldatasource
| > | level?
| > |
| > | I figured that I sould search with the datatable.selec t but how do I
| > access
| > | the datatable of the sqldatasource?
| > |
| > | Thanks for your help
| > |
| > | J
| > |
| > |
| > | "Steven Cheng[MSFT]" <st*****@online .microsoft.com> wrote in message
| > | news:5w******** ******@TK2MSFTN GXA02.phx.gbl.. .
| > | > Hi J,
| > | >
| > | > Welcome to ASPNET newsgroup.
| > | > As for the filtering data displayed in the asp.net GridView
control
| > | > question, based on my understanding, we should do this at
DataSource
| > | > level... I think you're using a SqlDataSource to provide the data
| > records
| > | > to the GridView , yes? If so, you can have alook at the
following
| > msdn
| > | > reference about using filtering function of the SqlDataSource.. ..:
| > | >
| > | > #How to: Enable Filtering for SqlDataSource Controls (Visual
Studio)
| > | > http://msdn2.microsoft.com/en-us/library/ms178306.aspx
| > | >
| > | > e.g:
| > | >
| > | > <asp:CheckBox ID="Discontinue d" runat="server"
AutoPostBack="T rue"
/>
| > | > <asp:SqlDataSou rce ID="SqlDataSour ce1" runat="server"
| > | > ConnectionStrin g="<%$
| > | > ConnectionStrin gs:LocalNorthWi nd %>"
| > | > SelectCommand=" SELECT [ProductID], [ProductName],
| > | > [Discontinued], [ReorderLevel], [CategoryID] FROM [Products]"
| > | > FilterExpressio n="Discontinu ed = {0}"
| > | > OnSelected="Sql DataSource1_Sel ected">
| > | > <FilterParamete rs>
| > | > <asp:ControlPar ameter ControlID="Disc ontinued"
| > | > Type="boolean"
| > | > Name="disc" PropertyName="C hecked" />
| > | > </FilterParameter s>
| > | > </asp:SqlDataSour ce>
| > | >
| > | >
| > | > The above sql datasource setting the filter as "Discontinu ed =
{0}"
,
| > and
| > | > the parameter is defined in the <Filterparamete rs> collection...
The
| > | > filter experssion is just the same as the Filter Expression we set
for
| > | > DataView class programmaticall y. And this filtering is done
through
| > the
| > | > DataSet returned by the DataSourceContr ol rather than filtering at
| > | > database
| > | > level ( the "Where" statement
| > | > of the DataSoureContro l is used to done database level filtering
....
| > | >
| > | > Thanks,
| > | >
| > | > Steven Cheng
| > | > Microsoft Online Support
| > | >
| > | > Get Secure! www.microsoft.com/security
| > | > (This posting is provided "AS IS", with no warranties, and confers
no
| > | > rights.)
| > | >
| > | >
| > | >
| > | >
| > | > --------------------
| > | > | Reply-To: <wa********@new sgroups.nospam>
| > | > | From: <wa********@new sgroups.nospam>
| > | > | Subject: Filtering data inside the Gridview Control?
| > | > | Date: Wed, 14 Dec 2005 19:25:24 -0800
| > | > | Lines: 18
| > | > | X-Priority: 3
| > | > | X-MSMail-Priority: Normal
| > | > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | > | X-RFC2646: Format=Flowed; Original
| > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | > | Message-ID: <e0************ *@TK2MSFTNGP09. phx.gbl>
| > | > | Newsgroups: microsoft.publi c.dotnet.framew ork.aspnet
| > | > | NNTP-Posting-Host: ip-206.159.118.137 .hdiss.net 206.159.118.137
| > | > | Path:
| > TK2MSFTNGXA02.p hx.gbl!TK2MSFTN GP08.phx.gbl!TK 2MSFTNGP09.phx. gbl
| > | > | Xref: TK2MSFTNGXA02.p hx.gbl
| > | > microsoft.publi c.dotnet.framew ork.aspnet:3650 02
| > | > | X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.aspnet
| > | > |
| > | > | Hello,
| > | > |
| > | > | Does anyone have an idea on how I can filter the data in the
| > gridview
| > | > | control that was returned by an sql query?
| > | > |
| > | > | I have a gridview that works fine when I populate it with data.
Now
| > I
| > | > want
| > | > | to look at that data and filter it based on what is in it.
| > | > |
| > | > | I know that this could have been done with data sets and data
views
| > in
| > | > | asp.net 1.1 but how is this done now in asp.net 2.0?
| > | > |
| > | > | Is there a way to do this?
| > | > |
| > | > | Thanks,
| > | > |
| > | > | J
| > | > |
| > | > |
| > | > |
| > | >
| > |
| > |
| > |
| >
|
|
|

Dec 21 '05 #7
Cool! So you use the ObjectDataSourc e with custom class currently? That's
also a good idea....

Thanks for your further followup.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

--------------------
| Reply-To: <wa********@new sgroups.nospam>
| From: <wa********@new sgroups.nospam>
| References: <e0************ *@TK2MSFTNGP09. phx.gbl>
<5w************ **@TK2MSFTNGXA0 2.phx.gbl>
<ep************ **@TK2MSFTNGP10 .phx.gbl>
<dU************ **@TK2MSFTNGXA0 2.phx.gbl>
<u$************ **@TK2MSFTNGP09 .phx.gbl>
<Ig************ **@TK2MSFTNGXA0 2.phx.gbl>
| Subject: Re: Filtering data inside the Gridview Control?
| Date: Wed, 21 Dec 2005 14:52:32 -0800
| Lines: 378
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| Message-ID: <OY************ **@TK2MSFTNGP10 .phx.gbl>
| Newsgroups: microsoft.publi c.dotnet.framew ork.aspnet
| NNTP-Posting-Host: ip-206.159.118.137 .hdiss.net 206.159.118.137
| Path: TK2MSFTNGXA02.p hx.gbl!TK2MSFTN GP08.phx.gbl!TK 2MSFTNGP10.phx. gbl
| Xref: TK2MSFTNGXA02.p hx.gbl
microsoft.publi c.dotnet.framew ork.aspnet:3664 27
| X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.aspnet
|
| Ok,
|
| I figured it out. Here is what I did:
|
| 1. I bound both the grid view control and the list boxes to a dataset
that
| I created which pulls the data from the DB based on an SQL Statement.
| 2. I created a custom class that finds only distinct rows based on a
table
| and column. I call this class as the datasource of the data list
controls
| that I have on the page displaying the filtering options.
| 3. The SQL SELECT statement gets pulled from the querystring values in
the
| url. In other words the querystring values get dynamically ordered and
| built into an select statement.
| 4. Every time a new query is ran the list boxes display values based on
the
| results returned. Thus, this filters your results.
|
| Using the dataset really helps with the overhead because each control is
| pulling from the same object.
|
| Thanks,
|
| J
|
|
|
|
|
| "Steven Cheng[MSFT]" <st*****@online .microsoft.com> wrote in message
| news:Ig******** ******@TK2MSFTN GXA02.phx.gbl.. .
| > Hi J,
| >
| > Thanks for your response.
| > Of course manually loop the gridView and extract values from each row is
| > one possible appraoch, but that's not a good idea, either hard to code
| > also
| > have poor performance....
| > As for the further question you mentioned, I think it's better that you
| > move that query into a separate new datasource object, so that we can
| > cache
| > that query to improve performance(I think those query of DISTINCT values
| > should be cachable....). We can use the Select statement and Select
| > parameters to define parameter based query like:
| >
| > =============== ========
| > <asp:SqlDataSou rce ID="SqlDataSour ce2" runat="server"
| > ConnectionStrin g="<%$
| > ConnectionStrin gs:LocalNorthWi nd %>"
| > SelectCommand=" SELECT [Discontinued], [ProductID],
| > [ProductName], [UnitPrice] FROM [Products] WHERE ([Discontinued] =
| > @Discontinued)" >
| > <SelectParamete rs>
| > <asp:Paramete r DefaultValue="t rue" Name="Discontin ued"
| > Type="Boolean" />
| > </SelectParameter s>
| > </asp:SqlDataSour ce>
| > <asp:GridView ID="GridView2" runat="server"
| > AutoGenerateCol umns="False" DataKeyNames="P roductID"
| > DataSourceID="S qlDataSource2">
| > <Columns>
| > <asp:CheckBoxFi eld DataField="Disc ontinued"
| > HeaderText="Dis continued" SortExpression= "Discontinu ed" />
| > <asp:BoundFie ld DataField="Prod uctID"
| > HeaderText="Pro ductID" InsertVisible=" False"
| > ReadOnly="True" SortExpression= "ProductID" />
| > <asp:BoundFie ld DataField="Prod uctName"
| > HeaderText="Pro ductName" SortExpression= "ProductNam e" />
| > <asp:BoundFie ld DataField="Unit Price"
| > HeaderText="Uni tPrice" SortExpression= "UnitPrice" />
| > </Columns>
| > </asp:GridView>
| > =============== =======
| >
| > And we can also programmaticall y call the datasource.sele ct method with
| > the
| > certain parameter instances as I've mentioned in the former message....
| >
| > Thanks,
| >
| > Steven Cheng
| > Microsoft Online Support
| >
| > Get Secure! www.microsoft.com/security
| > (This posting is provided "AS IS", with no warranties, and confers no
| > rights.)
| >
| >
| >
| >
| >
| > --------------------
| > | Reply-To: <wa********@new sgroups.nospam>
| > | From: <wa********@new sgroups.nospam>
| > | References: <e0************ *@TK2MSFTNGP09. phx.gbl>
| > <5w************ **@TK2MSFTNGXA0 2.phx.gbl>
| > <ep************ **@TK2MSFTNGP10 .phx.gbl>
| > <dU************ **@TK2MSFTNGXA0 2.phx.gbl>
| > | Subject: Re: Filtering data inside the Gridview Control?
| > | Date: Sun, 18 Dec 2005 14:48:48 -0800
| > | Lines: 231
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | X-RFC2646: Format=Flowed; Original
| > | Message-ID: <u$************ **@TK2MSFTNGP09 .phx.gbl>
| > | Newsgroups: microsoft.publi c.dotnet.framew ork.aspnet
| > | NNTP-Posting-Host: ip-206.159.118.137 .hdiss.net 206.159.118.137
| > | Path: TK2MSFTNGXA02.p hx.gbl!TK2MSFTN GP08.phx.gbl!TK 2MSFTNGP09.phx. gbl
| > | Xref: TK2MSFTNGXA02.p hx.gbl
| > microsoft.publi c.dotnet.framew ork.aspnet:3656 06
| > | X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.aspnet
| > |
| > | Steven,
| > |
| > | I was told by someone that I should just loop through the gridview and
| > get
| > | the data that way. Is this incorrect?
| > |
| > | The way that you explain makes sense. Is the select method expression
| > just
| > | like SQL scripts? Because what I really need is to be able to do
| > something
| > | like, "SELECT DISTINCT column FROM table WHERE column='column' ". For
| > the
| > | column that I want the data from, there are going to be many of the
same
| > | values. All I want is to know what ones are there. Therefore I dont
| > need
| > | duplicates. So if there were:
| > |
| > | [column1]
| > | hat
| > | car
| > | car
| > | house
| > | hat
| > | hat
| > | hat
| > | car
| > |
| > | It would return back to me:
| > |
| > | hat
| > | car
| > | house
| > |
| > | I am then going to take these values and put them into a datalist
| > control
| > | that will display these as hyperlinks. Then when the user clicks on
the
| > | hyperlink I can use that value as criteria for doing an sql query to
the
| > DB
| > | thus repopulating the gridview and repopulating the datalist that
| > represent
| > | the values to filter by based on what was returned by the last sql
query
| > to
| > | the DB.
| > |
| > | I hope that makes sense.
| > |
| > | I will try what you suggested and let you know how it works.
| > |
| > | Thanks,
| > |
| > | J
| > |
| > |
| > |
| > |
| > |
| > | "Steven Cheng[MSFT]" <st*****@online .microsoft.com> wrote in message
| > | news:dU******** ******@TK2MSFTN GXA02.phx.gbl.. .
| > | > Hi J,
| > | >
| > | > Thanks for the response. For searching, I'm afraid we have to do it
at
| > | > datasource level since the GridView dosn't maintain orginal data
| > records
| > | > after databinding (only displaying databound data......). Also,
the
| > | > DataSource control is mainly used to provide statically data
| > retrieveing
| > | > and work with databound controls, so we can manually call its
methods
| > to
| > | > retrieve the Selected data, but can not affect the way DataBound
| > control
| > | > use it (we can only apply some parameters ...... in it...)
| > | >
| > | > So for your scenario, you can try manually call the SqlDataSource
| > | > control's
| > | > Select method with proper parameters then the returned object is the
| > | > datasouce (by default it is a DataView since the DataSourceMode is
set
| > to
| > | > "DataSet" ). e.g:
| > | >
| > | > DataView dv =
SqlDataSource1. Select(DataSour ceSelectArgumen ts.Empty)
| > as
| > | > DataView;
| > | > Response.Write( "<br>Return ed: " + dv.Table.Select (.....) );
| > | >
| > | > Also, for such scenario, it is prefered that you not use
| > | > DataSourceContr ol,
| > | > just programmaticall y retrieve the DataSet/DataTable your self and
| > stored
| > | > in Cache, so that you can reuse it for searching later ......
| > | >
| > | > Thanks,
| > | >
| > | > Steven Cheng
| > | > Microsoft Online Support
| > | >
| > | > Get Secure! www.microsoft.com/security
| > | > (This posting is provided "AS IS", with no warranties, and confers
no
| > | > rights.)
| > | >
| > | >
| > | >
| > | > --------------------
| > | > | Reply-To: <wa********@new sgroups.nospam>
| > | > | From: <wa********@new sgroups.nospam>
| > | > | References: <e0************ *@TK2MSFTNGP09. phx.gbl>
| > | > <5w************ **@TK2MSFTNGXA0 2.phx.gbl>
| > | > | Subject: Re: Filtering data inside the Gridview Control?
| > | > | Date: Thu, 15 Dec 2005 01:48:11 -0800
| > | > | Lines: 110
| > | > | X-Priority: 3
| > | > | X-MSMail-Priority: Normal
| > | > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | > | X-RFC2646: Format=Flowed; Original
| > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | > | Message-ID: <ep************ **@TK2MSFTNGP10 .phx.gbl>
| > | > | Newsgroups: microsoft.publi c.dotnet.framew ork.aspnet
| > | > | NNTP-Posting-Host: ip-206.159.118.137 .hdiss.net 206.159.118.137
| > | > | Path:
| > TK2MSFTNGXA02.p hx.gbl!TK2MSFTN GP08.phx.gbl!TK 2MSFTNGP10.phx. gbl
| > | > | Xref: TK2MSFTNGXA02.p hx.gbl
| > | > microsoft.publi c.dotnet.framew ork.aspnet:3650 43
| > | > | X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.aspnet
| > | > |
| > | > | Steven,
| > | > |
| > | > | Cool that helped me with filtering the rows but I have one other
| > | > question.
| > | > |
| > | > | How do I search through the gridview? Would I do this at the
| > | > sqldatasource
| > | > | level?
| > | > |
| > | > | I figured that I sould search with the datatable.selec t but how
do I
| > | > access
| > | > | the datatable of the sqldatasource?
| > | > |
| > | > | Thanks for your help
| > | > |
| > | > | J
| > | > |
| > | > |
| > | > | "Steven Cheng[MSFT]" <st*****@online .microsoft.com> wrote in
message
| > | > | news:5w******** ******@TK2MSFTN GXA02.phx.gbl.. .
| > | > | > Hi J,
| > | > | >
| > | > | > Welcome to ASPNET newsgroup.
| > | > | > As for the filtering data displayed in the asp.net GridView
| > control
| > | > | > question, based on my understanding, we should do this at
| > DataSource
| > | > | > level... I think you're using a SqlDataSource to provide the
data
| > | > records
| > | > | > to the GridView , yes? If so, you can have alook at the
| > following
| > | > msdn
| > | > | > reference about using filtering function of the
SqlDataSource.. ..:
| > | > | >
| > | > | > #How to: Enable Filtering for SqlDataSource Controls (Visual
| > Studio)
| > | > | > http://msdn2.microsoft.com/en-us/library/ms178306.aspx
| > | > | >
| > | > | > e.g:
| > | > | >
| > | > | > <asp:CheckBox ID="Discontinue d" runat="server"
| > AutoPostBack="T rue"
| > />
| > | > | > <asp:SqlDataSou rce ID="SqlDataSour ce1" runat="server"
| > | > | > ConnectionStrin g="<%$
| > | > | > ConnectionStrin gs:LocalNorthWi nd %>"
| > | > | > SelectCommand=" SELECT [ProductID], [ProductName],
| > | > | > [Discontinued], [ReorderLevel], [CategoryID] FROM [Products]"
| > | > | > FilterExpressio n="Discontinu ed = {0}"
| > | > | > OnSelected="Sql DataSource1_Sel ected">
| > | > | > <FilterParamete rs>
| > | > | > <asp:ControlPar ameter ControlID="Disc ontinued"
| > | > | > Type="boolean"
| > | > | > Name="disc" PropertyName="C hecked" />
| > | > | > </FilterParameter s>
| > | > | > </asp:SqlDataSour ce>
| > | > | >
| > | > | >
| > | > | > The above sql datasource setting the filter as "Discontinu ed =
| > {0}"
| > ,
| > | > and
| > | > | > the parameter is defined in the <Filterparamete rs> collection...
| > The
| > | > | > filter experssion is just the same as the Filter Expression we
set
| > for
| > | > | > DataView class programmaticall y. And this filtering is done
| > through
| > | > the
| > | > | > DataSet returned by the DataSourceContr ol rather than filtering
at
| > | > | > database
| > | > | > level ( the "Where" statement
| > | > | > of the DataSoureContro l is used to done database level filtering
| > ....
| > | > | >
| > | > | > Thanks,
| > | > | >
| > | > | > Steven Cheng
| > | > | > Microsoft Online Support
| > | > | >
| > | > | > Get Secure! www.microsoft.com/security
| > | > | > (This posting is provided "AS IS", with no warranties, and
confers
| > no
| > | > | > rights.)
| > | > | >
| > | > | >
| > | > | >
| > | > | >
| > | > | > --------------------
| > | > | > | Reply-To: <wa********@new sgroups.nospam>
| > | > | > | From: <wa********@new sgroups.nospam>
| > | > | > | Subject: Filtering data inside the Gridview Control?
| > | > | > | Date: Wed, 14 Dec 2005 19:25:24 -0800
| > | > | > | Lines: 18
| > | > | > | X-Priority: 3
| > | > | > | X-MSMail-Priority: Normal
| > | > | > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | > | > | X-RFC2646: Format=Flowed; Original
| > | > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | > | > | Message-ID: <e0************ *@TK2MSFTNGP09. phx.gbl>
| > | > | > | Newsgroups: microsoft.publi c.dotnet.framew ork.aspnet
| > | > | > | NNTP-Posting-Host: ip-206.159.118.137 .hdiss.net
206.159.118.137
| > | > | > | Path:
| > | > TK2MSFTNGXA02.p hx.gbl!TK2MSFTN GP08.phx.gbl!TK 2MSFTNGP09.phx. gbl
| > | > | > | Xref: TK2MSFTNGXA02.p hx.gbl
| > | > | > microsoft.publi c.dotnet.framew ork.aspnet:3650 02
| > | > | > | X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.aspnet
| > | > | > |
| > | > | > | Hello,
| > | > | > |
| > | > | > | Does anyone have an idea on how I can filter the data in the
| > | > gridview
| > | > | > | control that was returned by an sql query?
| > | > | > |
| > | > | > | I have a gridview that works fine when I populate it with
data.
| > Now
| > | > I
| > | > | > want
| > | > | > | to look at that data and filter it based on what is in it.
| > | > | > |
| > | > | > | I know that this could have been done with data sets and data
| > views
| > | > in
| > | > | > | asp.net 1.1 but how is this done now in asp.net 2.0?
| > | > | > |
| > | > | > | Is there a way to do this?
| > | > | > |
| > | > | > | Thanks,
| > | > | > |
| > | > | > | J
| > | > | > |
| > | > | > |
| > | > | > |
| > | > | >
| > | > |
| > | > |
| > | > |
| > | >
| > |
| > |
| > |
| >
|
|
|

Dec 22 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
8101
by: Bill Manring | last post by:
I am writing a very simple web application using VS 2005 and a SQL Server Database. I am just starting to experiment wth .NET 2.0 and I am trying out the supposedly improved databinding. I dragged a table from a datasource on to my web form and the GridView cpntrol was created. I turned on paging, and Edit and Delete buttons. I ran the app and everything worked great - not a single line of code written yet. Then I wanted to add a...
1
3497
by: SomebodyElse | last post by:
Hi. Apologies if this has been asked here before - I've searched & searched but can't find anything. It's probably my serach parameters, but I'm having trouble even describing it to a search engine! I'm sure this is a very simple problem, with straighforward solution. It certainly is in classic asp/ado, but I'm finally learning this .NET business. I like it, but can't get my head around some concepts. Anyway....
5
6444
by: sutphinwb | last post by:
Hi - This could be a simple question. When I relate two tables in a datasetet, how do I get that relation to show up in a GridView? The only way I've done it, is to create a separate table in the dataset with a join query for the GetData() select method. I use ObjectDataStore to couple the GridView with the table adapter on the dataset. If I point the ODS at the child table, the GridView will bind to the "normal" select and I end up...
1
10439
by: Nam | last post by:
I have a DropDownList control in a TemplateField inside my GridView How do I access this control’s SelectedIndexChanged event in codeBehind page? I don’t see the ID “DropDownList1” of this control in the list of objects in odeBehind page. GridView1 and DropDownList1 are bound to TWO DIFFERENT tables (DataSources). The last column of each row in the GridView is shown as a DropDownList. I want to use the SelectedIndexChanged ...
2
2219
by: kevinpond | last post by:
I have a Gridview displaying the results of a stored procedure. The stored procedure is constantly changing so the columns displayed in the data grid are constantly changing. I'd like to give users the ability to filter by the value of any column. This is easily done using the FilterExpression property of the DataSource. However, prior to creating the filter expression, I need to know if the column they wish to filter contains text or...
3
6629
by: Harry Haller | last post by:
Hello, I want to implement a generic list which will be used to display 7 columns in a GridView. One should be able to sort, filter and page each of the 7 columns. Ideally the filter should be implemented simultaneously for multiple columns - but the data need only be sorted by a single column at a time. Sorting should be both ascending and descending. I'm currently using a DataView but it's far too slow, because there are a large...
1
2766
by: weird0 | last post by:
I have created a GridView and dynamically added data to it by creating a DataTable(as advised) and bound it with a reader. Then, assigned the DataSource of GridView to DataTable. But even that aint working as alternatively assigned reader obj. to datasource of GridView. Here is the code for the aspx.cs page: ( The reader surely cootains data as its HasRows property is true). Plz help. Never got stuck this bad with a problem. For more...
11
5291
by: =?Utf-8?B?QW5u?= | last post by:
Hello, I want to send an email when the Insert button is clicked that includes data entered on the form. I am using asp.net 2.0 and an insert template. I receive the email OK, but there is no subject or body. Please help. Here's my code: Protected Sub InsertButton_Init(ByVal sender As Object, ByVal e As System.EventArgs) Dim message As New System.Net.Mail.MailMessage() Dim client = New System.Net.Mail.SmtpClient()
1
4686
by: HockeyFan | last post by:
protected void gvAuthorizedSigners_RowCommand(object sender, GridViewCommandEventArgs e) { if (e.CommandName == "EmptyInsert") { odsAuthorizedSigners.Insert(); return; } if (e.CommandName == "Insert")
0
8697
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, well explore What is ONU, What Is Router, ONU & Routers main usage, and What is the difference between ONU and Router. Lets take a closer look ! Part I. Meaning of...
0
8622
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9184
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8930
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7767
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing, and deploymentwithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6538
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4382
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4634
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3062
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.