Android Question SQLITE returns 0 result

DALB said:

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.

Click to expand...

RB Smissaert said:

>>
" 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

Click to expand...

DALB said:

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.

Click to expand...

synt="SELECT * FROM bnotes WHERE " & spiChoix & _
        " LIKE '%" & filtre.ToUpperCase & "%' OR " & spiChoix & " LIKE '%" & _
        filtre.ToLowerCase & "%' OR " & spiChoix & " LIKE '%" & filtre & "%'"
        
        curs=Starter.sql1.execquery(synt)

DALB said:

if anyone have a look, a suggestion.

Click to expand...

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}%"$))

RB Smissaert said:

>>
" 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

Click to expand...

sfsameer said:

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

Click to expand...

Mahares said:

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.

Click to expand...