Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DataGridView filter - combobox column

Posted on 2006-06-20
11
Medium Priority
?
14,116 Views
Last Modified: 2012-06-27
Hello,

I am working on implementing a full-text search for a datagridview.  Currently, I just use some backgruond logic to create a rowFilter statement and set this into the bindingSource.Filter property of the dataGridView's datasource.

This method, however, does not account for a DataGridView's combobox columns - since I am filtering the underlying datasource, I am really filtering on the value of the column, not on the text - what I really want to be able to do is filter by the text (hence the "full-text search"...).

My question is the following: Is there a way to filter the DataGridView so that I can incorporate the text of the cells, rather than the values?  

I would like to avoid 3rd party controls if at all possible, so please don't suggest this as a solution...

Thanks!
0
Comment
Question by:TLevin10
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 10

Expert Comment

by:jagadeesh_motamarri
ID: 16948715
OK...this might be a very good example for you - http://www.codeproject.com/cs/miscctrl/gridextensions.asp

____Jags.
0
 
LVL 3

Expert Comment

by:Dimkov
ID: 16950334
Instead of using bindingSource.Filter option, why don’t you build your SQL String?
there you can put anything you want after "where"

select * from mytable where firstcolumn="8" and secondcolumn="work"

or better
CString temp;
temp.Format("select * from mytable where firstcolumn=%d and secondcolumn='%s'", field1.iValue, field2.sValue);


Hope it helps,
Dimkov
0
 

Author Comment

by:TLevin10
ID: 16950488
hey Jags,

Thanks for the sample - I saw this one already, and it has a very simple way of dealing with combobox columns - it doesn't allow them!  Too bad, I liked the ideas...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:TLevin10
ID: 16950506
Hi Dimkov,

Unfortuntely, this is an action I have to avoid - doing a full text search of 50,000 records that are already at the client would be silly to reload them all from the server.  In addition, I would have to do a bunch of inner joins with other tables to ACTUALLY implement the full text search, since your solution still uses the ID to search by, which isn't a full-text search...

sadly, that solution isn't applicable in my situation.
0
 
LVL 3

Expert Comment

by:Dimkov
ID: 16957526
If I got it right, you have the recordset on the client, and you want to search through the data in it?

why don't you open another recordset and do an sql query on the first one?
then just swap the recordset beind displayed

I didn't got the part "full text search" - > can you give an example?
0
 

Author Comment

by:TLevin10
ID: 16959186
Well as far as I know its not possible to do a SQL query on a client side dataset in C#.  Once a dataset is pulled from the SQL server, the relationships not intrinsically downloaded are lost - in a sense, the dataset doesn't know whether the "category_id" column in one is related to the "categoryID" primary key of another column.  There is no knowledge of foreign key relationships outside of the SQL server itself, unless one provides it themself.  

The best I can do to describe what a "full text search" is that I convert all datatypes to a string, so a datetime datatype becomes a string representation, as does an int, etc.  Once everythign is a string, we can do a 'LIKE' filter on the client side to see if the strings contain the string we are searching for.  As an example:

ID                 Name                    Date                         Price
1                  Vendor                  11/25/2005               $400.00
2                  Wholesaler            1/07/2006                 $805.25
3                  Retailer                 1/21/2006                 $999.99

When the user views the table, they see string representations of every column.  The price is represented as currency, the ID as an int, and the date a short-date string.  In a full text search, if the user typed 'ven' they would get only row #1, since this is the only row where one of the columns contains the string 'ven'.  However, if they typed '05', they would get records 1 and 2, since the date in ID #1 contains '05' in the 2005, and the price in #2 cotains 05 in $805.25.  This way, the user is visibly searching through the records they see on screen and doens't have to have any knowledge of the data behind it.  

If you want to see a program which uses this principle, look at Apple iTunes - when you type in a "search" criteria, it searches on all visible columns and finds records which visibly match the criteria you are searching for.

I just realized I posted this question in the wrong category (should have been in Programming->C#) so I will see what it takes to get it moved there.
0
 

Author Comment

by:TLevin10
ID: 16965402
Well since I've spent a lot of time explaining my own question, I have finally solved it.  The actual implementation is somewhat tricky, but it involves creating a RowFilter string based upon the datasource of the grid being assumed as filterable, and then assuming that the combobox columns are fed by a dataset.  With this information, one can filter the underlying dataset of a combobox column and return a string of values, which are comma delimited, and include these in an "IN()" clause for the rowfilter.  The rest of the columns are filtered using a combination of "AND" statements and "OR" statements, with any regular text columns being converted to string representations and then filtered.  See the code below, which is used to build the RowFilter string, passed the search text and the datagridview:

----------------------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Data;

namespace FilterComboBoxTester
{
    class FullTextSearch
    {
        public static string CreateRowFilter(DataGridView dg, string text)
        {
            string rowfilter = "";
            if (text != "")
            {

                string searchstring = "";
                foreach (DataGridViewColumn d in dg.Columns)
                {
                    string p = "";
                    if (d.GetType() == typeof(DataGridViewComboBoxColumn))
                    {
                        DataGridViewComboBoxColumn cbc = d as DataGridViewComboBoxColumn;
                        DataSet underdata = cbc.DataSource as DataSet;
                       
                        string FilterString = "";
                        if (underdata != null)
                        {
                            string displayColumn = cbc.DisplayMember.Replace(underdata.Tables[0].TableName + ".", "");
                            string valueColumn = cbc.ValueMember.Replace(underdata.Tables[0].TableName + ".", "");
                            FilterString = GetFilterByID(underdata, valueColumn, displayColumn, text);
                        }

                        p = string.Format("{0} IN ({1})", cbc.DataPropertyName, FilterString);
                    }
                    else
                    {
                        if (d.IsDataBound && d.Visible)
                            p = string.Format("(CONVERT({0}, System.String) LIKE '*{{0}}*')", d.DataPropertyName);
                    }

                    if (p != "")
                    {
                        if (searchstring == "")
                            searchstring = p;
                        else
                            searchstring = string.Format("{0} OR {1}", searchstring, p);
                    }
                }

                string[] s = text.Replace("'", "''").Split(' ');
                for (int i = 0; i < s.Length; i++)
                {
                    string x = s[i];
                    string add = string.Format("(" + searchstring + ")", x);
                    if (i > 0)
                        rowfilter = string.Format("{0} AND {1}", rowfilter, add);
                    else
                        rowfilter = add;
                }
            }
            else
                rowfilter = "";


            return rowfilter;
        }

        public static string GetFilterByID(DataSet data, string valueColumn, string displayColumn, string searchString)
        {
            DataView dv = new DataView(data.Tables[0]);
            dv.RowFilter = string.Format("CONVERT({0}, System.String) LIKE '*{1}*'", displayColumn, searchString);
            string returnString = "";
            foreach (DataRowView r in dv)
            {
                if (returnString == "")
                    returnString = r[valueColumn].ToString();
                else
                    returnString = string.Format("{0}, {1}", returnString, r[valueColumn].ToString());
            }

            return returnString;
        }
    }
}
----------------------------------------------------------------------------------------------

The class can be used by calling the following (assuming dataGridBinder is a filterable component, such as the DataBinder or a DataView):

----------------------------------------------------------------------------------------------
string searchText = this.TextBox1.Text;
this.dataGridBinder.Filter = FullTextSearch.CreateRowFilter(this.dataGridView1, text);
----------------------------------------------------------------------------------------------

After that, the data grid is sorted according the text which is displayed, not just the underlying values!  I think this would be helpful for other EE members trying to do the same thing - please let me know if it helps you!

0
 

Author Comment

by:TLevin10
ID: 16985877
Oops - mixed up a quick bit there - call the method by using:

----------------------------------------------------------------------------------------------
string searchText = this.TextBox1.Text;
this.dataGridBinder.Filter = FullTextSearch.CreateRowFilter(this.dataGridView1, searchText);
----------------------------------------------------------------------------------------------
0
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
ID: 17025489
Closed, 500 points refunded.

GhostMod
Community Support Moderator
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question