Making an app with multiple connected databases

[HDSInformatics]

Hi,

If I click on a selected record in the scrollview the app must open a new scrollview with records linked to the first scrollview. (this is based on a second table). Is it possible to do this with basic4android?

Michael

 

[Hubert Brandel]

YES ...

I do have one DATABASE with three tables, so I only need ONE connection (SQL object) and can SELECT from all the tables. If you have 2 Databases, you may need 2 SQL Objects, but normaly one database do have all needed tables.

 

[HDSInformatics]

YES ...

I do have one DATABASE with three tables, so I only need ONE connection (SQL object) and can SELECT from all the tables. If you have 2 Databases, you may need 2 SQL Objects, but normaly one database do have all needed tables.

That is great!
Thanks you for replying!

We are having a LOT of problems and can't get it to work... Is there a way you can help me out with some code? Give us some tips? We can't get the content to appear when we select and click a record on the first scrollview to be shown on the second one, which is located on a new 'page'.

Again thanks for the reply!

Michael

 

[Erel]

You should post your code and explain the issues you encounter.

 

[Hubert Brandel]

HDSInformatics asked for example code ...
OK, this is not the best way B4A could do it, but I am new with B4A and SQL too, so I do it my way :sign0089:

I have a listview on one page and after click one line it will open a second window with editcontrols (one field per table) and a list with child records (the second table). I understand that you have one window with 2 listviews.

... in MAIN

Sub Activity_Create(FirstTime As Boolean)
   SQL1.Initialize(File.DirInternal, "data.db", True)
...
Sub Activity_Resume
   If SQL1.IsInitialized = False Then
      SQL1.Initialize(File.DirInternal, "data.db", True)
   End If

Window with the list ...

Sub Activity_Create(FirstTime As Boolean)
   ' count the total records and those with a valid date (they are done).
   nAnzGesamtLG = Main.sql1.ExecQuerySingleResult("select count(*) from LG")
   nAnzAbgelesenLG = Main.sql1.ExecQuerySingleResult("select count(*) from LG where AbleseDatLG > '' ")
   
   Activity.LoadLayout("...")

   ' LoadListTable1 ' Resume is enough

   if nAnzGesamtLG = 0 Then
      Msgbox("No Data","Info")
   End If 
End Sub

Sub Activity_Resume
   If Main.SQL1.IsInitialized = False Then   
      Main.SQL1.Initialize(File.DirInternal, "data.db", True)
   End If 
   
   LoadListTable1

End Sub

Sub LoadListTable1()
   Dim cur As Cursor
   Dim sLabel As String

   lvTable1.Clear ' clear old lines
   ...
   DoEvents
   cur = Main.sql1.ExecQuery( "SELECT ... FROM Table1 WHERE ... ORDER BY ..." )
      
   For i = 0 To cur.RowCount - 1
      cur.Position = i
      sLabel1 = i & " " & cur.GetString("ID" ) & ... fields ... 
      lvTable1.AddSingleLine( sLabel1, cur.GetString("ID" ) ) ' the ID of this line - 2. Parameter will be returnd after a click-event.
   next
   cur.Close

   DoEvents
End Sub

Sub lvTable1_ItemClick (Position As Int, Value As Object)
   ... my program will now oben the edit page for Table1, 
   ... you have to clear and load your second list ! 
   LoadTable2(Value) ' the Value has now the ID from the active line in Table1
End Sub

your code have to do something like that

Sub LoadListTable2(sID as string) 
   Dim cur As Cursor
   Dim sLabel As String

   lvTable2.Clear ' clear old lines
   ...
   DoEvents
   cur = Main.SQL1.ExecQuery("SELECT ... FROM Table2 WHERE ID = '" & sID & "' ORDER BY ..." )

   For i = 0 To cur.RowCount - 1
      cur.Position = i
      sLabel1 = i & " " & ... fields ... 
      lvTable2.AddSingleLine( sLabel1, ID for that line )
   next
   cur.Close

   DoEvents
End Sub

The ID in my example is a stringvalue, if you have an integer unique key, you have to delete the ' around the ID-variable.