Creating a parametrised, MySQL query with wildcards

Question

I want to populate a datagrid using a parameter based query. However, I want to have the ability to enter multiple parameters that may contain wildcards. However, the following test query isn't correct.

Any ideas where I'm going wrong?

SELECT `Customer ID`, `Company Name`, `Address Line 3` FROM XTest.all WHERE `Company Name` LIKE '%'+@Company+'%' ORDER BY `Company Name`;

I want to find everything that has an "A" in the field, so you would normally do LIKE %a% - but this query is using parameters. — AndyDB, Apr 23 '14 at 06:40
It's a safer way of running a query as opposed to just accepting the string. For example if the string contains " - it may throw an error. — AndyDB, Apr 23 '14 at 06:45

score 2 · Answer 1 · edited May 23 '17 at 12:18

string company = "%" + your_criteria_on_company + "%";

string sql = "SELECT 
                `Customer ID`, `Company Name`, `Address Line 3` 
              FROM XTest.all 
              WHERE 
                `Company Name` LIKE @Company 
              ORDER BY `Company Name`";

SqlCommand sqlCommand = new SqlCommand( sql, dbConnection );
sqlCommand.Parameters.AddWithValue( "@Company", company );

' add more of your code here
' and then
sqlCommand.Prepare();
MySqlDataReader resultSet = sqlCommand.ExecuteReader();

Refer to:

Documentation on SqlParameter AddWithValue
Using Prepared Statement in C# with Mysql

score 1 · Accepted Answer · edited May 23 '17 at 10:29

1

You need to enclose column names in "" double quotes (depends on the setting of SQL Mode).

Please check this link and follow the answer of @DRapp. Here is link: mysql parameterized query in ASP.NET

As per the answer you need to use ? instead of @.

Try this:

SELECT `Customer ID`, `Company Name`, `Address Line 3` 
        FROM XTest.all 
        WHERE `Company Name` LIKE  concat('%',? + "Company",'%') 
        ORDER BY `Company Name`;

objCommand.Parameters.AddWithValue("?Company", "CompanyValue");

edited May 23 '17 at 10:29

Community

1
1

answered Apr 23 '14 at 06:32

Hassan

5,360
2
22
35

sorry yes not applicable for mysql. – Hassan Apr 23 '14 at 06:36
1

Thanks Hassan for the effort (much appreciated) – AndyDB Apr 23 '14 at 06:41
Really sorry - but still don't understand. It would be really appreciated if you could apply your solution to the query. thank you (again). – AndyDB Apr 23 '14 at 06:54
1

@HassanNisar: `@` being part of the variable is not an error. – Ravinder Reddy Apr 23 '14 at 07:14
Thank you so much - that's a real help. – AndyDB Apr 23 '14 at 07:15

Creating a parametrised, MySQL query with wildcards

2 Answers2