Android Question SQLite Problem

Terradrones · Oct 16, 2022

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: Copy

[/
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.

klaus · Oct 16, 2022

Put a breakpoint in the Try line and check the value of WorkItem.

Terradrones · Oct 16, 2022

klaus said:
Put a breakpoint in the Try line and check the value of WorkItem.

Hi Klaus

I have done that and the value of the WorkItem is the same as the Stakevalue that I selected in the Combobox.

klaus · Oct 16, 2022

Terradrones said:
Error occurred on line: 1222 (AddGeo)

Do you have an AddGeo routine ?
What is line 1222 ?

Terradrones · Oct 16, 2022

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.

Code: Copy

[/

#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


]

Terradrones · Oct 16, 2022

klaus said:
Do you have an AddGeo routine ?
What is line 1222 ?

Hi Klaus

On the code that I sent you, its on line no. 73.

Terradrones · Oct 16, 2022

sfsameer said:
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: Copy

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"

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: Copy

[/
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
]

Mahares · Oct 16, 2022

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

Not necessarily. If there are records in the query,ResultSet.NextRow will go to the first record.

Enxix · Oct 16, 2022

Hola, Check line 21/22

Mahares · Oct 16, 2022

sfsameer said:
So using "Do While rs.NextRow" will check if there are results

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:

Code: Copy

If rs.rowcount > 0 then
   'code
End if

or you can use:

Code: Copy

If rs.nextrow then
   'code
End if

since the OP is looking for a single record in his latest post.

mcqueccu · Oct 17, 2022

Besides rowcount is not crossplatform

Terradrones · Oct 22, 2022

mcqueccu said:
Besides rowcount is not crossplatform

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.

Mahares · Oct 22, 2022

Terradrones said:
OK, I have solved my problem!

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

Mahares · Oct 22, 2022

Terradrones said:
he 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.

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.

Terradrones · Oct 23, 2022

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.

Android Question SQLite Problem

Terradrones

Active Member

klaus

Expert

Terradrones

Active Member

klaus

Expert

Terradrones

Active Member

Terradrones

Active Member

Terradrones

Active Member

Mahares

Expert

Enxix

Member

Mahares

Expert

mcqueccu

Expert

Terradrones

Active Member

Mahares

Expert

Mahares

Expert

Terradrones

Active Member