ASP.NET: MY SQL "IN" condition does not work

when using the “IN” condition in a mysql query does not work.

a simple query i have tried

SELECT * FROM mifostenant-default.m_code_value where code_id in (30 ,31)

i tried to pass it as a variable and also i have tried to hard code it to see if it was a wappler bug, the query works,
however only returns the data for code_id 30, and it disregards 31

can someone confirm this please.

Kindest regards,

johnny hajjar
@patrick

Try square brackets, IN needs an array, not a list

2 Likes

it is not bug . it have to be an Array .

i have tied square brackets, and it does not work, can you confirm it does on your end?? if so, can u send me a screen shot, i Have also tried putting it in quotes etc.

No error is thrown, it just returns no values.

Mysql ASP.NET
@George
@s.alpaslan
@Hyperbytes

I have used this a few times so I know it is working, I will take a look through some of my old code to see what i did for you, but could you try not using any brackets at all, as I think Wappler auto adds them in for you, so just enter 31,32 without anything else. Let me know if it works please, if not i will start a hunt for what i did quickly.

Hi,

I initially tried without any brackets, however also in this case no results returned and no error is thrown.

i really hope its something i am doing wrong so i can finish up.
@psweb i really hope you can find your solution :slight_smile: or confirm this is not working paul

Cheers.

I found one of my old queries where i know it worked however it is different to your use case mine was
country IN mexico,canada using no brackets or quotation marks, but yours are numbers, the only thing i can imagine is maybe your numbers need the .toNumber() formatter as they might be getting parsed as strings rather than numbers.
I will continue looking but in the meantime can you try adding the .toNumber() formatter to your numbers possibly.

I think you need to use:
{{'31,30'.split(",")}}

1 Like

Well done Tom, that worked for my test

guys Im still baffled here,

is my screenshot correct?? because now that i done that i get a 500 internal server error

please try guys
this example without “split”
“30”, “31”

tried above, but getting internal server error

that has already been tried

This might be a very silly thing to ask with regard to this, but i just tried my country query as explained above again and had to do that in exactly the same way as @TomD explained, to pass a complete string and then split it on a character. So that is how the IN Operator is working within the Database Query Builder.

Why then can i use this with the same IN operator as the query builder but within the Users and Permissions. NOTE: value admin,groupadmin

@johnny.hajjar I tested @TomD solution and it worked for me.
{{‘31,30’.split(",")}}
Single Quotes around the numbers separated by a comma, in the split though those are double quotes then a comma then more double quotes.
Just making sure in your split you did not do 2x single quotes which made it look like a double quote.

not working for me, are you using mysql, ASP.net?

I’m using MySQL/PHP. Perhaps that’s the problem (though it shouldn’t be).

have you tried in the qury builder or only the users and permissions, the issues is in the query builder

@paul, the IN you mention for user permissions has nothing to do with the IN in the query builder.
@TomD you don’t need to use split, just enter an array there, just like @s.alpaslan showed.

@johnny.hajjar if you see an error 500 there when using an array, we call @patrick here to check this for .net

Square brackeds should work, seems an ASP.NET error @patrick should check it out

Thanks George, yes indeed it seems to be bug, all the above just didn’t seem to make much logic, and certainly not the Wappler way of keeping this simple. i look forward to a resolution, yuk im stuck again :frowning:
@patrick
@George
@Teodor