SQL and Erel TableView

[nrasool]

SQL and TableView

Hi there,

Please could someone help, I'm trying to use Tableview and show the data stored in SQLite db. This is the full coding I have

#Region  Project Attributes 
   #ApplicationLabel: B4A Example
   #VersionCode: 1
   #VersionName: 
   'SupportedOrientations possible values: unspecified, landscape or portrait.
   #SupportedOrientations: unspecified
   #CanInstallToExternalStorage: False
#End Region

#Region  Activity Attributes 
   #FullScreen: False
   #IncludeTitle: True
#End Region

Sub Process_Globals
   'These global variables will be declared once when the application starts.
   'These variables can be accessed from all modules.
   Dim SQL As SQL
End Sub

Sub Globals
   'These global variables will be redeclared each time the activity is created.
   'These variables can only be accessed from this module.
   'Dim WebView1 As WebView
   Dim Table1 As Table
End Sub

Sub Activity_Create(FirstTime As Boolean)
   'Do not forget to load the layout file created with the visual designer. For example:
   'Activity.LoadLayout("Layout1")
   
   If FirstTime Then
      SQL.Initialize(File.DirRootExternal, "test.db", True)
   End If
   Activity.LoadLayout("1")
   Table1.Initialize(Me, "Table1", 4)
   Table1.AddToActivity(Activity, 0, 0dip, 100%x, 50%y)
   'Delete old tables and create new ones.
   DBUtils.DropTable(SQL, "bpmain")
   
   'This is to show how to use the DBVersion Subs. 
   Dim DBVersion, CurrentDBVersion As Int
   'DBVersion = DBUtils.GetDBVersion(SQL)
   Log("Database Version: " & DBUtils.GetDBVersion(SQL))
   
   If SQL.ExecQuerySingleResult("SELECT count(name) FROM sqlite_master WHERE type='table' AND name ='bpmain'") = 0 Then
       'don't exist
      'Creating Tables for 1st time
      Dim m As Map
      m.Initialize
      m.Put("Id", DBUtils.DB_INTEGER)
      m.Put("Date", DBUtils.DB_INTEGER)
      m.Put("col1", DBUtils.DB_INTEGER)
      m.Put("col2", DBUtils.DB_INTEGER)
      m.Put("col3", DBUtils.DB_INTEGER)
      DBUtils.CreateTable(SQL, "bpmain", m, "Id")
      Log("Creating main table")
   Else
       'exist
      Log("main table already exists")
   End If
   
   Dim Dd As DateDialog
   Dim ret1 As String
   Dim Cursor As Cursor
   Dim Date1 As String
   
   Dim col1 As Int
   Dim col2 As Int
   Dim col3 As Int
   

   FillTable

   ShowTable2

End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub
Sub FillTable
   Dim ListOfMaps As List
   ListOfMaps.Initialize
   Dim id As Int
   For i = 1 To 40
      Dim m As Map
      m.Initialize
   '   id = Rnd(id + 1, id + 10000)
      m.Put("Id", i)
      m.Put("Date", DateTime.Date(DateTime.Now))
      m.Put("col1", Rnd(80,120) )
      m.Put("col2", Rnd(80,100) )
      m.Put("col3", Rnd(50,90)  )
      ListOfMaps.Add(m)
   Next
   DBUtils.InsertMaps(SQL, "bpmain", ListOfMaps)
End Sub
Sub ShowTable2
   Dim Cursor1 As Cursor
   Dim txt = "SELECT * FROM bpmain"
   Cursor1=SQL.ExecQuery(txt)      
   Dim Col_Name(5) As String
   For i=0 To Cursor1.ColumnCount-1
        Col_Name(i)=Cursor1.GetColumnName(i)
   Next
   Table1.SetHeader(Array As String(Col_Name(0), Col_Name(1), Col_Name(2),Col_Name(3),Col_Name(4)))
   Cursor1.Position=0
   For i=0 To Cursor1.RowCount-1
      Cursor1.Position=i
      Table1.AddRow(Array As String(Cursor1.GetString("Id"), Cursor1.GetString("Date"), Cursor1.GetString("col1"),Cursor1.GetString("col2"),Cursor1.GetString("col3")))
   Next
End Sub

I know the issue is to do with in Sub ShowTable2 the following:

Table1.AddRow(Array As String(Cursor1.GetString("Id"), Cursor1.GetString("Date"), Cursor1.GetString("col1"),Cursor1.GetString("col2"),Cursor1.GetString("col3")))

As far as I can see it the right fields in getting that data. But when I compile and run the tableview is empty, and on the log I'm getting "Wrong number of values."

I'm using the latest version - 2.70, just wondered if anyone can see why the data is not showing

Many thanks and appreciate your help

Kind Regards

 

[Mahares]

This line:

Table1.Initialize(Me, "Table1", 4)

should be :

Table1.Initialize(Me, "Table1", 5)

since you have 5 columns in your table

 

[nrasool]

Thanks for that, much appreciate, that shows the column to 5, however the data is still not being populated in the tableview. Very strange

Kind Regards

 

[klaus]

Post your project as a zip file (IDE menu Files / Export As Zip) so we could test it.

Best regards.

 

[nrasool]

Hi klaus

Attached is the project zip file

Many thanks if you can help

Kind Regards

 

[klaus]

You must add this line to define the column widths:

ShowTable2
Table1.SetColumnsWidths(Array As Int(50dip, 100dip, 50dip, 50dip, 50dip))

In the attached project you find another Table class module including a LoadSQLiteDB routine.

Best regards.

 

[nrasool]

Ahhh, so the data was there, just couldn't show it because of no width!... Many thanks for your help Klaus