Android Question Multiple LIKE / AND SQlite Syntax Issue

Discussion in 'Android Questions' started by Rob Rendle, Apr 26, 2015.

  1. Rob Rendle

    Rob Rendle Member Licensed User

    Afternoon,

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

    This is the query :-

    Code:
    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'
     
  2. rboeck

    rboeck Well-Known Member Licensed User

    Hi, you have simply forgotten to use this char around your string variables: '
    Code:
    Results.testtxt = "SELECT NAME FROM Rec WHERE Ingredients LIKE '" & Ingt1 & "' AND Ingredients LIKE '" & Ingt2 & "' AND Ingredients LIKE '" & Ingt3 & '"
     
  3. DarkMann

    DarkMann Member Licensed User

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

    Code:
    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

    Code:
    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
     
  4. Mahares

    Mahares Well Known Member Licensed User

    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:
    Code:
    Results.testtext = "SELECT NAME FROM Rec WHERE Ingredients IN(?,?,?)"
    Cursor1=SQL1.ExecQuery2(Results.testtext,
    Array As String(Ingt1,Ingt2,Ingt3))
     
  5. Rob Rendle

    Rob Rendle Member Licensed User

    Hi David,

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

    Code:
    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 :-

    Code:
    SELECT NAME FROM Rec WHERE ING LIKE Chicken AND ING LIKE Ham AND ING LIKE Lettuce
     
  6. Rob Rendle

    Rob Rendle Member Licensed User

    This throws a 'testtext unknown member' error when compiling
     
  7. Mahares

    Mahares Well Known Member Licensed User

    This should work for you I hope:
    Code:
    Cursor1=SQL1.ExecQuery2("SELECT NAME FROM Rec WHERE Ingredients IN(?,?,?)" _
    ,
    Array As String(Ingt1,Ingt2,Ingt3))
     
    Last edited: Apr 26, 2015
  8. Rob Rendle

    Rob Rendle Member Licensed User

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

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

    Mahares Well Known Member Licensed User

    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:
    Code:
    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 and mangojack like this.
  10. Rob Rendle

    Rob Rendle Member Licensed User

    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.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice