Hi,

I have a DataGridView with a table loaded onto it. I have nine textboxes which will serve as a search feature for the DataGridView. What I want is that once text is entered, it goes through the relevant column filtering data as per what is inputed. This works perfectly and have severally typed code that still worked well despite not being exactly same code. My problem arises from the fact that after filtering using one textbox, when I enter the text in another textbox in order to filter results I had, it instead discards the previous filter and searches as if it were the first time filtering data. Here is the code that fills a DataSet and DataView.

        public void FillDataSet(string sql, DataSet dataSet, DataView dataView, string tableName) 
        {
            try
            {
                using (OleDbConnection connection = new OleDbConnection(databaseConnString.GetConnectionString))
                {
                    connection.Open();
                    using (OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connection))
                    {
                        adapter.Fill(dataSet, tableName);
                        dataView.Table = dataSet.Tables[tableName];
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "An unexpected error occurred", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

Here is the method that filters the DataView

        public void FilterDataSet(DataView dataView, string FilterCriteria, DataGridView dataGridView) 
        {
            DataTable tbl = new DataTable();
                dataView.RowFilter = FilterCriteria;
                dataGridView.DataSource = tbl;
        }

Here is an instance that I call the method to filter - this works perfectly

        private void tbPartNumber_TextChanged(object sender, EventArgs e)
        {
            filterPartNum = "PartNumber LIKE '%" + tbPartNumber.Text + "%'";

            populator.FilterDataSet(tblPartsDataView, filterPartNum, partDetailsDataGridView);
        }

I have attached a screenshot of my filter textboxes.

Please help me extend usability of this filter to be a multifilter,

Thanks in advance.

Recommended Answers

All 3 Replies

The following is an easy way to accomplish it.

Add the following:

Dictionary<string, string> filterDict = new Dictionary<string, string>();

private string whereClause = string.Empty;

Then call the following everytime "TextChanged" (for any TextBox) occurs:

Version 1:

This version assumes that all of the column data types are string data types (such as varchar, nvarchar, etc..)

private void updateFilterDict(string keyName, string value)
{
    string newDictValue = string.Empty;

    //value to store in dictionary
    newDictValue = keyName + " LIKE " + "'%" + value + "%'";

    //check if dictionary already 
    //contains the key
    if (filterDict.ContainsKey(keyName))
    {
        //if value is null or empty
        //remove key. Otherwise, update
        //key.
        if (String.IsNullOrEmpty(value))
        {
            filterDict.Remove(keyName);
        }//if
        else
        {
            filterDict[keyName] = newDictValue; 
        }//else
    }//if
    else
    {
        //add key to dictionary
        filterDict.Add(keyName, newDictValue);
    }//else

    int filterCount = 0;
    foreach (KeyValuePair<string,string> kvp in filterDict)
    {
        if (filterCount > 0)
        {
            whereClause += " AND " + kvp.Value;
        }//if
        else
        {
            whereClause = "WHERE " + kvp.Value;
        }//else

        filterCount += 1;
    }//foreach

    //ToDo: Update DataGridView results here
    Console.WriteLine("whereClause: " + whereClause);

}//updateFilterDict

Version 2:

This version allows one to specify different search behavior for different filters / data types. It is for demonstration purposes. It is a good idea to maintain consistency for a data type. If when a user enters a string value (ex: "12") and the results returned "contains" the value (ex:"12") in one filter, it is a good idea to do similarly in the other filters (when possible).

private void updateFilterDict(string keyName, string value)
{
    string newDictValue = string.Empty;

    switch (keyName)
    {
        case "SerialNumber":
            //SerialNumber starts with value
            newDictValue = keyName + " LIKE " + "'" + value + "%'";
            break;
        default:
            //contains value
            newDictValue = keyName + " LIKE " + "'%" + value + "%'";
            break;
    }//switch

    //check if dictionary already 
    //contains the key
    if (filterDict.ContainsKey(keyName))
    {
        //if value is null or empty
        //remove key. Otherwise, update
        //key.
        if (String.IsNullOrEmpty(value))
        {
            filterDict.Remove(keyName);
        }//if
        else
        {
            filterDict[keyName] = newDictValue;
        }//else
    }//if
    else
    {
        //add key to dictionary
        filterDict.Add(keyName, newDictValue);
    }//else


    int filterCount = 0;
    foreach (KeyValuePair<string, string> kvp in filterDict)
    {
        if (filterCount > 0)
        {
            whereClause += " AND " + kvp.Value;
        }//if
        else
        {
            whereClause = "WHERE " + kvp.Value;
        }//else

        filterCount += 1;
    }//foreach

    //ToDo: Update DataGridView results here
    Console.WriteLine("whereClause: " + whereClause);

}//updateFilterDict

Then call "updateFilterDict" in each of the "TextChanged" event handlers.

Usage: updateFilterDict("databaseColumnName", textBoxName.Text)

  • databaseColumnName should be the name of the column that exists in the database.

  • textBoxName should be the name of the TextBox that you are using to create a data filter (ex: tbPartNumber, tbSerialNumber, etc...)

Below are a few of them:

PartNumber:

Note: "PartNumber" in the code below should be the column name in the database that holds the part number.

private void tbPartNumber_TextChanged(object sender, EventArgs e)
{
    updateFilterDict("PartNumber", tbPartNumber.Text);
}

SerialNumber:

Note: "SerialNumber" in the code below should be the column name in the database that holds the serial number.

private void tbSerialNumber_TextChanged(object sender, EventArgs e)
{
    updateFilterDict("SerialNumber", tbSerialNumber.Text);
}

Tag:

Note: "Tag" in the code below should be the column name in the database that holds the tag.

private void tbTag_TextChanged(object sender, EventArgs e)
{
    updateFilterDict("Tag", tbTag.Text);
}

Above, in "updateFilterDict", change from:

whereClause = "WHERE " + kvp.Value;

To:

whereClause = kvp.Value;

Thanks a lot for the code, I however would like to use the code I have created for the multi-filter. Here is where I really need help. I would like to use DataSet, DataView and DataTable to solve this. I want to load the Ms Access db to the DataSet, then find a way to 'chain' DataView to the DataTable such that when it (DataView) is filtered, changes are saved to the DataTable. This way as filters change so does table I load. I also want to have it such that when I removed all text then all records that have any value with respect to particular individual filter are saved.

I am able to use just one textbox for filtering as each subsequent filter ignores changes used by previous filter. Help me add ability to filter from 'esults found'

Thanks once again for your answer.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.