Using Parameters in MySQL LIKE query

[Zettai]

Hi All,

I am trying to query a mysql database using the 'LIKE' keyword within my query based on what the user has input.

It works if I pass the string, however I am having trouble getting it to expand the parameter when combining it with the LIKE MySQL keyword.

As seen below parameters work in a simple SELECT without 'LIKE':

string sql2 = "SELECT * FROM vocab WHERE english LIKE '%" + english + "%'";
string sql3 = "SELECT * FROM vocab WHERE english LIKE '%" + "@English" + "%'";
string sql4 = "SELECT * FROM vocab WHERE english =@English";

sql2 and sql4 both work and return data when entered through my form. However sql3 doesn't as it doesn't seem to expand @English into whatever I have entered.

The single quotes around the % symbols are probably the cause but I can't figure it out.

Could someone please tell me the correct syntax for the statement in sql3?

[sotoasty]

I have found that this works for me.

MySQL.CommandText = "SELECT ID FROM Employees WHERE FirstName LIKE ?FirstName";
MySQL.Parameters.AddWithValue("?FirstName","SoToast%");

That would be for your command object.

There is no need for the Single Quote when you are using parameters.

[Zettai]

Thanks SoToasty,

However I am using the following function to retrieve the results based on what I type in the textfield:

dsVocab = vocabMgr.getVocab(txtEnglish.Text);

getVocab(
public DataSet getVocab(string english)
{
....
}

When I use a query similar to what you have stated above it doesn't use the variable 'english' it uses whatever string literal I type between the % symbols.

So if I do this:

cmd.CommandText = "SELECT * FROM vocab WHERE english LIKE ?english";
cmd.Parameters.AddWithValue("?english", "%phone%");

It returns words all with 'phone' in them:
earphones
a telephone bill

Alterntively if I do the following hoping it will expand the variable:
cmd.CommandText = "SELECT * FROM vocab WHERE english LIKE ?english";
cmd.Parameters.AddWithValue("?english", "%english%");

Then in textfield txtEnglish I type 'car' it doesn't return words that have 'car' in them, it returns only the row that matches the word 'english'

Is there a way to use parameters that will expand what I pass in as a variable?

Thanks again for all your help.

[sotoasty]

You have to use the Value in the text field in place of value of the parameter. In your example above you should do...


cmd.CommandText = "SELECT * FROM vocab WHERE english LIKE ?english";
cmd.Parameters.AddWithValue("?english", txtEnglish.Text);

A Parameter in the DB world is like a substitution.

?english in your commandtext, will get substituted with whatever is in the parameter value. In this case, the value will be whatever you type into your txtEnglish field. What happens on the backend is esentially a Replace function (with some further checks and validataion), so that in the end, if you type in "%Car%, into your text field, your command text will come out like this.


"SELECT * FROM vocab WHERE english LIKE '%Car%'";