473,385 Members | 1,602 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

Can I filter rows or create aggregate rows with DataTable?

I've read that the expression property for DataColumns is used to "Sets or
retrieves the expresssion used to filter rows, calculate the values in a
column, or create an aggregate column.".

I have seen examples on how to filter a column, but how would I filter out
an entire row depending on the value of a column?

For example, if I wanted to filter out every row in a multi-column table
where the "Is Fubar" column equals "true", how would I do this using
expression?

The other question was about creating an aggregate row. I see how aggregate
columns are created, which perform calcs on values with a row, but is there a
nice and easy way to create an aggregate row?

One option I was thinking about (stop me if it sounds too crazy) is to have
a second DataTable, which will only have one row with matching columns as the
first DataTable. This row would be the aggregate row for all the rows from
the first table. But can I use expression on the total columns in the second
table to sum all the values of a particular column in the first table?
Nov 16 '05 #1
3 14291

"MrNobody" <Mr******@discussions.microsoft.com> wrote in message
news:61**********************************@microsof t.com...
I've read that the expression property for DataColumns is used to "Sets or
retrieves the expresssion used to filter rows, calculate the values in a
column, or create an aggregate column.".

I have seen examples on how to filter a column, but how would I filter out
an entire row depending on the value of a column?

For example, if I wanted to filter out every row in a multi-column table
where the "Is Fubar" column equals "true", how would I do this using
expression?
Try with "[Is Fubar] = true" expression.

The other question was about creating an aggregate row. I see how
aggregate
columns are created, which perform calcs on values with a row, but is
there a
nice and easy way to create an aggregate row?
What does aggregate row mean to you in first place?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

One option I was thinking about (stop me if it sounds too crazy) is to
have
a second DataTable, which will only have one row with matching columns as
the
first DataTable. This row would be the aggregate row for all the rows from
the first table. But can I use expression on the total columns in the
second
table to sum all the values of a particular column in the first table?

Nov 16 '05 #2


"Miha Markic [MVP C#]" wrote:

Try with "[Is Fubar] = true" expression.

Ok, but then where do I put this expression? For example, I have a DataTable
with 3 columns named "ID", "Country", "Continent". If I want to filter every
row in this table where Continent = 'Europe', how do I do that?

I tried:

datatable.Columns["Continent"].Expression = "[Continent] = 'Europe'" but I
get the error: " Cannot set Expression property due to circular reference in
the expression.". So I tried doing that on the "ID" Column and all that
happens is it converts my data in ID column to '1' if the row's continent is
Europe or '0' if it's not. What I want is for that row to become hidden.

As far as I can tell, there is not other place to enter Expressions for rows
on DataGrid/DataTable.

Is there something I am missing?


What does aggregate row mean to you in first place?


Well to me it means a row which performs aggragate functions on the data in
all the other rows of the table. So if my table contained rows which had a
prices column, I may want to have an aggregate row on the bottom which does a
Sum on all the prices of that table. Can I do this using expression, and if
so, how do I do it?

Thanks for your reply
Nov 16 '05 #3

"MrNobody" <Mr******@discussions.microsoft.com> wrote in message
news:63**********************************@microsof t.com...


"Miha Markic [MVP C#]" wrote:

Try with "[Is Fubar] = true" expression.


Ok, but then where do I put this expression? For example, I have a
DataTable
with 3 columns named "ID", "Country", "Continent". If I want to filter
every
row in this table where Continent = 'Europe', how do I do that?

I tried:

datatable.Columns["Continent"].Expression = "[Continent] = 'Europe'" but
I
get the error: " Cannot set Expression property due to circular reference
in
the expression.". So I tried doing that on the "ID" Column and all that
happens is it converts my data in ID column to '1' if the row's continent
is
Europe or '0' if it's not. What I want is for that row to become hidden.

As far as I can tell, there is not other place to enter Expressions for
rows
on DataGrid/DataTable.

Is there something I am missing?


Use DataView to filter the content or DataTable.Select method.
DataView dv = new DataView(table);
dv.RowFilter = "filter expression";


What does aggregate row mean to you in first place?


Well to me it means a row which performs aggragate functions on the data
in
all the other rows of the table. So if my table contained rows which had a
prices column, I may want to have an aggregate row on the bottom which
does a
Sum on all the prices of that table. Can I do this using expression, and
if
so, how do I do it?


No, you can't, since the expression applies to whole column and aggregates
are used in master-detail relations.
However, you might add a row to table and set its values manually (to
reflect the aggregate value).

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com
Nov 16 '05 #4

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

Similar topics

6
by: Tony Miller | last post by:
All I have an aggregate query using the function Month & Year on a datereceived field ie: TheYear: Year() TheMonth: Month() These are the group by fields to give me a Count on another field by...
3
by: Dan V. | last post by:
How can I use real SQL on a DataTable? i.e. not array of rows using a filter... as in DataTable.Select. I read at : microsoft.public.dotnet.framework.adonet "As others have posted: There is no...
1
by: werk | last post by:
For limiting access to the database to strictly necessary I try to filter the query by using DataView. Thw DataSet ds contains three columns (fields) : (LAND_ID, Landcode, Landnaam) and four...
1
by: Andrew | last post by:
Hey all, I am very new to ASP.Net (and .Net in general), but that isn't stopping the boss from wanting to begin new projects in it. This latest project has me kinda stumped and after a couple...
5
by: Geoff Jones | last post by:
Hi Suppose we have an array of DataRows e.g. Dim drMyRows() As DataRow = myTable.Rows(0).GetChildRows("PricesCompany") generated via a relationship. The question I have is this. Isn't...
3
by: Niyazi | last post by:
Hi all, I have a dataTable that contains nearly 38400 rows. In the dataTable consist of 3 column. column 1 Name: MUHNO column 2 Name: HESNO Column 3 Name: BALANCE Let me give you some...
6
by: mike11d11 | last post by:
I cant seem to filter down my dataset table by criteria in expression. Can someone tell me why I still have the same amount of rows after I use this filter select option. Private Sub...
2
by: Paul | last post by:
Hi all, I have a DataTable and a DataGridView that is using it as a datasource. I'm using the DataTable.Select method and I can find some rows. Is it now possible, somehow to use the result of the...
2
by: Raymond Chiu | last post by:
Dear all, If I have the dataset, What the code should be to filter records in the dataset by some fields criteria? Is it like a SQL? Thanks for your help,
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

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.