Android Question Display SQLite selection on a new activity.

Discussion in 'Android Questions' started by Rob Rendle, Feb 8, 2015.

  1. Rob Rendle

    Rob Rendle Member Licensed User

    Morning all,

    I was wondering if anyone could help with this?

    I'm making a recipe app, the recipe's are stored in an SQLite database.

    So far I've managed to get to the stage whereby the user can select a nationality, which is then passed as a query to the db and displays the results in a listview.

    Now when the user selects the recipe they want, I want a new activity to open which remembers the user's recipe selection and queries the database for the other fields and displays them on the screen.

    This is where I'm stuck :(!

    Some more info (or at least my reasoning)

    The database has the following columns (all as text)

    Name, Ingredients, Process, Nationality, Cooking Time (and more but boy relevant here)

    The nationality query is simple, user selects Indian and the name of any entry that has Indian in the nationality column appears in a listview.

    Now say a user selects Madras, I want a new activity to launch (let's call it Recipe) which queries the database for Madras and displays the ingredients, process and cooking time.

    I think I can handle the display part, it's just getting the user's selection to the new activity.

    The reason why I'm doing it this way is so that I don't need to have an activity for every recipes, instead just one activity that displays what ever the user has selected.

    While I'm at it, is there a way to query a db for an exact match but on a different order. So for example if in the ingredients section I could have Chicken, Lemon, Lettuce. If the user did a search on Lemon, Chicken, Lettuce. Would the result still appear despite being in a different order?

    Thank you :)
  2. eurojam

    eurojam Well-Known Member Licensed User

    you can use the Process_Globals from your new activity and pass the selected recipe_id from your Main Activity to the new activity and open it. There you can query your database with the passed id. something like this
    Sub open_recipe
      new_activity.recipe_id = 
    End Sub
    hope this helps
    Devan likes this.
  3. Rob Rendle

    Rob Rendle Member Licensed User

    Thanks Stefan, I'll give that a go
  4. klaus

    klaus Expert Licensed User

    Did you have a look at the SQLiteLight examples ?
    Link in my signature.
    Devan and eurojam like this.
  5. Rob Rendle

    Rob Rendle Member Licensed User

    Yes I have and found it very useful (thanks), without it I wouldn't have got my idea to this stage
  6. klaus

    klaus Expert Licensed User

    In the example you have what you are asking for.
    The Edit module shows the selection from the first Activity.
    Devan and Rob Rendle like this.
  7. Rob Rendle

    Rob Rendle Member Licensed User

    Thanks Klaus,

    It's taken a fair amount of 'playing' tonight to get that to an understandable level. Should be able to get my head around it soon :)

    I'm thinking it'll be easier to give each recipe an ID as well as name in the db
    Last edited: Feb 9, 2015
  8. klaus

    klaus Expert Licensed User

    Sure !
  9. udg

    udg Expert Licensed User

    Hi Rob,
    once you have a working solution, maybe you could take some time to re-think your DB design in order to avoid duplicate info.
    I mean you could shape it in a way similar to the following:

    Table: RECIPES
    ID, Name, Nationality, CookingTime
    Table: RECIPE
    ID, Name,..

    This way, you would use first table to select a recipe by name or nationality (or both as in the case of same recipe made differently in two countries).
    Then, when your user selects a recipe from the list in first activity, you pass its ID to your second activity where a select on the second table will get you the list of ingredients (in the form of IDs) and how to use them. Finally (in that same select) you recover ingredients names by way of their IDs.

    Just my 2cents

    Devan likes this.
  10. Rob Rendle

    Rob Rendle Member Licensed User

    I can't thank you all enough for the suggestions :).

    Thanks for the design pointers udg - it's worth considering when I'm ready to start the db proper.

    Ok, so I've finally managed to crack this, using this method :-

    Sub rlv_ItemClick (Position As Int, Value As Object)

    selectedRecipeID = Value

    End Sub

    However, now I've stumbled across another issue.

    I'm using Horizontal Scroll Views (just as a test, I doubt I'll be using this proper), and have used an example to make it easier (to explain the HSV stuff you'll see in a mo)

    So now, in my new activity (Called Recipe), I have this :-

    Sub Activity_Create(FirstTime As Boolean)

    SQL1.Initialize(DBFileDir, DBFileName, 

    Dim txt As String

    txt = 
    "SELECT Ingredients FROM Rec WHERE Name =" & IndiaMenu.selectedRecipeID

        cur = SQL1.ExecQuery(txt)
        SetHsvValue(HSV1, cur)

    And I get an error on the ExecQuery, the error is this :-

    LastException android.database.sqlite.SQliteException: no such column; Madras (code 1):, while compiling: SELECT Ingredients FROM Rec WHERE Name =Madras

    Now I know that the itemClick must be working, as it was Madras is what I 'clicked' on, however, why is it searching for a Column called Madras and not a Row?

    In my current SQLite Database I have the following 'Columns'

    Name, Ingredients, Process, Nationality

    So when searching for Ingredients where the name is Madras, it should show the info I've entered in my db? Which in this case is Chicken

    Thanks again all. So close!

  11. keirS

    keirS Well-Known Member Licensed User

    Because you need to tell SQLite that Madras is a string.

    txt = "SELECT Ingredients FROM Rec WHERE Name = " & "'" & IndiaMenu.selectedRecipeID  &  "'"
  12. Rob Rendle

    Rob Rendle Member Licensed User

    Thanks again keirS,

    Whilst the app installs and compiles, I'm getting a SQLiteCursor message in my HSV and this is the error in the logs :-

    android.database.sqlite.SQLiteException: unrecognized token: "'" (code 1): , while compiling: SELECT Ingredients FROM Rec WHERE Name =Madras'
  13. Mahares

    Mahares Well Known Member Licensed User

    Try this:
    txt = "SELECT Ingredients FROM Rec WHERE Name = '" & IndiaMenu.selectedRecipeID & "'"
    Devan likes this.
  14. Rob Rendle

    Rob Rendle Member Licensed User

    Thanks Mahares

    No error now, however, in the HSV I get :-

    (SQLiteCursor) android.database.sqlite.SQLiteCursor@234289a7

    Can HSV's not display strings?
  15. Rob Rendle

    Rob Rendle Member Licensed User

    I think I need to run a cur.getstring ("Ingredients") line

    I'll let anyone who sees this thread know how it goes
  16. Rob Rendle

    Rob Rendle Member Licensed User

    It was as I thought.

    I didn't quite understand the HSV, however now with a Scrolling View with a Panel, this works a treat. This was the code in the end :-

    Sub RunSIV

    Dim i As Int
    Dim txt As String
    Dim selectedRecipeID="" As String
    Dim cur As Cursor
    Dim ilbl As Label
        SIV.Color = 
        ilbl.TextColor = 
        ilbl.Color = 
        txt = 
    "SELECT Ingredients FROM Rec WHERE Name = '" & IndiaMenu.selectedRecipeID & "'"
        cur = SQL1.ExecQuery(txt)
    For i = 0 To cur.RowCount -1
        cur.Position = i

    End Sub

    Thanks for all of you're help :)
  17. Mahares

    Mahares Well Known Member Licensed User

    Rob: You do not need both lines. The second line by itself is sufficient: ilbl.Text=cur.GetString("Ingredients")
    Also, if your cursor returns many 'Ingredients', you will be overwriting the label text every time. You do not want to do that unless the cursor returns only one.
  18. Rob Rendle

    Rob Rendle Member Licensed User

    Fair enough, thanks for the tip :)

    In reference to the ingredients - it'll only ever return one 'set' of ingredients, as the variable 'selectedRecipeID' is the name of the recipe the user selected and all names are unique.
  19. Mahares

    Mahares Well Known Member Licensed User

    OK, here is another tip only one line of code instead of 7 lines for next time if you only return one item:
    ilbl.Text=SQL1.ExecQuerySingleResult("SELECT Ingredients FROM Rec WHERE " _
    "Name = '" & IndiaMenu.selectedRecipeID & "'")
  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