Can't get this query to work with Dataview.RowFilter (data from SQL Server CE)

0
votes

I am trying to run query on a dataview using DataView.RowFilter and trying to get:

First name (string) last name (string) Checked (bit)

and display them in a datagridview. I don't have any issues to query all people/all checked people/all unchecked people, and I get valid results. for checked people for example I do: 

dv.RowFilter = "Checked = 1";

I also made a search (textbox) from which I type a value and get an updated dataview which also update the datagridview.

so I can get this to work:

dv.RowFilter = "FirstName like '%" + textBox1.Text + "%'" + "OR LastName like '%" + textBox1.Text + "%'";

So my problem is I can't get this following query to work, it combines the last query above but I only wish to get the rows where they are checked:

dv.RowFilter = "FirstName like '%" + textBox1.Text + "%'" + "OR LastName like '%" + textBox1.Text + "%'" + "WHERE Checked = 1";

This doesn't work either:

dv.RowFilter = "Checked = 1 AND FirstName like '%" + textBox1.Text + "%'" + " OR LastName like '%" + textBox1.Text + "%'";

I've tried many things and searched all over for how to combine AND and OR but nothing gave me a result. there is always an issue with 'Checked'. sometimes the debugger says: "Missing operand after 'Checked' operator." I tried to fix it but couldn't, data origin is from SQL Server CE (just to mention).

Thanks in advance for any help!

1
sqlsql-server-cedataview

1 Answers

1
votes

Try this:

dv.RowFilter = "(FirstName like '%" + 
                 textBox1.Text + "%'" + " OR LastName like '%" + 
                 textBox1.Text + "%') AND Checked = 1";

Without brackets it defaults to:

"FirstName like '%" + 
 textBox1.Text + "%'" + " OR (LastName like '%" + 
 textBox1.Text + "%' AND Checked = 1)";