Android Question Display SQLite selection on a new activity.

[Rob Rendle]

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

 

[eurojam]

Rob,
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 = id
  StartActivity(new_activity)
End Sub

hope this helps
stefan

 

[Rob Rendle]

Thanks Stefan, I'll give that a go

 

[klaus]

Did you have a look at the SQLiteLight examples ?
Link in my signature.

 

[Rob Rendle]

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

 

[klaus]

In the example you have what you are asking for.
The Edit module shows the selection from the first Activity.

 

[Rob Rendle]

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

 

[klaus]

I'm thinking it'll be easier to give each recipe an ID as well as name in the db

Sure !

 

[udg]

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_RECIPES, ID_INGREDIENT, Quantity, HowToUseIt
Table: INGREDIENTS
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

udg

 

[Rob Rendle]

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
StartActivity(Recipe)


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, True)

Dim txt As String

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

    Activity.LoadLayout("Recipe")
    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!

Rob

 

[keirS]

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

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

 

[Rob Rendle]

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'

 

[Mahares]

Try this:

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

 

[Rob Rendle]

Thanks Mahares

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

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

Can HSV's not display strings?

 

[Rob Rendle]

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

I'll let anyone who sees this thread know how it goes

 

[Rob Rendle]

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.Panel.LoadLayout("inlbl")
    SIV.Color = Colors.White
   
    ilbl.TextColor = Colors.Black
    ilbl.Color = Colors.White
   
    txt = "SELECT Ingredients FROM Rec WHERE Name = '" & IndiaMenu.selectedRecipeID & "'"
   
    cur = SQL1.ExecQuery(txt)
   
    For i = 0 To cur.RowCount -1
    cur.Position = i
    cur.GetString("Ingredients")
   
    ilbl.Text=(cur.GetString("Ingredients"))
   
    Next
   

End Sub

Thanks for all of you're help

 

[Mahares]

cur.GetString("Ingredients")

ilbl.Text=(cur.GetString("Ingredients"))

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.

 

[Rob Rendle]

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.

 

[Mahares]

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 & "'")