xRalf
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks cadsjo,
but it throws an exception
System.Data.SyntaxErrorExc eption was unhandled
Message="Syntax error: Missing operand before 'Mod' operator."
here
dvSearch.RowFilter = rowFilter;
but it throws an exception
System.Data.SyntaxErrorExc
Message="Syntax error: Missing operand before 'Mod' operator."
here
dvSearch.RowFilter = rowFilter;
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.
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.
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.
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.
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"
bindingsource(the name of your bindingsource for the datagridview).Filter = "Smith"
ASKER
The problem is that my datagridview is empty after the filtering
and I don't know why it is empty.
and I don't know why it is empty.
ASKER
If I use this version of filter
rowFilter = string.Format("{0} like *{1}*", searchField, textBox.text);
I get the same exception as before
rowFilter = string.Format("{0} like *{1}*", searchField, textBox.text);
I get the same exception as before
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.
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.
ASKER
I don't understand what is the binding source. What type is it and how to write it
in my sourcecode
in my sourcecode
Are you using a bound datagridview? How does the data get into your datagridview?
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";
DataSet itemDataSet = new DataSet();
itemDataSet.ReadXml("books
dataGridView1.DataSource = itemDataSet;
dataGridView1.DataMember = "item";
Try this:
itemDataSet.Tables(0).Defa ultView.Ro wFilter =string.Format("{0} like *{1}*", searchField, textBox.text);
itemDataSet.Tables(0).Defa
ASKER
I tried this
DataSet itemDataSet = new DataSet();
string searchFiled = "Surname";
itemDataSet.ReadXml("books .xml");
itemDataSet.Tables[0].Defa ultView.Ro wFilter = "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
DataSet itemDataSet = new DataSet();
string searchFiled = "Surname";
itemDataSet.ReadXml("books
itemDataSet.Tables[0].Defa
//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
Try this:
DataSet itemDataSet = new DataSet();
string searchFiled = "Surname";
itemDataSet.ReadXml("books .xml");
dataGridView1.DataSource = itemDataSet;
dataGridView1.DataMember = "item";
itemDataSet.Tables[0].Defa ultView.Ro wFilter = "Surname = 'Smith' ";
(I don't know why you had an extra quote after Smith)
DataSet itemDataSet = new DataSet();
string searchFiled = "Surname";
itemDataSet.ReadXml("books
dataGridView1.DataSource = itemDataSet;
dataGridView1.DataMember = "item";
itemDataSet.Tables[0].Defa
(I don't know why you had an extra quote after Smith)
ASKER
I tried it, but this doesn not filter the datagridview,
it has every row as before filtering
it has every row as before filtering
Could you add this line and let me know what you get?
msgbox (ItemDataSet.Tables(0).Nam e)
msgbox (ItemDataSet.Tables(0).Nam
ASKER
I tried this
MessageBox.Show(itemDataSe t.Tables[0 ].ToString ());
it writes : item
MessageBox.Show(itemDataSe
it writes : item
What is item? Is that an existing field?
Are you sure Surname is the exact fieldname?
Are you sure Surname is the exact fieldname?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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"
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"
Do you have a length of job field?
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";
no filter at all to display all records.
Sort dataview by hiredate field descending:
myDataView.Sort = "Hiredate DESC";
ASKER
I have two fields : date of job start, date of job end
and every client can have more than one job.
and every client can have more than one job.
You'll have to play with it but something like this:
myDataView.Sort = "Datediff("d",jobstart, jobend) ";
myDataView.Sort = "Datediff("d",jobstart, jobend) ";
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.ToStrin g());
sb.Append("/");
sb.Append(cd.Day.ToString( ));
sb.Append("#");
myDataView.RowFilter = "jobstart >= " + sb.ToString();
Depending on what datatype your jobstart field is.
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.ToStrin
sb.Append("/");
sb.Append(cd.Day.ToString(
sb.Append("#");
myDataView.RowFilter = "jobstart >= " + sb.ToString();
Depending on what datatype your jobstart field is.
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.ToStrin g());
sb.Append("/");
sb.Append(cd.Day.ToString( ));
sb.Append("#");
myDataView.RowFilter = "jobstart > " + sb.ToString();
Depending on what datatype your jobstart field is.
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.ToStrin
sb.Append("/");
sb.Append(cd.Day.ToString(
sb.Append("#");
myDataView.RowFilter = "jobstart > " + sb.ToString();
Depending on what datatype your jobstart field is.
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
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
Define average. Based in your example John Smith would average to 12 months or more. Why don't you want to display him?
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.
in mongths
Let's say that John Smith is average != 12 months and only Jack London is 12 months.
For that you would need an aggregate field that calculates the average.
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