Using a Textbox to filter from DataGridView and SQLite Database.

[M1kkelZU]

Hey all,
I'm trying to get some more user friendly things in my program done. Now I'm trying to filter by typing in a text box and it filters to what you are typing and shows the entire row.

This is what I currently have:

private void txtFilter_TextChanged(object sender, EventArgs e)
        {
            try
            {
                SQLiteConnection connect = new SQLiteConnection(connString);
                connect.Open();
                string whereQuery = "select Channel from Test where Channel '" + txtFilter.Text + "'";
                SQLiteDataAdapter adap = new SQLiteDataAdapter(whereQuery, connString);
                
                DataSet ds2 = new DataSet();
                adap.Fill(ds2);
                dataGridView1.DataSource = ds2.Tables[0];
            }
            catch (Exception ex)
            {
                MessageBox.Show("An error occured.\n" + ex.ToString(),"Error!");
            }
        }

I get an exception error though:

SQLite error
near "'N'": syntax error

// N is what I started typing to start the filter.

I don't exactly get what I've done wrong.
Any help would be much appreciated. Thanks


EDIT:
Ok, so I've edited the code now to this:

private void txtFilter_TextChanged(object sender, EventArgs e) // FILTER TODO
        {
                DataView view = new DataView();
                SQLiteConnection connect = new SQLiteConnection(connString);
                connect.Open();
                view.RowFilter = "Channel like '%" + txtFilter.Text + "%'";
        }

No more exception errors, just not what I want it to do. It just keeps everything there. Any ideas?

[Mikkeee]

I wouldn't open your db every time a keypress has been made due to the overhead involved. Instead I would either use a select statement on your data or set your View rowfilter. Your updated code unnecessarily creates a view, opens a connection, and set's the row filter but it's never assigned to your DataGridView. You should be fine with one line of code in there instead:

dataGridView1.RowFilter = "Channel like '%" + txtFilter.Text + "%'";

[M1kkelZU]

When I use that line of code, it can't find RowFilter. The rror message basically says that DataGridView doesn't contain a definition for RowFilter.
so I changed it a little bit

DataView view = new DataView();
            dataGridView1.DataSource = view;
            view.RowFilter = "Channel like '%" + txtFilterChannel.Text + "%'";

Now it searches but does not show the record with a channel name that starts with the letter I inserted in to my textbox.

[Mikkeee]

I was a little quick on the trigger....

DataTable dt = (DataTable)DataGridView1.DataSource;
dt.DefaultView.RowFilter = "Channel like '%" + txtFilterChannel.Text + "%'";

[M1kkelZU]

Using that gives me this error":

Unable to cast object of type 'System.Windows.Forms.BindingSource' to type 'System.Data.DataTable'.

on this Line:

DataTable dt = (DataTable)dataGridView1.DataSource;

I have no clue what it means lol.

[Mikkeee]

How are you setting your DataSource? In your code above you set it to a datatable. I ran this same filter on my database without issue.

[M1kkelZU]

Maybe I need to use dTable instead of making a new datatable.

As I've declared dTable first, I'll try that.

EDIT:
after doing that, it gives me this error:
Cannot find column [Channel].

The code:

            dataGridView1.DataSource = dTable;
            dTable.DefaultView.RowFilter = "Channel like '%" + txtFilterChannel.Text + "%'";

[Mikkeee]

Use this in your form load:

SQLiteConnection connect = new SQLiteConnection(connString);
connect.Open();
SQLiteDataAdapter adap = new SQLiteDataAdapter("select Channel from Test", connect);
 
DataSet ds = new DataSet();
adap.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];

And this in your TextChanged event:

DataTable dt = (DataTable)DataGridView1.DataSource;
dt.DefaultView.RowFilter = "Channel like '%" + txtFilterChannel.Text + "%'";

[M1kkelZU]

If I do that, I get the same error that it can't cast the object type Bindingsource to type DataTable. It should work though :L

EDIT:
Did it just a tad differently:

 private void txtFilterChannel_TextChanged(object sender, EventArgs e)
        {
            SQLiteConnection connect = new SQLiteConnection(connString);
            connect.Open();
            SQLiteDataAdapter adap = new SQLiteDataAdapter("select Channel from Test", connect);

            DataSet ds = new DataSet();
            adap.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];

            DataTable dt = (DataTable)dataGridView1.DataSource;
            dt.DefaultView.RowFilter = "Channel like '%" + txtFilterChannel.Text + "%'";
        }

Now, it only shows the name of the channel, not like the date or recording date. I have the feeling we've sorted it by column and not by row?

[Mikkeee]

You're query string is only selecting the Channel field. Just change it to "select * from Test" if you want all the fields.

[M1kkelZU]

Ah ok thanks, I really should be a bit more logical in these situations. thanks man :)

[henimex]

i have a similar issue but i would like to choose column via combobox

 dt.DefaultView.RowFilter = "product_name LIKE '%" + textBox4.Text + "%'";

that works fine but i want to use combobox1.text not product_name.
i have tried some knows formats but doesnt worked example

dt.DefaultView.RowFilter = "'"+comboBox1.Text+"' LIKE '%" + textBox4.Text + "%'";

need help on this thank you

compalte code is here (i have tried some other ways to do it but rowfilter just worked fine)

 set_connection();
                con.Open();
                com = con.CreateCommand();
                //string comtext = "SELECT * FROM products WHERE product_name LIKE'" + textBox4.Text + "'";
                //string comtext = "SELECT * FROM products WHERE product_name LIKE @pname";
                //com.Parameters.AddWithValue("@pname", "%Kar%");
                string comtext = "SELECT * FROM urunler";
                adapt = new SQLiteDataAdapter(comtext, con);
                ds.Reset();
                adapt.Fill(ds);
                dt = ds.Tables[0];
                dataGridView1.DataSource = dt;
                dt.DefaultView.RowFilter = "product_name LIKE '%" + textBox4.Text + "%'";
                //dt.DefaultView.RowFilter = "'"+comboBox1.Text+"' LIKE '%" + textBox4.Text + "%'";
                con.Close();

[Mikkeee]

The single quotes you have around your column name is the issue. Try this:

dt.DefaultView.RowFilter = "[" + comboBox1.Text + "] LIKE '%" + textBox4.Text + "%'";

[henimex]

The single quotes you have around your column name is the issue. Try this:

dt.DefaultView.RowFilter = "[" + comboBox1.Text + "] LIKE '%" + textBox4.Text + "%'";

thank you so much! i was really stucked before [] thank you