I am trying to read data from a sqlite table and place each record into a variable:
My SQL statement is:
B4X:
Cursor1 = SQL0.ExecQuery("SELECT DISTINCT StoreGroup FROM tableSP2")
My Code is:
B4X:
Sub GetStoreGroups
Dim StoreGroup1 As String
Dim StoreGroup2 As String
Dim StoreGroup3 As String
Dim StoreGroup4 As String
Dim StoreGroup5 As String
Dim StoreGroupCount As Int
Dim Cursor1 As Cursor
Cursor1 = SQL0.ExecQuery("SELECT DISTINCT StoreGroup FROM tableSP2")
StoreGroupCount = Cursor1.RowCount
For i = 0 To Cursor1.RowCount - 1
Cursor1.Position = i
StoreGroup1 = (Cursor1.GetString2(0))
StoreGroup2 = (Cursor1.GetString2(1))
StoreGroup3 = (Cursor1.GetString2(2))
StoreGroup4 = (Cursor1.GetString2(3))
StoreGroup5 = (Cursor1.GetString2(4))
Next
Cursor1.Close
End Sub
I get the following error:
B4X:
java.lang.IllegalStateException: Couldn't read row 0, col 1 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it.
Sub GetStoreGroups
Dim StoreGroup1 As String
Dim StoreGroup2 As String
Dim StoreGroup3 As String
Dim StoreGroup4 As String
Dim StoreGroup5 As String
Dim StoreGroupCount As Int
Dim Cursor1 As Cursor
Cursor1 = SQL0.ExecQuery("SELECT DISTINCT StoreGroup FROM tableSP2")
StoreGroupCount = Cursor1.RowCount
For i = 0 To Cursor1.RowCount - 1
Cursor1.Position = i
If Cursor1.Position = 0 Then
StoreGroup1 = (Cursor1.GetString2(0))
End If
If Cursor1.Position = 1 Then
StoreGroup2 = (Cursor1.GetString2(0))
End If
If Cursor1.Position = 2 Then
StoreGroup3 = (Cursor1.GetString2(0))
End If
If Cursor1.Position = 3 Then
StoreGroup4 = (Cursor1.GetString2(0))
End If
If Cursor1.Position = 4 Then
StoreGroup5 = (Cursor1.GetString2(0))
End If
Next
Cursor1.Close
End Sub
Where are you supposed to use the returning values? One way would be from inside the loop. Another to store them in a list:
B4X:
Sub GetStoreGroups
Dim StoreGroupList As List
StoreGroupList.initialize
Dim StoreGroupCount As Int
Dim Cursor1 As Cursor
Cursor1 = SQL0.ExecQuery("SELECT DISTINCT StoreGroup FROM tableSP2")
StoreGroupCount = Cursor1.RowCount
if storegroupcount>0 then
For i = 0 To Cursor1.RowCount - 1
Cursor1.Position = i
storeGroupList.add(cursor1.getstring2(0))
Next
end if
Cursor1.Close
' now storegrouplist contains all distinct storeGroup items. If none, its size is zero.
for j=0 to storeGroupList.size-1
log("storeGroup " & j & "=" & storeGroupList.get(j))
next
End Sub
@mc73, Thanks
Once I have the StoreGroup(x), I call another sub that manipulates the data and displays in a list.
The number of StoreGroup can be 1 to 50.
I have written the sub that handles the data and displays in the list - but it is very long and well, somewhat badly written.
But it works for now - so will clean-up once I have all running