Android Question Deleting a row from a B4XTable and database.

[PoliceFreez]

I have a B4XTable where I sometimes need to delete a row. When I Longpress the row in the table it asks me if I'm sure of the deletion.
Once I answer "yes" it deletes the row perfectly.
As can be seen below I deleted tow 6.
Before delete row: After Delete
Row 1 Row 1
Row 2 Row 2
Row 3 Row 3
Row 4 Row 4
Row 5 Row 5
Row 6 Row 7
Row 7 Row 8
Row 8 Row 9
Row 9 Row 10
Row 10

Now if I try and delete any Row Greater than row 7 nothing happens.
If I delete any row from row 5 to row 1 it does delete the row though.

The code is"

Sub DeleteRow2 (tbl As B4XTable, RowId As Long, DBRowId As Long)
    SQL1.ExecNonQuery2("DELETE FROM GroceProd WHERE gid = ?", Array (DBRowId))
    tbl.sql1.ExecNonQuery2("DELETE FROM data WHERE rowid = ?", Array (RowId))
    Dim page As Int = tbl.CurrentPage
    Dim FirstIndex As Int = tbl.FirstRowIndex
    tbl.ClearDataView
    If FirstIndex + 1 >= tbl.mCurrentCount Then
        page = page - 1
    End If
    tbl.CurrentPage = page
End Sub

Sub B4XTable2_CellLongClicked (ColumnId As String, RowId As Long)
    Dim sf As Object = XUI.Msgbox2Async("Are you sure you want to delete this row?", "Deleting a row from your data!", "Yes", "", "No", Null)
    Wait For (sf) Msgbox_Result (Result As Int)
    If Result = XUI.DialogResponse_Positive Then
        DeleteRow2(B4XTable2, RowId, RowId)
    End If
    Dim Data1 As List
    Data1.Initialize
    Dim Rs As ResultSet = SQL1.ExecQuery("SELECT gid, gprod, gprice FROM GroceProd")
    Do While Rs.NextRow
        Dim Row(3) As Object
        Row(0) = Rs.GetInt("gid")
        Row(1) = Rs.GetString("gprod")
        Row(2) = Rs.GetString("gprice")
        Data1.Add(Row)
    Loop
    B4XTable2.SetData(Data1)
    Rs.Close
End Sub

Please tell me where the error is to prevent it to delete rows after the previous deleted row?

 

[Mahares]

tell me where the error is to prevent it to delete rows after the previous deleted row

The SQLite rowid is not the same as the B4XTable RowId (random unique number that changes all the time)

Sub DeleteRow2 (tbl As B4XTable, RowId As Long)
    SQL1.ExecNonQuery2("DELETE FROM GroceProd WHERE gid = ?", Array (RowId))
    tbl.sql1.ExecNonQuery2("DELETE FROM data WHERE rowid = ?",    Array (RowId))
    Dim page As Int = tbl.CurrentPage
    Dim FirstIndex As Int = tbl.FirstRowIndex
    tbl.ClearDataView
    If FirstIndex + 1 >= tbl.mCurrentCount Then
        page = page - 1
    End If
    tbl.CurrentPage = page
End Sub

In the long click sub it should be:
DeleteRow2(B4XTable2, RowId)

 

[Erel]

Note that there is a new method named UpdateTableCounters. Better to call it instead of ClearDataView (which was originally intended for a different use case).

 

[PoliceFreez]

Hi guys.
Unfortunately, I tried the above methods and I still get the same issue that it does not delete any item that is below the last deleted item on the B4Xtable.

 

[eps]

Your code looks a bit over-complicated to me... but I'm sure there's a reason why you've done things in a certain way.

I've got something similar - see these two functions, one to delete from the DB and the other to rebuild the list view. Admittedly this is a fairly small list of items, usually 20-30 but possibly up to 100s.

Sub ListView3_ItemLongClick (Position As Int, Value As Object)
    Dim result As Int
    result = Msgbox2("Are you sure?", "Remove selected Favourite", "Yes", "", "No", Null)
    If result = DialogResponse.Positive Then
        SQL1.ExecNonQuery("DELETE FROM favourite where fk_event_id = '" & Value & "'")
    're-populate the listview of FAVOURITEs
    RepopulateFavourites
    End If
End Sub

and

Sub RepopulateFavourites
    're-populate the listview of FAVOURITEs
    Dim Cursor3 As Cursor
    ListView3.TwoLinesLayout.Label.TextSize = 16
    ListView3.TwoLinesLayout.SecondLabel.TextSize=12
    ListView3.TwoLinesLayout.Label.Gravity = Gravity.FILL
    ListView3.ScrollingBackgroundColor = Colors.Transparent
    ListView3.Clear

    Cursor3 = SQL1.ExecQuery("SELECT name, startdate, display_date, event._id FROM event, favourite where event._id = favourite.fk_event_id order by date(startdate)")
    For i = 0 To Cursor3.RowCount - 1
              Cursor3.Position = i
           ListView3.AddTwoLines2(Cursor3.GetString("name"), Cursor3.GetString("display_date"), Cursor3.GetInt("_id"))
       Next
      
    Cursor3.Close
End Sub

Admittedly the code is quite old.

 

[eps]

I'd put some logging in the code and see if you can determine why your code is faulting.

 

[PoliceFreez]

Cool thanks I will try these when I get home.

 

[Mahares]

I've got something similar

I do not see how using the B4XTable library has anything to do with a listview in this thread. They are totally different. The OP is using B4XTable features to manipulate the SQLite and internal databases. If it works for him, then that is great.

Cool thanks I will try these when I get home.

If you still have an issue, export a small project. I am sure someone will figure it out. If not, erel always comes to the rescue.

 

[PoliceFreez]

Ok, I did some logging and the following was observed:

When deleting the first time, say row 8 of 10 it deletes fine.
Now if I delete row 7 of 10 it deletes fine.
However, if I try and delete row 9 of 10 it does nothing.

The log shows now that that row 9 is actually row 7 and therefore it deletes nothing.
That means that if I want to delete row 300 it will actually delete row 298 which will cause an issue.

I hope I stated it very clearly, but I would love any further assistance in this regard.

 

[eps]

I do not see how using the B4XTable library has anything to do with a listview in this thread. They are totally different. The OP is using B4XTable features to manipulate the SQLite and internal databases. If it works for him, then that is great.

If you still have an issue, export a small project. I am sure someone will figure it out. If not, erel always comes to the rescue.

Erm - it's a SQL database with a method of displaying a list...

 

[eps]

Maybe put some counts in the code - and log those... There's something not quite right here (obviously) or ensure you have Open and Close Cursor statements in place, to ensure the database is in the state you expect it to be.

I'm not quite sure why you're performing two deletes.. Personally I would delete from the source and then rebuild the table (or whatever mechanism you choose to display the data in).

Ah I think the issue is that in the database you end up with IDs 1,2,3,4,5,6,9,10. In the table you have 1,2,3,4,5,6,7,8. or similar

So your mixing up the 'wrong' ids... As mentioned I would remove from the source and rebuild, but it could be that you have a sizeable table and rebuilding that each time someone deletes takes too long.. Anyway you can't use the same rowid for both, unless you rebuild and even then it needs to be the DB one (imo) as the other one is a display id.

 

[PoliceFreez]

Hi. I decided to go with a clv. Now it works fine.