How can I make a search filter with multiple rows to search from like id and name?

Question

I need to make a filter like that for a system I'm developing but I can only make it work with a single row but when I try to add more rows and run the program it stops and giveme an error, this is the code I'm using:

public partial class frmconsultamateriaprima : Form
{
    DataView view = new DataView();

    public frmconsultamateriaprima()
    {
        InitializeComponent();
    }

    private void frmconsultamateriaprima_Load(object sender, EventArgs e)
    {
        DataTable datatable = new DataTable();
        SqlConnection con = new SqlConnection(@"Data Source=USER-PC;Initial Catalog=dbpuntodeventa;Integrated Security=True");
        con.Open();
        datatable.Load(new SqlCommand("select * from materiaprima", con).ExecuteReader());
        dataGridView1.DataSource = view = datatable.DefaultView;
        con.Close();
    }

    private void txtfiltro_TextChanged(object sender, EventArgs e)
    {
        view.RowFilter = (" nombre like  '%" + txtfiltro.Text + "%' or id like  '%" + txtfiltro.Text + "%'");
        if (txtfiltro.Text == "")
        view.RowFilter = string.Empty;

    }
}

could you try something like the following using single quotes for example `view.Select("nombre like '" & txtfiltro.Text & "%'")` etc.. following the same pattern for id .... possible duplicate but checkout this posting [how I can sear row](http://stackoverflow.com/questions/14497367/how-i-can-search-rows-in-a-datatable-with-a-searchstring) — MethodMan, Sep 30 '14 at 21:48
While @DJKRAZE is probably right, could you please post your full error? — crthompson, Sep 30 '14 at 21:49
I tried what drake said but I keep getting the error it says the like part cant be used for system.int32 and system.string — user36379, Sep 30 '14 at 22:03
You'd need to cast the ID to a varchar in order to get `LIKE` to work, but think about it...is it really appropriate as it stands? The event fires on TextChanged, so as soon as the user enters e.g. 4, the whole data set is filtered to return the records matching containing 4 in the `nombre` as well as records with IDs of e.g. 4, 14, 24, 40, 41, 42, 43, 124, 146, 144, 402...would that really be what they want? Perhaps implement some sort of delay so that the filter only applies to the ID as well if there's been 2 or 3 seconds elapsed since the key up. — dyson, Sep 30 '14 at 22:26

score 0 · Accepted Answer · answered Sep 30 '14 at 22:46

You can convert the id into a string using the CONVERT function like so...

private void txtfiltro_TextChanged(object sender, EventArgs e)
{
    view.RowFilter = " nombre like  '%" + txtfiltro.Text + 
          "%' or CONVERT(id,'System.String')  like  '%" + txtfiltro.Text + "%'";
}

How can I make a search filter with multiple rows to search from like id and name?

1 Answers1