'Cannot perform 'like' operation on system.single and system.string.'

Question

1.00/5 (1 vote)

See more:

, +

i am building a win form app using access database as data-source , and in the search button of my winform i have this code"

C#

private void searchAccessDatabase()
        {
            if (string.IsNullOrEmpty(KeywordTextBox.Text.Trim()))
            {
                Refreshdata();
                return;
            }
            string strkeyword = KeywordTextBox.Text.Trim().ToString();

            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("(Convert(ID,'System.String') LIKE '" + "{0}" + "')", strkeyword);
            sb.AppendFormat("OR (Customer_Name LIKE '*" + "{0}" + "*')", strkeyword);
            sb.AppendFormat("OR (Complaint_Number LIKE '*" + "{0}" + "*')", strkeyword);
            sb.AppendFormat("(Convert(Size,'System.String') LIKE '" + "{0}" + "')", strkeyword);
            sb.AppendFormat("OR (Material_Number LIKE '*" + "{0}" + "*')", strkeyword);
            sb.AppendFormat("OR (Nature_Of_Problem LIKE '*" + "{0}" + "*')", strkeyword);
            sb.AppendFormat("OR (Spool_Type LIKE '*" + "{0}" + "*')", strkeyword);
            string strFilter = sb.ToString();
           material_Return_DataBindingSource.Filter = strFilter;

            if (material_Return_DataBindingSource.Count != 0)
            {
                dataGridView1.DataSource = material_Return_DataBindingSource;
            }
            else
            {
                MessageBox.Show("No Records Found", "Search Result", MessageBoxButtons.OK,
                    MessageBoxIcon.Exclamation);

                Refreshdata();
                return;
            }
        }

But during running the below error is coming : 'Cannot perform 'Like' operation on System.Single and System.String.'

I know that the error is related to the format type of the cells and the search type i am using but i am unable to correct them as i am still learning on this : Anybody please correct me where i am wrong.

What I have tried:

This is the sample data table : which will help you see the sample data i am entering to the table , with which i want to search

ID		10
Returned_Date		23-Mar-20
Customer_Name		XXXX
Complaint_Number		C20-02
Spool_Number		QF18087829
Size		330
12_NC		Q123456
Material_Number		12345P
DOP		12-03-2020
Qty_Sent_Grams		1.371000051
Qty_Sent_Meter		1.371000051
Qty_Returned_Grams		0.305000007
Qty_Returned_Meter		0.305000007
Difference_Qty_Grams		0
Difference_Qty_Meters		0
Nature_Of_Problem		wwwww
Type _of_material		EPS
Spool_Type		S200(ABS)
Remarks		wwwww

Posted 11-Aug-20 19:26pm

Member 14898617

Updated 11-Aug-20 20:24pm

Add a Solution

Comments

Sandeep Mewara 12-Aug-20 2:31am

I am not sure, Think, this should not be in single quotes: 'System.String'

Sandeep Mewara 12-Aug-20 2:47am

BTW, I would use String.format() for readability here at the cost of performance.

GenJerDan 12-Aug-20 3:45am

How would LIKE work on a number? Is 3 like 13? Or is 4 like 3, only a little bigger?
Anyway, check out CAST and CONVERT and turn the number into a string and do the comparison on the newly converted string.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Answer 1 · 2020-08-11T20:24:00

Think, this should not be in single quotes: 'System.String'

Further, can you make sure the strFilter looks like as expected?
Surely following corrections:
1. Is it not missing spaces between all the OR conditions?
2. Missing OR in line:

sb.AppendFormat("(Convert(Size, System.String) LIKE '" + "{0} " + "')", strkeyword);

One more thing, you sure, you want to append '*' to searchwords with LIKE. Or that would be "%" ? (below code does not have this change)

Try below:

C#

string strkeyword = KeywordTextBox.Text.Trim().ToString();

StringBuilder sb = new StringBuilder();
sb.AppendFormat("(Convert(ID, System.String) LIKE '" + "{0}" + "') ", strkeyword);
sb.AppendFormat("OR (Customer_Name LIKE '*" + "{0}" + "*') ", strkeyword);
sb.AppendFormat("OR (Complaint_Number LIKE '*" + "{0}" + "*') ", strkeyword);
sb.AppendFormat("OR (Convert(Size, System.String) LIKE '" + "{0} " + "')", strkeyword);
sb.AppendFormat("OR (Material_Number LIKE '*" + "{0}" + "*') ", strkeyword);
sb.AppendFormat("OR (Nature_Of_Problem LIKE '*" + "{0}" + "*') ", strkeyword);
sb.AppendFormat("OR (Spool_Type LIKE '*" + "{0}" + "*')", strkeyword);
string strFilter = sb.ToString();
material_Return_DataBindingSource.Filter = strFilter;

Reference: BindingSource.Filter Property (System.Windows.Forms) | Microsoft Docs[^]