Android Question SQLITE returns 0 result

[DALB]

Hello,

I have a little problem I can't solve, here.
with these lines...

        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.

 

[edgar_ortiz]

Hi,

You can try

log (synt)

To see what you're really running.

 

[RB Smissaert]

Hello,

I have a little problem I can't solve, here.
with these lines...

        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

 

[RB Smissaert]

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

Sorry should read:
where x like a or x like b or x like c

RBS

 

[sfsameer]

Hello,

I have a little problem I can't solve, here.
with these lines...

        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 :

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

 

[Mahares]

if anyone have a look, a suggestion.

To simplify your query, this is what you need:

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.

 

[DALB]

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

 

[DALB]

Hello,

Based on the above code, it should be like this :

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 !

 

[DALB]

To simplify your query, this is what you need:

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.