Android Question SQLite Problem

Terradrones

Active Member
Licensed User
I need help again please.

I have different Tables in a SQLite Database where I store my different Geometric Parameters of a Road Design. These will consist of Horizontal Alignment, Vertical Alignment, Crossfalls, Stakeline Offsets, etc.

When I open the Activity to display the Vertical Alignment, it will show all the Stakevalues of the Vertical Curves in a Combobox. This it all displays correctly, but when I click on one of the Stakevalues in the Combobox to show me the Stakevalue, VPI Elevation and the Parabolic curve lengths it gives me an error.

Here is the error message:

Error occurred on line: 1222 (AddGeo)
android.database.CursorIndexOutOfBoundsException: Index -1 requested, with a size of 0
at android.database.AbstractCursor.checkPosition(AbstractCursor.java:514)
at android.database.AbstractWindowedCursor.checkPosition(AbstractWindowedCursor.java:138)
at android.database.AbstractWindowedCursor.getString(AbstractWindowedCursor.java:52)
at anywheresoftware.b4a.sql.SQL$CursorWrapper.GetString(SQL.java:377)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:732)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:348)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:146)
at anywheresoftware.b4a.BA$1.run(BA.java:352)
at android.os.Handler.handleCallback(Handler.java:938)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:223)
at android.app.ActivityThread.main(ActivityThread.java:7731)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:612)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:997)
(Exception) java.lang.Exception: android.database.CursorIndexOutOfBoundsException: Index -1 requested, with a size of 0

This is the code that I am using:

B4X: 
[/
Sub Load_Ver
    VerNo.clear
    ResultSet = CGlobals.SQL1.ExecQuery("SELECT Stake FROM VerPi")
    Do While ResultSet.NextRow
        Try
            'VerNo.Add(NumberFormat2(ResultSet.GetString("Stake"),1,3,3,False))
            VerNo.Add(ResultSet.GetString("Stake"))
        Catch
            Log(LastException)
        End Try
    Loop
    ResultSet.Close            'close
End Sub

Sub VerNo_ItemClick (Position As Int, Value As Object)
    WorkItem = VerNo.SelectedItem
    'WorkItem=NumberFormat2(WorkItem,1,3,3,False)
    Try
        Query = "SELECT * FROM VerPi WHERE Stake = ?"
        ResultSet =CGlobals.SQL1.ExecQuery2(Query, Array As String (WorkItem))
        ResultSet.NextRow
        VerStake.Text = NumberFormat2(ResultSet.GetString("Stake"),1,3,3,False)
        VPILevel.Text = NumberFormat2(ResultSet.GetString("VPiLevel"),1,3,3,False)
        Length1.Text = NumberFormat2(ResultSet.GetString("Length1"),1,3,3,False)
        Length2.Text = NumberFormat2(ResultSet.GetString("Length2"),1,3,3,False)
        CurveType.SelectedIndex=ResultSet.GetString("CType")
    Catch
        Log(LastException)
        CurveType.SelectedIndex=0
    End Try
    ResultSet.Close
End Sub
]

I can list the Vertical Alignment in a Table without any problems.
 
Sort by date Sort by votes

Terradrones

Active Member
Licensed User
Hi Klaus

"AddGeo", is the Activity where I handle all the Geometric Design Parameters.

I have marked the line in red, that gives me the error. The error pops when I click on the Combobox to select a Stakevalue.

AddGeo: 
[/

#Region ******************************************************************* Vertical Alignment ******************************************************

#Region *********************************************************************** Data Input ************************************************************

Sub VerStake_FocusChanged(HasFocus As Boolean)
    If HasFocus =False Then
        If IsNumber(VerStake.Text) = False Then VerStake.Text=0
        VerStake.Text=NumberFormat2(VerStake.Text,1,3,3,False)
    Else
        VerStake.SelectAll
    End If
End Sub

Sub VPILevel_FocusChanged(HasFocus As Boolean)
    If HasFocus =False Then
        If IsNumber(VPILevel.Text) = False Then VPILevel.Text=0
        VPILevel.Text=NumberFormat2(VPILevel.Text,1,3,3,False)
    Else
        VPILevel.SelectAll
    End If
End Sub

Sub Length1_FocusChanged(HasFocus As Boolean)
    If HasFocus =False Then
        If IsNumber(Length1.Text) = False Then Length1.Text=0
        Length1.Text=NumberFormat2(Length1.Text,1,3,3,False)
    Else
        Length1.SelectAll
    End If
End Sub

Sub Length2_FocusChanged(HasFocus As Boolean)
    If HasFocus =False Then
        If IsNumber(Length2.Text) = False Then Length2.Text=0
        Length2.Text=NumberFormat2(Length2.Text,1,3,3,False)
    Else
        Length2.SelectAll
    End If
End Sub

#End Region

#Region ********************************************************* Database *********************************************************

Sub Open_Ver
    CGlobals.CreateOtherTables
    Load_Ver
End Sub

Sub Load_Ver
    VerNo.clear
    ResultSet = CGlobals.SQL1.ExecQuery("SELECT Stake FROM VerPi")
    Do While ResultSet.NextRow
        Try
            'VerNo.Add(NumberFormat2(ResultSet.GetString("Stake"),1,3,3,False))
            VerNo.Add(ResultSet.GetString("Stake"))
        Catch
            Log(LastException)
        End Try
    Loop
    ResultSet.Close            'close
End Sub

Sub VerNo_ItemClick (Position As Int, Value As Object)
    WorkItem = VerNo.SelectedItem
    'WorkItem=NumberFormat2(WorkItem,1,3,3,False)
    Try
        Query = "SELECT * FROM VerPi WHERE Stake = ?"
        ResultSet =CGlobals.SQL1.ExecQuery2(Query, Array As String (WorkItem))
        ResultSet.NextRow
[B][COLOR=rgb(226, 80, 65)]        VerStake.Text = NumberFormat2(ResultSet.GetString("Stake"),1,3,3,False)[/COLOR][/B]
        VPILevel.Text = NumberFormat2(ResultSet.GetString("VPiLevel"),1,3,3,False)
        Length1.Text = NumberFormat2(ResultSet.GetString("Length1"),1,3,3,False)
        Length2.Text = NumberFormat2(ResultSet.GetString("Length2"),1,3,3,False)
        CurveType.SelectedIndex=ResultSet.GetString("CType")
    Catch
        Log(LastException)
        CurveType.SelectedIndex=0
    End Try
    ResultSet.Close
End Sub
        
Sub Clean_VerText
    VerStake.RequestFocus
    VerStake.Text = ""
    VPILevel.Text = ""
    Length1.Text = ""
    Length2.Text = ""
End Sub

#End Region

#Region ********************************************************* Buttons Pressed *******************************************

Sub SaveVer_Click
    KB.HideKeyboard
    Save_Ver
End Sub

Sub InsertVer_Click
    'Save_Ver
End Sub

Sub EraseVer_Click
    Erase_Ver
End Sub

Sub ExitVer_Click
    HideGeoPanels
End Sub

#End Region

#Region *********************************************************** Save\InsertVer\Erase Data **************************************************

Sub Save_Ver
    If IsNumber(VerStake.Text)=True Then
        If IsNumber(VPILevel.Text)=False Then VPILevel.Text=NumberFormat2(0,1,3,3,False)
        If IsNumber(Length1.Text)=False Then Length1.Text=NumberFormat2(0,1,3,3,False)
        If IsNumber(Length2.Text)=False Then Length2.Text=NumberFormat2(0,1,3,3,False)
        Query = "SELECT * FROM VerPi WHERE Stake = ?"
        ResultSet =CGlobals.SQL1.ExecQuery2(Query, Array As String (VerStake.text))
        If ResultSet.NextRow = True Then
            'if it exists show a message and do nothing else
            Msgbox2Async("Override?", "Vertical Pi Exists", "Yes", "No", "", Null,False)
            Wait For Msgbox_Result (Answ As Int)
            If Answ = DialogResponse.POSITIVE Then            'if yes, update the entry
                Update_Ver
                Clean_VerText
            Else
                VerStake.RequestFocus
                Return
            End If
        Else
            'if not, add the Vertical Pi
            Query = "INSERT INTO VerPi VALUES (?,?,?,?,?)"
            CGlobals.SQL1.ExecNonQuery2(Query, Array As String(VerStake.Text, VPILevel.text, Length1.text, Length2.text, CurveType.SelectedIndex))
            Load_Ver
            ToastMessageShow("Pi Added", False)
            Clean_VerText
        End If
    Else
        Msgbox2Async("Not Enough Data Entered", "Missing Data", "OK", "", "", Null,False)
        Return
    End If
    ResultSet.Close
End Sub

Sub Erase_Ver
    If VerNo.SelectedItem>=0 Then
        Msgbox2Async("Are You Sure To Erase The VPi AT SV " & NumberFormat2(VerNo.SelectedItem,1,3,3,False) , "Erase VPi", "Yes", "Cancel", "", Null,False)
        Wait For Msgbox_Result (Answ As Int)
        If Answ = DialogResponse.POSITIVE Then
            Query = "DELETE FROM VerPi WHERE Stake = ?"
            CGlobals.SQL1.ExecNonQuery2(Query, Array As String(VerStake.Text))
            ToastMessageShow("Pi Erased", False)
            Load_Ver
            Clean_VerText
        End If
    Else
        Msgbox2Async("Select Vertical VPi To Erase", "No VPi Selected", "OK", "", "", Null,False)
    End If
End Sub

Sub Update_Ver
    Query = "UPDATE VerPi Set Stake = ?, VPiLevel = ?, Length1 = ?, Length2 = ?, CType = ? WHERE Stake = ?"
    CGlobals.SQL1.ExecNonQuery2(Query, Array As String(VerStake.Text, VPILevel.Text, Length1.Text, Length2.Text, CurveType.Selectedindex, VerStake.Text))
    ToastMessageShow("Pi Updated", False)        'display a confirmation message
    Clean_VerText
End Sub

#End Region

#End Region


]
 
Upvote 0

sfsameer

Well-Known Member
Licensed User
Longtime User
Hello,

You are forcing the "ResultSet.NextRow" without checking if it has a next row or not.

You should add : "Do While ResultSet.NextRow" because it will check if there is a row or not.

B4X: 
  Do While ResultSet.NextRow
        ........ 
    Loop
    ResultSet.Close

Second thing is you should define another ResultSet because you are opening and closing the same ResultSet within a loop of that ResultSet, which is causing :
"android.database.CursorIndexOutOfBoundsException: Index -1 requested, with a size of 0"
 
Upvote 0

Terradrones

Active Member
Licensed User
Hi

I am using the method that you mention.

I am using the following code to add all my vertical Curves points to a Combobox and it works.

B4X: 
[/
Sub Load_Ver
    VerNo.clear
    ResultSet = CGlobals.SQL1.ExecQuery("SELECT Stake FROM VerPi")
    Do While ResultSet.NextRow
        Try
            VerNo.Add(ResultSet.GetString("Stake"))
        Catch
            Log(LastException)
        End Try
    Loop
    ResultSet.Close            'close
End Sub
]

The problem is with the following code, when I select one of the Stakevalues in the Combobox and it is supposed to display the values of the selected curve.

The error happens at "VerStake.Text"

[CODE=b4x][/
Sub VerNo_ItemClick (Position As Int, Value As Object)
    WorkItem = VerNo.SelectedItem
    Try
        Query = "SELECT * FROM VerPi WHERE Stake = ?"
        ResultSet =CGlobals.SQL1.ExecQuery2(Query, Array As String (WorkItem))
        ResultSet.NextRow
        VerStake.Text = NumberFormat2(ResultSet.GetString("Stake"),1,3,3,False)
        VPILevel.Text = NumberFormat2(ResultSet.GetString("VPiLevel"),1,3,3,False)
        Length1.Text = NumberFormat2(ResultSet.GetString("Length1"),1,3,3,False)
        Length2.Text = NumberFormat2(ResultSet.GetString("Length2"),1,3,3,False)
        CurveType.SelectedIndex=ResultSet.GetString("CType")
    Catch
        Log(LastException)
        CurveType.SelectedIndex=0
    End Try
    ResultSet.Close
End Sub
]
 
Upvote 0

sfsameer

Well-Known Member
Licensed User
Longtime User
Mahares said:
Not necessarily. If there are records in the query,ResultSet.NextRow will go to the first record.
Click to expand...
Very necessarily, without it will cause this error if there were no results :
B4X: 
Dim rs As ResultSet
        rs = sql.ExecQuery("select id,row from test")
        rs.NextRow
        Log(rs.GetString("row"))
        rs.Close


B4X: 
Error occurred on line: 99 (Main)
java.sql.SQLException: ResultSet closed
    at org.sqlite.RS.checkOpen(RS.java:63)
    at org.sqlite.RS.findColumn(RS.java:108)
    at org.sqlite.RS.getString(RS.java:317)
    at anywheresoftware.b4j.objects.SQL$ResultSetWrapper.GetString(SQL.java:552)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:629)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:234)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:109)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:98)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:96)
    at b4j.example.main.main(main.java:29)


So using "Do While rs.NextRow" will check if there are results returned or not and won't cause the error above.
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
sfsameer said:
So using "Do While rs.NextRow" will check if there are results
Click to expand...
Of course you get an error if you have rs.NextRow by itself and there are no records.. You do not need to use Do While rs.NextRow Loop. You can use:
B4X: 
If rs.rowcount > 0 then
   'code
End if
or you can use:
B4X: 
If rs.nextrow then
   'code
End if
since the OP is looking for a single record in his latest post.
 
Upvote 0

sfsameer

Well-Known Member
Licensed User
Longtime User
Mahares said:
since the OP is looking for a single record in his latest post.
Click to expand...
Hello,

You can simply get a single record by using (limit 1) in SQLite and MySQL or (top 1) in MS SQL

"do while rs.nextrow" is much simpler rather than adding "If rs.rowcount > 0 then rs.nextrow " or "If rs.nextrow then"
 
Upvote 0

Terradrones

Active Member
Licensed User
mcqueccu said:
Besides rowcount is not crossplatform
Click to expand...
Hi All

OK, I have solved my problem!

The Data (Vertical Curve Info) gets stored as a Double number value when I either enter the Data manually or import them from a CSV or Text file. In my code to display the values, I try to read the SQLite Database as Strings. I have changed the Type to "Double" and it is working.
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Terradrones said:
OK, I have solved my problem!
Click to expand...
We are glad you solved the problem which could have been resolved much sooner if a small sample project was included in the first place. When you post too much code, it overwhelms the members that want to help you, especially when you have dozens of variables and a database that no one has access to its schema or its data. That's why a small project ( which is not always easy to prpare) can go a long way in expediting your answer. I noticed that you had 2 similar threads regarding a surveying topic a week earlier and they were never answered by anyone. In fact they still show 0 response. The main reason is the threads have a ton of code that makes it very hard to come up with a response
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
When you store a column data in a SQLite table as DOUBLE or TEXT and the data is all numbers, you can read the data using rs.GetString("colname") or rs.GetDouble("colname") , even when you use Numberformat2. So, I am not sure I agree with your explanation for the reason of your problem, unless I am not understanding your answer. Could you please clarify.
 
Upvote 0

Terradrones

Active Member
Licensed User
Hi

I changed "VerStake.Text = NumberFormat2(ResultSet.GETSTRING("Stake"),1,3,3,False)" to "VerStake.Text = NumberFormat2(ResultSet.GetDouble("Stake"),1,3,3,False)" and it worked.
 
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Solved
Android Question Increase search ability SQLite (Solved)
2 3 4
Replies
78
Views
8K
Shelby
F
  • Question
Android Question sqlite problem / with count ? Group by
Replies
6
Views
1K
FrankDev
F
  • Article
B4A Library TDDButils Database Management for SQlite and SQLiteCipher
Replies
2
Views
3K
incendio
W
  • Question
Android Question sqlite "no such function" runtime error
2
Replies
27
Views
7K
Wong Ka Chun
W
  • Question
Android Question Problem with REAL at sqlite
Replies
3
Views
1K
iNKUBO
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…