Mysql WHERE LIKE with RadCombobox.Text

Question

I am having trouble with SELECT WHERE LIKE due to the use of @.

Using

string Mysql = "SELECT * FROM sytransactioncategory WHERE TransactionCategory LIKE '%' + @TransactionCategory + '%'";

Do not Work.

I found several interesting answers, like this but I did not not manage to adapt it to my case. Here is my current C# code:

string Mysql = "SELECT * FROM sytransactioncategory WHERE TransactionCategory LIKE @TransactionCategory";
MySqlDataAdapter adapter = new MySqlDataAdapter(Mysql,ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
adapter.SelectCommand.Parameters.AddWithValue("@TransactionCategory", e.Text);

Anyone could give me a track please?

You're query construction in the first case is a little flawed, should be `....LIKE '%" + ....`. In the second case, you've not provided any `%` symbols, unless they are already in `e.Text`. — Uueerdo, May 13 '15 at 22:13
@OllieJones AFAIK, not after a certain amount of time passes. — Uueerdo, May 13 '15 at 23:03
Uueerdo, the second case is what is working so far. It is my working basis. I should have mentioned that. — NALL, May 14 '15 at 22:47
And for the first case, @ is missing. you should read WHERE TransactionCategory LIKE '%' + @TransactionCategory + '%' — NALL, May 14 '15 at 22:57

score 1 · Answer 1 · answered May 13 '15 at 22:55

The + operator in MySQL works on numbers.

You want ... LIKE CONCAT('%', TransactionCategory, '%') if you're trying to construct strings in your query.

The kicker is this: 0 + 'somethingrandom' has the result value of zero, because MySQL tries to convert 'somethingrandom' to an integer, and decides it's zero.

0 + '2randomthings'

has a value of 2.

So, your query is saying WHERE column LIKE 0 which

doesn't fail by throwing any kind of syntax error.
doesn't find anything useful.

Debugging this kind of stuff in MySQL is a pain in the synt*x

score 0 · Answer 2 · answered May 14 '15 at 23:11

Thank you Ollie. I tried your solution in phpmyadmin and it looks like these both options work:

LIKE CONCAT('%', 'TransactionCategory', '%') (meaning your answer adding the ' before and after TransactionCategory)
LIKE '%TransactionCategory%'

So my problem is now: how can I replace TransactionCategory by @TransactionCategory, which is the value of what is entered in the RadComboBox "rcbTransactionCategory"

score 0 · Answer 3 · answered May 14 '15 at 23:36

0

You should try to make the second method work, it's better than the first.

To solve your problem, in c#, your statement should be like this:

string Mysql = "SELECT * FROM sytransactioncategory WHERE TransactionCategory LIKE '%" + str_TransactionCategory + "%'";

Where str_TransactionCategory is the variable holding your search value.

answered May 14 '15 at 23:36

Tim3880

2,563
1
11
14

Hello Tim. I am a bit confused as first you advise me to use the second method ('%TransactionCategory%') and then you give me a third one ('%" + str_TransactionCategory + "%') that I cannot run successfully in phpmyadmin. (it doesn't return any value). I tried the following to use your str_TransactionCategory: RadComboBox rcbAccountCode = (RadComboBox)item.FindControl("rcbAccountCode"); and then how can I state str_TransactionCategory = rcbAccountCode.Text? – NALL May 15 '15 at 00:44
You need gather the value from your form somehow,maybe just like your code. I don't have your whole setup and it's up to you to find out, maybe by reading a complete tutorial somewhere. The second method makes things complicated and you can master it later. – Tim3880 May 15 '15 at 01:39

score 0 · Answer 4 · answered May 16 '15 at 17:30

0

In fact, the answer was quite simple. No need to use findcontrol. This works for me:

string Mysql = "SELECT * FROM sytransactioncategory WHERE TransactionCategory LIKE CONCAT('%', @TransactionCategory, '%');

Thank you for your help.

answered May 16 '15 at 17:30

NALL

3
5

Mysql WHERE LIKE with RadCombobox.Text

4 Answers4