Android Question Multiple LIKE / AND SQlite Syntax Issue

[Rob Rendle]

Afternoon,

Struggling with this one (I think I've run out of walls to bang my had against).

This is the query :-

Results.testtxt = "SELECT NAME FROM Rec WHERE Ingredients LIKE " & Ingt1 & " AND Ingredients LIKE " & Ingt2 & " AND Ingredients LIKE '" & Ingt3 & "

The Ingtn variables are text.

I've followed some rules I've seen on here and fail (i.e 'Text'" should be used for Text Variables) however I always end of with a syntax error when compiling

Using the code above, I get a different error. If I were to set Ingt3 to say Ham, I'd get an error of 'Unknown Column, Ham'

 

[rboeck]

Hi, you have simply forgotten to use this char around your string variables: '

Results.testtxt = "SELECT NAME FROM Rec WHERE Ingredients LIKE '" & Ingt1 & "' AND Ingredients LIKE '" & Ingt2 & "' AND Ingredients LIKE '" & Ingt3 & '"

 

[DarkMann]

If your original code is as shown, the syntax error is because you have the " at the end of the line, enclosing nothing.

As rboeck says, you should surround strings in statements with ' characters for clarity and safety. I always do the typing with empty single quotes in the right places first and then add my variables in later, like this:-

Results.testtext = "SELECT NAME FROM Rec WHERE Ingredients LIKE '' AND Ingredients LIKE ''"

Now add the variables into the gaps between the single quotes and all will be well

Results.testtext = "SELECT NAME FROM Rec WHERE Ingredients LIKE '" & Ingt1 & "' AND Ingredients LIKE '" & Ingt2 &"'"

Note the double-single-double at the end to close the final set of singles around the last variable. Rboeck's version above misses this and will also fail - typo's are far to easy in things like this.

David

 

[Mahares]

To avoid the single and double quote confusion in a SQL statement and when you have multiple LIKE, it is sometimes better to use this approach:

Results.testtext = "SELECT NAME FROM Rec WHERE Ingredients IN(?,?,?)"
Cursor1=SQL1.ExecQuery2(Results.testtext,Array As String(Ingt1,Ingt2,Ingt3))

 

[Rob Rendle]

Hi David,

Thanks for the examples, they make sense, however now my Query ends up looking like

SELECT NAME FROM Rec WHERE ING LIKE 'Chicken' AND ING LIKE 'Ham' AND ING LIKE 'Lettuce''

Which is not what I want, it should read like this :-

SELECT NAME FROM Rec WHERE ING LIKE Chicken AND ING LIKE Ham AND ING LIKE Lettuce

 

[Rob Rendle]

To avoid the single and double quote confusion in a SQL statement and when you have multiple LIKE, it is sometimes better to use this approach:

Results.testtext = "SELECT NAME FROM Rec WHERE Ingredients IN(?,?,?)"
Cursor1=SQL1.ExecQuery2(Results.testtext,Array As String(Ingt1,Ingt2,Ingt3))

This throws a 'testtext unknown member' error when compiling

 

[Mahares]

This should work for you I hope:

Cursor1=SQL1.ExecQuery2("SELECT NAME FROM Rec WHERE Ingredients IN(?,?,?)" _
,Array As String(Ingt1,Ingt2,Ingt3))

 

[Rob Rendle]

This should work for you I hope:

Cursor1=SQL1.ExecQuery2("SELECT NAME FROM Rec WHERE Ingredients IN(?,?,?)" _
,Array As String(Ingt1,Ingt2,Ingt3))

Thanks again Mahares, unfortunately this causes a syntax error :-

Results.testtxt = "SELECT NAME FROM Rec WHERE ING IN(?,?,?)" ,Array As String(Ingt1,Ingt2,Ingt3)

 

[Mahares]

Results.testtxt = "SELECT NAME FROM Rec WHERE ING IN(?,?,?)" ,Array As String(Ingt1,Ingt2,Ingt3)

That is not correct.
The SQL statement I gave you has the correct syntax. The problem lies in your Results.testtxt. You need to post more code so we know exactly what Results.testtxt. is. You need to adapt your code to something like this:

Dim Ingt1 as string ="Ham"
Dim Ingt2 as string ="Chicken"
Dim Ingt3 as string ="Lettuce"
Dim txt as string ="SELECT NAME FROM Rec WHERE Ingredients IN(?,?,?)"
Cursor1=SQL1.ExecQuery2(txt,Array As String(Ingt1,Ingt2,Ingt3))

 

[Rob Rendle]

Thanks Mahares, I understand where I'm wrong now.

Results is a module that all my other modules link to after testtxt has been given a value (to save time on creating a results module for each query). However it uses a query like this to achieve it.

SQL.ExecQuery (testtxt)

And populates a listview from there.

I need to create a new results module for this type of query.

Thanks again.