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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cadsjoCommented:
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
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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
13598Commented:
You could try this:
DataView myDataView = new DataView(itemDataSet.Tables[0]);

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

this.dataGridView1.DataSource = myDataView ;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.