"Cannot perform '=' operation on System.Int32 and System.String" while performing a search

Question

Trying to search through a DataList, with the parameters being movie genres (loaded through a database, so no switch case) and a name

protected void ButtonFilter_Click(object sender, EventArgs e)
    {
        string filter = "";
        int selectedCount = 0;
        for (int i = 0; i < this.CheckBoxListGenres.Items.Count; i++)
            if (this.CheckBoxListGenres.Items[i].Selected)
                selectedCount++;
        if (selectedCount > 0)
        {
            filter = "GenreID=";
            int n = 0; //Used to determine to which genre the loop has arrived
            for (int i = 0; i < this.CheckBoxListGenres.Items.Count; i++)
            {
                if (this.CheckBoxListGenres.Items[i].Selected)
                {
                    if (n > 0 && n < selectedCount)
                        filter += " AND ";
                    filter+="'*"+this.CheckBoxListGenres.Items[i].Value.ToString()+"*'";
                    n++;

                }
            }

            if (this.TextBoxMovieName.Text!="")
                filter += " AND MovieName LIKE '*" + this.TextBoxMovieName.Text + "*'";
            DataTable dataTable = ((DataSet)Application["DataSetMovies"]).Tables[0];
            DataView dataView = new DataView(dataTable);
            filter = Convert.ToString(filter);
            dataView.RowFilter = filter; //!Getting the error here!
            this.DataListMovies.DataSource = dataView;
            this.DataListMovies.DataBind();
        }
    }

Tried debugging, the string itself seems fine, so I tried using Convert.ToString() on filter, just to make sure but it doesn't matter. Help?

Thanks in advance

Fizzy

It looks like you are trying to add a filter that checks if GenreID='2' or something, perhaps it is treating it as a string but the column is integer, try removing all the extraneous formatting and create a filter that just does GenreID=2. Can you provide an example of what filter contains when it is being set? Also as Igor points out, your chaining of AND expressions cannot just daisy chain values, you need to specify the field each time too: GenreID=2 AND GenreID = 3. – Bulbous

1. Is your GenreID a text or nummeric field? 2. Have you tried to check filter expression before assigning? According to your code you can get GenreID='AAA' AND 'BBB' AND 'CCC' which is not valid expression – Endogen

The error is not about ` filter` being a wrong type. The error is about the contents of ` filter` itself – Pernas

@SonerGönül The error would be on that line if the exception was from the view trying to apply the filter. – Bulbous

@AdamHouldsworth That's make sense surely. I thought this is a compiler time error rather than runtime. – Okun

@Acesii Please post an example of what filter contains just before you try to set it to the data view. It is very likely incorrectly formed. Please see: csharp-examples.net/dataview-rowfilter – Bulbous

Answer 1

This is not compile time error this is runtime error.

Well in your database the thing for which you are applying filter is type of Int32..

For example if you have something like Num and it is of Int32 but you do somthing like below :-

dv.RowFilter = "Num = '7097'" ////This will have error

It will throw error because it will require :-

dv.RowFilter = "Num = 7097" ////This is correct

So in your case you have GenreID which is of Int32 but in your code you are providing some string against it.

Echols

This error can also occur simply because the length of the variable ('7097') exceeds the string length allowed in the column ('Num') in the DataTable - I have tested this. – Joyner

Answer 2

Probably your GenreID column is an INTEGER and you're comparing it with a STRING as in '**'. In this sample I used the INoperator to list all values at once without the AND operator between them, what would cause a logic error as none items would have GenreID=1 and GenreID=2:

protected void ButtonFilter_Click(object sender, EventArgs e)
{
    string filter = "1=1";
    if (this.CheckBoxListGenres.Items.OfType<ListItem>().Any(i => i.Selected))
        filter += String.Format(" AND GenreID IN ({0})'",
            String.Join(",", this.CheckBoxListGenres.Items.OfType<ListItem>()
                .Where(i => i.Selected).Select(i => i.Value)));

    if (this.TextBoxMovieName.Text != "")
        filter += " AND MovieName LIKE '%" + this.TextBoxMovieName.Text + "%'";

    DataTable dataTable = ((DataSet)MediaTypeNames.Application["DataSetMovies"]).Tables[0];
    DataView dataView = new DataView(dataTable);
    dataView.RowFilter = filter;
    DataListMovies.DataSource = dataView;
    DataListMovies.DataBind();
}

Labialized

Answer 3

Filter in your condition might be invalid so Use OR at the place of AND

Use % , insted of * in LIKE Operator

I.E filter += " AND MovieName LIKE '%" + this.TextBoxMovieName.Text + "%'";

protected void ButtonFilter_Click(object sender, EventArgs e)
    {
        string filter = "";
        int selectedCount = 0;
        for (int i = 0; i < this.CheckBoxListGenres.Items.Count; i++)
            if (this.CheckBoxListGenres.Items[i].Selected)
                selectedCount++;
        if (selectedCount > 0)
        {
            filter = "GenreID=";
            int n = 0; //Used to determine to which genre the loop has arrived
            for (int i = 0; i < this.CheckBoxListGenres.Items.Count; i++)
            {
                if (this.CheckBoxListGenres.Items[i].Selected)
                {
                    if (n > 0 && n < selectedCount)
                        filter += " OR ";
                    filter+="'%"+this.CheckBoxListGenres.Items[i].Value.ToString()+"%'";
                    n++;

                }
            }

            if (this.TextBoxMovieName.Text!="")
                filter += " OR MovieName LIKE '%" + this.TextBoxMovieName.Text + "%'";
            DataTable dataTable = ((DataSet)Application["DataSetMovies"]).Tables[0];
            DataView dataView = new DataView(dataTable);
            filter = Convert.ToString(filter);
            dataView.RowFilter = filter;
            this.DataListMovies.DataSource = dataView;
            this.DataListMovies.DataBind();
        }
    }

Syncytium

* and % can be used interchangeably: msdn.microsoft.com/en-us/library/… – Bulbous

Search works with OR now, thanks (at least with the criteria I've put) – Fizzy

Answer 4

DataRow filteredRows = dt.Select(“Name LIKE '%” + searchstring + “%’

OR

convert(Age, System.String) like '%” + searchstring + “%'”);

It works for me

Airlia