none
Search in DataGridView colum RRS feed

  • Question

  • Hi,I have a DataGridview and i want do a search in one of colums , but i want to my search to be like the command like in TSQL.
    for example if u enter B in my colum shows the all words with B.
    How i must do it?
    Friday, October 6, 2017 9:08 PM

Answers

  • Well as you have not indicated enough let's say you loaded the DataGridView using a DataTable which is set to the DataSource of a BindingSource component and the BindingSource is set to the DataSource of the DataGridView we can use the Filter method of the BindingSource to filter a specific column/Field or you can use filter off the DataView property of the DataTable mentioned above as shown here.

    If you have no set up a DataSource for the DataGridView there is nothing natively provided to do a like condition.

    Edit (found time to write an example)

    Here the BindingSource is optional but a BindingSource offers a good deal of nice functionality which is not shown here at all, I suggest checking it out.

    Okay, here I demo a starts with and contains samples plus a filter clear.

    The Operations class reads data from a database into a DataTable, once loaded there is no attachments back to the database so we are simply dealing with a container (the DataTable) to house information which come from from other places besides a Database.

    using System;
    using System.Data;
    using System.Windows.Forms;
    
    namespace DataGridViewRowFilter
    {
        public partial class Form1 : Form
        {
            BindingSource bs = new BindingSource();
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                if (!string.IsNullOrWhiteSpace(textBox1.Text))
                {
                    ((DataTable)bs.DataSource).DefaultView.RowFilter = $"ContactName LIKE '{textBox1.Text}%'";
                }           
            }
            private void button2_Click(object sender, EventArgs e)
            {
                if (!string.IsNullOrWhiteSpace(textBox2.Text))
                {
                    ((DataTable)bs.DataSource).DefaultView.RowFilter = $"ContactName LIKE '%{textBox2.Text}%'";
                }
            }
            private void button3_Click(object sender, EventArgs e)
            {
                ((DataTable)bs.DataSource).DefaultView.RowFilter = "";
            }
            private void Form1_Load(object sender, EventArgs e)
            {
                var ops = new Operations();
                bs.DataSource = ops.Read();
                dataGridView1.DataSource = bs;
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Friday, October 6, 2017 10:31 PM
    Moderator

All replies

  • Well as you have not indicated enough let's say you loaded the DataGridView using a DataTable which is set to the DataSource of a BindingSource component and the BindingSource is set to the DataSource of the DataGridView we can use the Filter method of the BindingSource to filter a specific column/Field or you can use filter off the DataView property of the DataTable mentioned above as shown here.

    If you have no set up a DataSource for the DataGridView there is nothing natively provided to do a like condition.

    Edit (found time to write an example)

    Here the BindingSource is optional but a BindingSource offers a good deal of nice functionality which is not shown here at all, I suggest checking it out.

    Okay, here I demo a starts with and contains samples plus a filter clear.

    The Operations class reads data from a database into a DataTable, once loaded there is no attachments back to the database so we are simply dealing with a container (the DataTable) to house information which come from from other places besides a Database.

    using System;
    using System.Data;
    using System.Windows.Forms;
    
    namespace DataGridViewRowFilter
    {
        public partial class Form1 : Form
        {
            BindingSource bs = new BindingSource();
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                if (!string.IsNullOrWhiteSpace(textBox1.Text))
                {
                    ((DataTable)bs.DataSource).DefaultView.RowFilter = $"ContactName LIKE '{textBox1.Text}%'";
                }           
            }
            private void button2_Click(object sender, EventArgs e)
            {
                if (!string.IsNullOrWhiteSpace(textBox2.Text))
                {
                    ((DataTable)bs.DataSource).DefaultView.RowFilter = $"ContactName LIKE '%{textBox2.Text}%'";
                }
            }
            private void button3_Click(object sender, EventArgs e)
            {
                ((DataTable)bs.DataSource).DefaultView.RowFilter = "";
            }
            private void Form1_Load(object sender, EventArgs e)
            {
                var ops = new Operations();
                bs.DataSource = ops.Read();
                dataGridView1.DataSource = bs;
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Friday, October 6, 2017 10:31 PM
    Moderator
  • Thanks,

    This is my code but i get error : ( System.NullReferenceException: 'Object reference not set to an instance of an object.' )

      private void tbGoodsSearch_TextChanged(object sender, EventArgs e)
            {

    CGoodsList oGoodsList = new CGoodsList(); if (!string.IsNullOrWhiteSpace(tbGoodsSearch.Text)) { ((DataTable)bs.DataSource).DefaultView.RowFilter = $"GoodsName LIKE '{tbGoodsSearch.Text}%'"; } bs.DataSource = oGoodsList.GetGoodsList(); dgv.DataSource = bs;

    }


    Saturday, October 7, 2017 4:23 PM
  • It would be wise to indicate the line which throw the exception. 

    Here is an example (modified from my first reply that does what you are doing that works. I added a TextBox named txtSearchContactNames.

    private void TxtSearchContactNames_TextChanged(object sender, EventArgs e)
    {
        if (!string.IsNullOrWhiteSpace(txtSearchContactNames.Text))
        {
            ((DataTable)bs.DataSource).DefaultView.RowFilter = $"ContactName LIKE '{txtSearchContactNames.Text}%'";
        }
        else
        {
            ((DataTable)bs.DataSource).DefaultView.RowFilter = "";
        }
    }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, October 7, 2017 4:36 PM
    Moderator
  • Still the same error:

      private void frmGoodsList_Load(object sender, EventArgs e)
            {
                GoodsUnit.DataSource = oGoodsList.dgvGoodsUnit(1);
                dgv.DataSource = oGoodsList.GetGoodsList();
    
            }
    
      private void tbGoodsSearch_TextChanged(object sender, EventArgs e)
            {
                CGoodsList oGoodsList = new CGoodsList();
                if (!string.IsNullOrWhiteSpace(tbGoodsSearch.Text))
                {
                    ((DataTable)bs.DataSource).DefaultView.RowFilter = $"GoodsName LIKE '{tbGoodsSearch.Text}%'";
                }
                else
                {
                    ((DataTable)bs.DataSource).DefaultView.RowFilter = "";
                }
    
                bs.DataSource = oGoodsList.GetGoodsList();
                dgv.DataSource = bs;
            }

    I get error in this line:

     ((DataTable)bs.DataSource).DefaultView.RowFilter = $"GoodsName LIKE '{tbGoodsSearch.Text}%'";

    Saturday, October 7, 2017 6:24 PM
  • In regards to your last reply, the only way that is possible is if the BindingSource DataSource has not been set.

    For me I can do this by not setting the DataSource as shown below

    We can avoid this...

    private void TxtSearchContactNames_TextChanged(object sender, EventArgs e)
    {
        if (bs.DataSource != null && bs.Current != null)
        {
            if (!string.IsNullOrWhiteSpace(txtSearchContactNames.Text))
            {
                ((DataTable)bs.DataSource).DefaultView.RowFilter = $"ContactName LIKE '{txtSearchContactNames.Text}%'";
            }
            else
            {
                ((DataTable)bs.DataSource).DefaultView.RowFilter = "";
            }
        }
        else
        {
            // we fall here if the DataSource or Current (row) is null
        }
    }
    One must use assertion to guard against exceptions such as these :-)


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, October 7, 2017 6:33 PM
    Moderator
  • It worked thank you so much.

    Can you explain this code to me? ( sorry if its off-tpoic )

     tbGoodsSearch.TextChanged +=  tbGoodsSearch_TextChanged;

    Saturday, October 7, 2017 6:47 PM
  • It's setting up the TextChanged event. See the following.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, October 7, 2017 6:49 PM
    Moderator
  • I am very grateful
    Saturday, October 7, 2017 6:56 PM
  • I am very grateful
    Your welcome :-)

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, October 7, 2017 7:03 PM
    Moderator