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
LVL 6
xRalfAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
13598Connect With a Mentor Commented:
You could try this:
DataView myDataView = new DataView(itemDataSet.Tables[0]);

myDataView.RowFilter = "Surname = 'Smith'";

this.dataGridView1.DataSource = myDataView ;
0
 
cadsjoConnect With a Mentor Commented:
You can create a dataview with a rowfilter.

You can put this in the textchanged event of your textbox or in the clickevent of a button

string searchField = "Surname";
string rowFilter = string.Empty;

rowFilter = string.Format("{0} like %{1}%", searchField, textBox.text);

DataView dvSearch = new DataView()

dvSearch.RowFilter = rowFilter;
DatagridviewYouUse.DataSource = dvSearch;

I typed on the fly, so I didn't check if it really compiles.

HTH
Cadsjo
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
xRalfAuthor Commented:
Thanks cadsjo,

but it throws an exception

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

here
dvSearch.RowFilter = rowFilter;
0
 
xRalfAuthor Commented:
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.
0
 
cadsjoCommented:
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.
0
 
13598Commented:
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"
0
 
xRalfAuthor Commented:
The problem is that my datagridview is empty after the filtering
and I don't know why it is empty.
0
 
xRalfAuthor Commented:
If I use this version of filter
rowFilter = string.Format("{0} like *{1}*", searchField, textBox.text);

I get the same exception as before
0
 
13598Commented:
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.
0
 
xRalfAuthor Commented:
I don't understand what is the binding source. What type is it and how to write it
in my sourcecode
0
 
13598Commented:
Are you using a bound datagridview?  How does the data get into your datagridview?
0
 
xRalfAuthor Commented:
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";
0
 
13598Commented:
Try this:
itemDataSet.Tables(0).DefaultView.RowFilter =string.Format("{0} like *{1}*", searchField, textBox.text);
0
 
xRalfAuthor Commented:
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
0
 
13598Commented:
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)
0
 
xRalfAuthor Commented:
I tried it, but this doesn not filter the datagridview,
it has every row as before filtering
0
 
13598Commented:
Could you add this line and let me know what you get?
msgbox (ItemDataSet.Tables(0).Name)
0
 
xRalfAuthor Commented:
I tried this
  MessageBox.Show(itemDataSet.Tables[0].ToString());

it writes : item
0
 
13598Commented:
What is item? Is that an existing field?
Are you sure Surname is the exact fieldname?
0
 
xRalfAuthor Commented:
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"
0
 
13598Commented:
Do you have a length of job field?
0
 
13598Commented:
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";
0
 
xRalfAuthor Commented:
I have two fields : date of job start, date of job end
and every client can have more than one job.
0
 
13598Commented:
You'll have to play with it but something like this:
myDataView.Sort = "Datediff("d",jobstart, jobend) ";
0
 
13598Commented:
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.
0
 
13598Commented:
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.
0
 
xRalfAuthor Commented:
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
0
 
13598Commented:
Define average. Based in your example John Smith would average to 12 months or more. Why don't you want to display him?
0
 
xRalfAuthor Commented:
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.
0
 
13598Commented:
For that you would need an aggregate field that calculates the average.
0
All Courses

From novice to tech pro — start learning today.