Android Question SQLITE returns 0 result

DALB

Active Member
Licensed User
Hello,

I have a little problem I can't solve, here.
with these lines...
query in base:
        synt="SELECT * FROM bnotes WHERE " & spiChoix & _
        " LIKE ('%" & filtre.ToUpperCase & "%' OR '%" & _
        filtre.ToLowerCase & "%' OR '%" & filtre & "%')"
        
        curs=Starter.sql1.execquery(synt)

'spiChoix' is the name or a column. It can be col1 or col2 etc...
'filtre' is the word I want to find in this column.

The result is null, but I know that my field are correct, the pragma is right.

A syntax can be like this:
SELECT * FROM tableNotes WHERE memo LIKE 'PAYS' OR 'pays' OR 'Pays'.
Even if I transform filtre.ToUpperCase to f1 and filtre.ToLowerCase to f2, not result.
Like or = give no result too.

If I don't use a filter like 'LIKE' as
SELECT * FROM tableNotes
I have all my lines correctly.

What could be hidden in this case which can be clarified ??
So, I use Sqlite in my apps without any problem, but here, it's a bug ! I lose my reason !

Thanks for answering if anyone have a look, a suggestion.
 

RB Smissaert

Well-Known Member
Licensed User
Hello,

I have a little problem I can't solve, here.
with these lines...
query in base:
        synt="SELECT * FROM bnotes WHERE " & spiChoix & _
        " LIKE ('%" & filtre.ToUpperCase & "%' OR '%" & _
        filtre.ToLowerCase & "%' OR '%" & filtre & "%')"
       
        curs=Starter.sql1.execquery(synt)

'spiChoix' is the name or a column. It can be col1 or col2 etc...
'filtre' is the word I want to find in this column.

The result is null, but I know that my field are correct, the pragma is right.

A syntax can be like this:

Even if I transform filtre.ToUpperCase to f1 and filtre.ToLowerCase to f2, not result.
Like or = give no result too.

If I don't use a filter like 'LIKE' as

I have all my lines correctly.

What could be hidden in this case which can be clarified ??
So, I use Sqlite in my apps without any problem, but here, it's a bug ! I lose my reason !

Thanks for answering if anyone have a look, a suggestion.

>>
" LIKE ('%" & filtre.ToUpperCase & "%' OR '%" & _
filtre.ToLowerCase & "%' OR '%" & filtre & "%')"

This is a SQL syntax error. Should be like this:
where x like a or a like b or x like c

RBS
 
Upvote 0

sfsameer

Well-Known Member
Licensed User
Hello,

I have a little problem I can't solve, here.
with these lines...
query in base:
        synt="SELECT * FROM bnotes WHERE " & spiChoix & _
        " LIKE ('%" & filtre.ToUpperCase & "%' OR '%" & _
        filtre.ToLowerCase & "%' OR '%" & filtre & "%')"
       
        curs=Starter.sql1.execquery(synt)

'spiChoix' is the name or a column. It can be col1 or col2 etc...
'filtre' is the word I want to find in this column.

The result is null, but I know that my field are correct, the pragma is right.

A syntax can be like this:

Even if I transform filtre.ToUpperCase to f1 and filtre.ToLowerCase to f2, not result.
Like or = give no result too.

If I don't use a filter like 'LIKE' as

I have all my lines correctly.

What could be hidden in this case which can be clarified ??
So, I use Sqlite in my apps without any problem, but here, it's a bug ! I lose my reason !

Thanks for answering if anyone have a look, a suggestion.
Hello,

Based on the above code, it should be like this :
B4X:
synt="SELECT * FROM bnotes WHERE " & spiChoix & _
        " LIKE '%" & filtre.ToUpperCase & "%' OR " & spiChoix & " LIKE '%" & _
        filtre.ToLowerCase & "%' OR " & spiChoix & " LIKE '%" & filtre & "%'"
        
        curs=Starter.sql1.execquery(synt)

You need to specify the column name after the "OR"

Thank you,
Saif
 
Upvote 0

Mahares

Expert
Licensed User
if anyone have a look, a suggestion.
To simplify your query, this is what you need:
B4X:
Dim MyQuery As String
    Dim rs As ResultSet
    Dim spiChoix As String = "Upper(Make)" 'spiChoixis a variable representing column name, and Make is the actual column name
    Dim filtre As String = "CaN"   'example
    MyQuery="select * from bnotes where " & spiChoix & "  like ?"  
    rs=SQL1.ExecQuery2(MyQuery, Array As String($"%${filtre.ToUpperCase}%"$))
Note that based on the general forum consensus, you want to use parameterized query.
 
Upvote 0

DALB

Active Member
Licensed User
>>
" LIKE ('%" & filtre.ToUpperCase & "%' OR '%" & _
filtre.ToLowerCase & "%' OR '%" & filtre & "%')"

This is a SQL syntax error. Should be like this:
where x like a or a like b or x like c

RBS

Thanks to this, It's the first time I use this writing with multi LIKE. On the web, I didn't see this syntax. Happy having a good team here.
 
Upvote 0

DALB

Active Member
Licensed User
Hello,

Based on the above code, it should be like this :
B4X:
synt="SELECT * FROM bnotes WHERE " & spiChoix & _
        " LIKE '%" & filtre.ToUpperCase & "%' OR " & spiChoix & " LIKE '%" & _
        filtre.ToLowerCase & "%' OR " & spiChoix & " LIKE '%" & filtre & "%'"
       
        curs=Starter.sql1.execquery(synt)

You need to specify the column name after the "OR"

Thank you,
Saif

Same answer than above, Thanks to this, It's the first time I use this writing with multi LIKE. On the web, I didn't see this syntax. Happy having a good team here. Now, it works fine !
 
Upvote 0

DALB

Active Member
Licensed User
To simplify your query, this is what you need:
B4X:
Dim MyQuery As String
    Dim rs As ResultSet
    Dim spiChoix As String = "Upper(Make)" 'spiChoixis a variable representing column name, and Make is the actual column name
    Dim filtre As String = "CaN"   'example
    MyQuery="select * from bnotes where " & spiChoix & "  like ?" 
    rs=SQL1.ExecQuery2(MyQuery, Array As String($"%${filtre.ToUpperCase}%"$))
Note that based on the general forum consensus, you want to use parameterized query.

Same answer than above, Thanks to this, It's the first time I use this writing with multi LIKE. On the web, I didn't see this syntax. Happy having a good team here. I'll use it in the future.
 
Upvote 0
Top