C# MySQL Binding source throws an Evaluation exception

Question

I'm making an application in C# which connects to an MySQL database and lists the results. I've added two text boxes which should allow users to filter the data by user id and user name/surname on textchanged events for both textboxes. The code looks like this

        private void tbSearchStudents_TextChanged(object sender, EventArgs e)
        {
            BindingSource binding = new BindingSource();
            binding.DataSource = dataGridViewStudents.DataSource;
            this.tsProgressBar.Visible = true;
            this.tsProgressBar.Style = ProgressBarStyle.Marquee;
            binding.Filter = @"students.uid LIKE '%" + this.tbSearchId.Text + @"%' 
                               AND CONCAT_WS(' ' , students.name, students.surname) 
                               LIKE '%" +  this.tbSearchName.Text + "%'";
            this.dataGridViewStudents.DataSource = binding;
            this.tsProgressBar.Visible = false;
        }

However when i run my program and try to enter something in either of those textboxes i get an EvaluationException exception:
The expression contains undefined function call CONCAT_WS().

When i only used the id textbox before i added the name/surname one and used only students.uid LIKE '%" + this.tbSearchId.Text + @"%' everything worked fine.

What is going on? It accepts LIKE expression but it doesn't recognize CONCAT nor CONCAT_WS functions?

Steve · Accepted Answer · 2013-06-01 12:45:48Z

1

Because CONCAT_WS is the MySql function to concatenate strings but is not a function known to the NET Framework and applicable to the Filter property of a BindingSource. In this context you are not asking the MySql database engine to parse your filter, you are talking with the NET Framework and you should follow its rules.

You need to use the + operator applied to strings as explained in the Expression property for the DataColumn object (The BindingSource.Filter property follows the same rules)

binding.Filter = @"students.uid LIKE '%" + this.tbSearchId.Text + @"%' 
                   AND students.name + ' ' + students.surname) 
                   LIKE '%" +  this.tbSearchName.Text + "%'";

also, pay attention to single quotes inside the your textboxes, you could receive another syntax error if there is a student surname like O'Hara. For safety add a

...
AND students.name + ' ' + students.surname) 
LIKE '%" +  this.tbSearchName.Text,Replace("'", "''") + "%'";
....

edited Jun 1, 2013 at 12:45

answered Jun 1, 2013 at 12:33

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MrPlow Over a year ago

That worked. Thanks! The LIKE threw me off and I thought that I was somehow comunicating with MySQL

Collectives™ on Stack Overflow

C# MySQL Binding source throws an Evaluation exception

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related