Android Question Connect spinner content to external database (ideally Excel file)

[Fab117]

Hi,


I am new in B4A community.

Until now, I created some Excel file containing macros in VBA, but now I would like to develop applications for Android.


I am creating my first app and I need help.


This application is designed to manage the contents of my freezer.

I have some layouts that are already done.

Main screen:



Screen for adding a new element in the freezer:




In this screen, I first select the category of product to freeze (p. Ex .: meat, fish, vegetables ...).



And then I select a sub category (eg .: beef, pork, veal, lamb, ...) based on 1st selection



To do this, I used code in the IDE.

In Sub Global:

' Variable pour la liste des catégories

Dim spnCategories As Spinner

Dim listeCategorie(5) As String

Dim lblCategorieChoisie As Label



' Variable pour la liste des sous catégories

Dim spnSousCategories As Spinner

Dim listeSousCategorie(5) As String

Dim lblSousCategorieChoisie As Label



In Sub BtnAjout_Click

' Charge la page permettant les ajouts d'articles

Activity.LoadLayout("PageAjoutArticle")



' Initialisation de la liste des catégories

listeCategorie(0)="Viande"

listeCategorie(1)="Poisson"

listeCategorie(2)="Légume"

listeCategorie(3)="Boulangerie"

listeCategorie(4)="Autre"

spnCategories.Initialize("spin")

spnCategories.Prompt="Selectionner la catégorie"

spnCategories.AddAll(listeCategorie)

Activity.AddView(spnCategories,120dip,120dip,40dip,40dip)



In Sub spin_ItemClick (Position As Int, Value As Object)

lblCategorieChoisie.Text = Value

' Initialisation de la liste des sous catégories

If Value="Viande" Then

listeSousCategorie(0)="viande0"

listeSousCategorie(1)="viande1"

listeSousCategorie(2)="viande2"

listeSousCategorie(3)="viande3"

listeSousCategorie(4)="Autre"

spnSousCategories.Initialize("spinSousCategorie")

spnSousCategories.Prompt="Selectionner la sous catégorie"

spnSousCategories.AddAll(listeSousCategorie)

Activity.AddView(spnSousCategories,120dip,160dip,40dip,40dip)

End If



But my purpose would be to use a data base in an Excel file such as :

[/url
]


I would really appreciate if someone could take time to let me know how I could define in my spinner that the datas to look for are in an Excel file.


Have a great day.


Fab

 

[Erel]

Start with this tutorial: https://www.b4x.com/android/forum/threads/read-write-excel-files-on-android.25632/#content

 

[klaus]

I would suggest you to switch from the Excel files to a SQLite database.
At a first look SQL may seem complicated, but it isn't and once you know it you wouldn't go back to Excel.
You can easily filter what ever you want and the filling of the Spinners would be much easier.

Attention à l'orthographe:
Accès à la liste des articles congelés.

 

[Fab117]

Dear Erel and Klaus,
Thank you for your help.
@klaus, could you please recommend a basic tutorial on SQLite database (et bien vu pour l'orthographe, merci)

Fab

 

[klaus]

Have a look at the three simpee examples in my signature, these examples are also shiped with the User's Guide.
Look at chapter 4 SQLite Database in the B4A User's Guide.
This link http://www.sqlite.org/lang.html is also very useful.

 

[Fab117]

Klaus,
I spend quite a long time on the 3 examples you gave me and on advanced user guide.
I understand not so badly the "process", however I am still not able to use it in my appli.

My first problem is to create the data base.
1) In the 1st example, I haven't found "persons.db" in the folder and subfolders. Could you please let me know where it is stored ?

2) In the 3rd example ("persons.db" is in subfolder "Files"), I replaced in the IDE all the database name and table name from "persons" to "toto"


but when I run it, I have an error message:


I thought that the code in the subroutine "Activity_Create" was their to create the database. Isn't it ?



It would be nice if you could spend a little bit more time to explain me those 2 points.

Have a nice day.

Fab

 

[klaus]

In SQLiteLight1 the database is created in the code.
If you look at the Activity_Create routine you have:
- SQL1.Initialize(File.DirInternal, "persons.db", True) which creates the database file persons.db in Files.DirInternal.
- a call to CreateDataBase which creates an empty table persons.

In SQLiteLight2 and SQLiteLight3 an existing sample database file is provided in the Files folder (Files.DirAssets).
B4A cannot read nor write to db files in the Files.DirAssets folder, that's the reason why the db file is copied from File.DirAssets to File.DirInternal with File.Copy(File.DirAssets, "persons.db", File.DirInternal, "persons.db").

In your example, do you have a toto.db file in File.DirAssets with a toto table inside it ?
If no, you need to create one with an external program or create it in the code like in SQLiteLight1.

Si tu veux continuer en français, tu peux poser les questions dans le forum français.