asked on

filter DataGridView

Hello,

I have a form with datagridview and four buttons. In the datagridview there are columns
surname, date of starting a job, data of ending a job.
I'd like to filter the datagridview by surname (e.g. to show in the datagridview every Smith),
the I'd like to show only records with avergate job length X years (every surname can have
more then one jobs in the past).
Then I'd like to show only employees with shortest length of employment and longist length
of employment.

Do you know how to implements these functionalities by simply clicking on buttons
or changing text in textboxes?

thanks for help

SOLUTION

cadsjo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

greatsubash

please refer the following link,
http://msdn.microsoft.com/en-us/library/aa480727.aspx
http://www.codeproject.com/KB/grid/DataGridViewFilterPopup.aspx
http://www.c-sharpcorner.com/UploadFile/yougerthen/102162008174917PM/1.aspx
http://stackoverflow.com/questions/311070/datagridview-filtering-onclick-event-c-winform
http://www.codeproject.com/KB/grid/GridFilter.aspx

xRalf

ASKER

Thanks cadsjo,

but it throws an exception

System.Data.SyntaxErrorException was unhandled
Message="Syntax error: Missing operand before 'Mod' operator."

here
dvSearch.RowFilter = rowFilter;

xRalf

ASKER

It works when I write
rowFilter = "Surname = 'Smith'";

but my datagridview is empty. I'd like to have there a record with Smith.

And this is the easier part of the question. I don't know how to make more complex queries described in my question.

cadsjo

xRalf,

Maybe you can use one of the links of greatsubash, because your filter needs are complicated.

Or you can refresh the datasource of your grid with a new queryresult from your query with a new where clause.
If you are querying Access you must use the asterisk instead of the percentage sign.

13598

Just use the underlying bindingsource.filter. It's easy. It does everything for you.
bindingsource(the name of your bindingsource for the datagridview).Filter = "Smith"

xRalf

ASKER

The problem is that my datagridview is empty after the filtering
and I don't know why it is empty.

xRalf

ASKER

If I use this version of filter
rowFilter = string.Format("{0} like *{1}*", searchField, textBox.text);

I get the same exception as before

13598

Why don't you use the bindingsource.filter not rowfilter that is the grid.
Did you try bindingsource.filter = string.Format("{0} like *{1}*", searchField, textBox.text);
If the value exists that will automatically refresh the datagridview for you.

xRalf

ASKER

I don't understand what is the binding source. What type is it and how to write it
in my sourcecode

13598

Are you using a bound datagridview? How does the data get into your datagridview?

xRalf

ASKER

I have an XML file and the date get into datagridview as follows

DataSet itemDataSet = new DataSet();
itemDataSet.ReadXml("books.xml");
dataGridView1.DataSource = itemDataSet;
dataGridView1.DataMember = "item";

13598

Try this:
itemDataSet.Tables(0).DefaultView.RowFilter =string.Format("{0} like *{1}*", searchField, textBox.text);

xRalf

ASKER

I tried this

DataSet itemDataSet = new DataSet();
string searchFiled = "Surname";
itemDataSet.ReadXml("books.xml");
itemDataSet.Tables[0].DefaultView.RowFilter = "Surname = 'Smith''";
//string.Format("{0} like *{1}*", searchFiled, tbxSearchName.Text);
dataGridView1.DataSource = itemDataSet;
dataGridView1.DataMember = "item";

the commented version is with syntax error

the uncommented version doesn't change the datagridview

13598

Try this:
DataSet itemDataSet = new DataSet();
string searchFiled = "Surname";
itemDataSet.ReadXml("books.xml");
dataGridView1.DataSource = itemDataSet;
dataGridView1.DataMember = "item";
itemDataSet.Tables[0].DefaultView.RowFilter = "Surname = 'Smith' ";
(I don't know why you had an extra quote after Smith)

xRalf

ASKER

I tried it, but this doesn not filter the datagridview,
it has every row as before filtering

13598

Could you add this line and let me know what you get?
msgbox (ItemDataSet.Tables(0).Name)

xRalf

ASKER

I tried this
MessageBox.Show(itemDataSet.Tables[0].ToString());

it writes : item

13598

What is item? Is that an existing field?
Are you sure Surname is the exact fieldname?

ASKER CERTIFIED SOLUTION

13598

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

xRalf

ASKER

Thank you. this works.

and do you know how to do more complex queries?

like myDataView.RowFilter = "every employee with average length of job = X"
like myDataView.RowFilter = "which employee had shortest length of job"

13598

Do you have a length of job field?

13598

To display from shortest to longest length of job for example based on a hiredate field, you could do something like this:
no filter at all to display all records.
Sort dataview by hiredate field descending:
myDataView.Sort = "Hiredate DESC";

xRalf

ASKER

I have two fields : date of job start, date of job end
and every client can have more than one job.

13598

You'll have to play with it but something like this:
myDataView.Sort = "Datediff("d",jobstart, jobend) ";

13598

If you want to only select employees who have only been working less than 7 days for example you would need something like:

DateTime cd = DateTime.Today.AddDays(-7);

System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("#");
sb.Append(cd.Year.ToString());
sb.Append("/");
sb.Append(cd.Month.ToString());
sb.Append("/");
sb.Append(cd.Day.ToString());
sb.Append("#");
myDataView.RowFilter = "jobstart >= " + sb.ToString();

Depending on what datatype your jobstart field is.

13598

If you want to select employees who have only been working less than 7 days for example you would need something like:

DateTime cd = DateTime.Today.AddDays(-7);

System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("#");
sb.Append(cd.Year.ToString());
sb.Append("/");
sb.Append(cd.Month.ToString());
sb.Append("/");
sb.Append(cd.Day.ToString());
sb.Append("#");
myDataView.RowFilter = "jobstart > " + sb.ToString();

Depending on what datatype your jobstart field is.

xRalf

ASKER

The problem is that one client have worked at more companies.

e.g.

John Smith IBM 1.2.2005 - 2.3.2006
John Smith Toshiba 4.5.2007 - 5.6.2008
John Smith Sony 6.7.2008-8.9.2010
Jack London IBM 5.8.2003-5.7.2004
Jack London Toshiba 5.8.-2005-5.9.2006

and I want to find e.g. clients with average employment length 12 mongths.
So, in the datagridview remains this
Jack London IBM 5.8.2003-5.7.2004
Jack London Toshiba 5.8.-2005-5.9.2006

13598

Define average. Based in your example John Smith would average to 12 months or more. Why don't you want to display him?

xRalf

ASKER

average(client) = (( jobend1 - jobstart1) + ... + (jobendN - jobstartN))/N
in mongths

Let's say that John Smith is average != 12 months and only Jack London is 12 months.

13598

For that you would need an aggregate field that calculates the average.