Android Question Issues with Reading Database Records - Help Please

[neomewilson]

First of all, let me say that I am a newbie to Basic 4 Android but before posting this I have read all the examples I could find on the forum, I purchased the new and excellent book on this language and environment but I am still stumped.

The basics of what I am trying to do is read records from a database using the "ExecuteMap" command and then display them in a label based on the position of a "SeekBar"

I only have one record in the table currently for testing so I am not trying to loop through a set of values yet but to read the single record, display it in the label for testing.

I have included all my code but the area I am having the issue is in the read routine. I think I am just not understand how to retrieve the values and then assign them to the label. Please look at the "ReadPrjListTable" Sub and let me know where I am approaching this incorrectly.

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

#Region  Activity Attributes
    #FullScreen: False
    #IncludeTitle: False
#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 DBMyWorld As SQL
    Dim prjListMap As Map
   
    prjListMap.Initialize

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 ImageView1 As ImageView
    Dim pnlHdrmain As Panel
    Dim btnAnalytics As Button
    Dim btnProject As Button
    Dim btnReports As Button
    Dim btnTasks As Button
    Dim panelNotifications As Panel
    Dim panelPrjViews As Panel
    Dim panelProjects As Panel
    Dim btnAddNew As Button
    Dim btnDelete As Button
    Dim btnEdit As Button
    Dim btnSettings As Button
    Dim panelActions As Panel
    Dim lblTitle As Label
    Dim labelProject As Label
    Dim lblProjectsLst As Label   
    Dim prjListSeekBar As SeekBar
    Dim prjListArray() As String

   
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("myworldmain")
   
    'Set SeekBar Value to 0
    prjListSeekBar.Value = 0
       
    'Test if this is the first time for the application to run and if so, create and initialize the database.
    If FirstTime Then
        If File.Exists(File.DirDefaultExternal, "MyWorld.db") = False Then
            CreatePrjListTable
            ReadPrjListTable
        Else
            ReadPrjListTable
        End If       
    End If   
   
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub
Sub CreatePrjListTable

    'If Table Doesn't Exist (FirstTime = True) Then Create Table, If (FirstTime = False) Do Nothing.
    DBMyWorld.Initialize(File.DirDefaultExternal, "MyWorld.db", True)
    prjListMap.Put("prjID", DBUtils.DB_INTEGER)
    prjListMap.Put("prjName", DBUtils.DB_TEXT)
    DBUtils.CreateTable(DBMyWorld,"ProjectList",prjListMap,"prjID")
    prjListMap.Put("prjName","ITOE Redesign for 2013")

End Sub
Sub ReadPrjListTable

    Dim prjCnt As Int
    Dim prjTotalRecords As Int
   
    DBMyWorld.Initialize(File.DirDefaultExternal, "MyWorld.db", True)
   
    prjTotalRecords = DBMyWorld.ExecQuerySingleResult("SELECT COUNT(*) FROM ProjectList")
    prjListMap = DBUtils.ExecuteMap("SELECT prjID, prjName FROM ProjectList", Null)
    If prjTotalRecords = Null Then
      labelProject.Text = "No Projects Defined!"
    Else
      labelProject.Text = prjListMap.Get("prjName")
    End If
       
End Sub
Sub prjListSeekBar_ValueChanged (Value As Int, UserChanged As Boolean)

    'Set Label for Project Name to Current Record
    Dim recPosition As Int
   
    If prjListSeekBar.Value > 0 Then
        recPosition = prjListSeekBar.Value
        labelProject.Text = prjListMap.Get("prjName")
    End If
   
End Sub

 

[derez]

Im not sure if this is the only problem but this line

prjListMap = DBUtils.ExecuteMap("SELECT prjID, prjName FROM ProjectList", Null)

is missing "DBMyWorld" as the first parameter:

ExecuteMap(SQL As SQL, Query As String, StringArgs() As String) As Map

 

[Erel]

I don't see any code that inserts data to the table.

 

[neomewilson]

I had a separate project that I wrote that included the insert. It worked and inserted a single record. I fixed the issue mentioned above by David Erez and now the code is working without errors but it's only returning a null into the label. I can see that the query returns a single record but I obviously still have something wrong with the way I am trying to assign the value from the prjListMap variable to the labelProject. Is someone can take a look at the following code and let me know what I might be doing wrong I would appreciate it.

    If prjTotalRecords = 0 Then
      labelProject.Text = "No Projects Defined!"
    Else
      labelProject.Text = prjListMap.Get("prjName")
    End If

I am reinserting the complete area of the read sub so you see the changes that were made.

Sub ReadPrjListTable

    Dim prjCnt As Int
    Dim prjTotalRecords As Int
   
    DBMyWorld.Initialize(File.DirDefaultExternal, "MyWorld.db", True)
   
    prjTotalRecords = DBMyWorld.ExecQuerySingleResult("SELECT COUNT(*) FROM ProjectList")
    prjListMap = DBUtils.ExecuteMap(DBMyWorld,"SELECT prjID, prjName FROM ProjectList", Null)
    If prjTotalRecords = 0 Then
      labelProject.Text = "No Projects Defined!"
    Else
      labelProject.Text = prjListMap.Get("prjName")
    End If
       
End Sub

 

[neomewilson]

Hurray! I got it working. This was just a test for me to get an understanding of how to use the database functionality. My next steps will be to create a form for adding, editing, and deleting project names, built the loop to get the values as needed and setup the seeker to have a max equal to the number of projects defined.

Here is the code once I had it changed and working:

Sub ReadPrjListTable

    Dim prjCnt As Int
    Dim prjTotalRecords As Int
   
    DBMyWorld.Initialize(File.DirDefaultExternal, "MyWorld.db", True)
   
    prjTotalRecords = DBMyWorld.ExecQuerySingleResult("SELECT COUNT(*) FROM ProjectList")
    prjListMap = DBUtils.ExecuteMap(DBMyWorld,"SELECT prjID, prjName FROM ProjectList", Null)
    If prjTotalRecords = 0 Then
      labelProject.Text = "No Projects Defined!"
    Else
      labelProject.Text = ""
    End If
       
End Sub
Sub prjListSeekBar_ValueChanged (Value As Int, UserChanged As Boolean)

    'Set Label for Project Name to Current Record
    Dim recPosition As Int
   
    If prjListSeekBar.Value > 0 Then
        recPosition = prjListSeekBar.Value
        labelProject.Text = prjListMap.GetValueAt(recPosition)
    End If
   
End Sub

Many thanks again for setting me on the correct course.