By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,844 Members | 1,889 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,844 IT Pros & Developers. It's quick & easy.

Using a Textbox to filter from DataGridView and SQLite Database.

P: 80
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:
Expand|Select|Wrap|Line Numbers
  1. private void txtFilter_TextChanged(object sender, EventArgs e)
  2.         {
  3.             try
  4.             {
  5.                 SQLiteConnection connect = new SQLiteConnection(connString);
  6.                 connect.Open();
  7.                 string whereQuery = "select Channel from Test where Channel '" + txtFilter.Text + "'";
  8.                 SQLiteDataAdapter adap = new SQLiteDataAdapter(whereQuery, connString);
  9.  
  10.                 DataSet ds2 = new DataSet();
  11.                 adap.Fill(ds2);
  12.                 dataGridView1.DataSource = ds2.Tables[0];
  13.             }
  14.             catch (Exception ex)
  15.             {
  16.                 MessageBox.Show("An error occured.\n" + ex.ToString(),"Error!");
  17.             }
  18.         }
I get an exception error though:
Expand|Select|Wrap|Line Numbers
  1. SQLite error
  2. 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:
Expand|Select|Wrap|Line Numbers
  1. private void txtFilter_TextChanged(object sender, EventArgs e) // FILTER TODO
  2.         {
  3.                 DataView view = new DataView();
  4.                 SQLiteConnection connect = new SQLiteConnection(connString);
  5.                 connect.Open();
  6.                 view.RowFilter = "Channel like '%" + txtFilter.Text + "%'";
  7.         }
No more exception errors, just not what I want it to do. It just keeps everything there. Any ideas?
Mar 11 '13 #1

✓ answered by Mikkeee

Use this in your form load:
Expand|Select|Wrap|Line Numbers
  1. SQLiteConnection connect = new SQLiteConnection(connString);
  2. connect.Open();
  3. SQLiteDataAdapter adap = new SQLiteDataAdapter("select Channel from Test", connect);
  4.  
  5. DataSet ds = new DataSet();
  6. adap.Fill(ds);
  7. dataGridView1.DataSource = ds.Tables[0];
  8.  
And this in your TextChanged event:
Expand|Select|Wrap|Line Numbers
  1. DataTable dt = (DataTable)DataGridView1.DataSource;
  2. dt.DefaultView.RowFilter = "Channel like '%" + txtFilterChannel.Text + "%'";
  3.  

Share this Question
Share on Google+
13 Replies


Mikkeee
P: 94
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:
Expand|Select|Wrap|Line Numbers
  1. dataGridView1.RowFilter = "Channel like '%" + txtFilter.Text + "%'";
  2.  
Mar 11 '13 #2

P: 80
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
Expand|Select|Wrap|Line Numbers
  1. DataView view = new DataView();
  2.             dataGridView1.DataSource = view;
  3.             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.
Mar 11 '13 #3

Mikkeee
P: 94
I was a little quick on the trigger....
Expand|Select|Wrap|Line Numbers
  1. DataTable dt = (DataTable)DataGridView1.DataSource;
  2. dt.DefaultView.RowFilter = "Channel like '%" + txtFilterChannel.Text + "%'";
  3.  
Mar 11 '13 #4

P: 80
Using that gives me this error":
Expand|Select|Wrap|Line Numbers
  1. Unable to cast object of type 'System.Windows.Forms.BindingSource' to type 'System.Data.DataTable'.
on this Line:
Expand|Select|Wrap|Line Numbers
  1. DataTable dt = (DataTable)dataGridView1.DataSource;
I have no clue what it means lol.
Mar 11 '13 #5

Mikkeee
P: 94
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.
Mar 11 '13 #6

P: 80
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:
Expand|Select|Wrap|Line Numbers
  1.             dataGridView1.DataSource = dTable;
  2.             dTable.DefaultView.RowFilter = "Channel like '%" + txtFilterChannel.Text + "%'";
Mar 11 '13 #7

Mikkeee
P: 94
Use this in your form load:
Expand|Select|Wrap|Line Numbers
  1. SQLiteConnection connect = new SQLiteConnection(connString);
  2. connect.Open();
  3. SQLiteDataAdapter adap = new SQLiteDataAdapter("select Channel from Test", connect);
  4.  
  5. DataSet ds = new DataSet();
  6. adap.Fill(ds);
  7. dataGridView1.DataSource = ds.Tables[0];
  8.  
And this in your TextChanged event:
Expand|Select|Wrap|Line Numbers
  1. DataTable dt = (DataTable)DataGridView1.DataSource;
  2. dt.DefaultView.RowFilter = "Channel like '%" + txtFilterChannel.Text + "%'";
  3.  
Mar 11 '13 #8

P: 80
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:
Expand|Select|Wrap|Line Numbers
  1.  private void txtFilterChannel_TextChanged(object sender, EventArgs e)
  2.         {
  3.             SQLiteConnection connect = new SQLiteConnection(connString);
  4.             connect.Open();
  5.             SQLiteDataAdapter adap = new SQLiteDataAdapter("select Channel from Test", connect);
  6.  
  7.             DataSet ds = new DataSet();
  8.             adap.Fill(ds);
  9.             dataGridView1.DataSource = ds.Tables[0];
  10.  
  11.             DataTable dt = (DataTable)dataGridView1.DataSource;
  12.             dt.DefaultView.RowFilter = "Channel like '%" + txtFilterChannel.Text + "%'";
  13.         }
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?
Mar 11 '13 #9

Mikkeee
P: 94
You're query string is only selecting the Channel field. Just change it to "select * from Test" if you want all the fields.
Mar 11 '13 #10

P: 80
Ah ok thanks, I really should be a bit more logical in these situations. thanks man :)
Mar 11 '13 #11

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

Expand|Select|Wrap|Line Numbers
  1.  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

Expand|Select|Wrap|Line Numbers
  1. 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)

Expand|Select|Wrap|Line Numbers
  1.  set_connection();
  2.                 con.Open();
  3.                 com = con.CreateCommand();
  4.                 //string comtext = "SELECT * FROM products WHERE product_name LIKE'" + textBox4.Text + "'";
  5.                 //string comtext = "SELECT * FROM products WHERE product_name LIKE @pname";
  6.                 //com.Parameters.AddWithValue("@pname", "%Kar%");
  7.                 string comtext = "SELECT * FROM urunler";
  8.                 adapt = new SQLiteDataAdapter(comtext, con);
  9.                 ds.Reset();
  10.                 adapt.Fill(ds);
  11.                 dt = ds.Tables[0];
  12.                 dataGridView1.DataSource = dt;
  13.                 dt.DefaultView.RowFilter = "product_name LIKE '%" + textBox4.Text + "%'";
  14.                 //dt.DefaultView.RowFilter = "'"+comboBox1.Text+"' LIKE '%" + textBox4.Text + "%'";
  15.                 con.Close();
2 Weeks Ago #12

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

Expand|Select|Wrap|Line Numbers
  1. dt.DefaultView.RowFilter = "[" + comboBox1.Text + "] LIKE '%" + textBox4.Text + "%'";
2 Weeks Ago #13

P: 2
@Mikkeee
thank you so much! i was really stucked before [] thank you
2 Weeks Ago #14

Post your reply

Sign in to post your reply or Sign up for a free account.