Android Question How do I code SQLite to xCLV

[darkz]

It's hard to figure out but I want to make a simple xclv that had all the basic SQLite functions such as Add,modify,delete etc.
would be great to get a simple source codes or any example for reference

here is my xlcv code
I'm sorry I'm really confused with xlcv

Sub Activity_Create(FirstTime As Boolean)
    Activity.LoadLayout("layout1")
    
    Dim xui As XUI
    For i = 0 To 10
        Dim p As B4XView = xui.CreatePanel("")
        p.SetLayoutAnimated(100,0,0,100%x,40dip)
        p.LoadLayout("item")
        Label1.Text = " Item numer: " & i
        btn.Text = i
        xclv.Add(p,i)
    Next
End Sub

 

[Brian Dean]

Here is an absolutely basic demonstrator, but hopefully it will get you started. It creates a simple database in File.DirInternal and then retrieves that data and displays it in a CustomListView. On subsequent invocations the database is retrieved directly from File.DirInternal.

The demo shows how to create a table, how to add records to the table, and how to retrieve values from the table and put them into a CLV. Ask if you want to know more.

 

[Brian Dean]

I have extended my example to demonstrate some other features of the CLV.

1. The record identifier from the database is added to the CLV items so that a clicked item can be tied back to its database entry.

2. Selected items can be deleted from the CLV and the database.

3. A button is provided to "repair and replace" the data to refresh the demonstration.

Note that this is NOT a realistic application. It is a simple example to show how data stored in a database and data listed in a CLV can be linked together.

 

[darkz]

Here is an absolutely basic demonstrator, but hopefully it will get you started. It creates a simple database in File.DirInternal and then retrieves that data and displays it in a CustomListView. On subsequent invocations the database is retrieved directly from File.DirInternal.

The demo shows how to create a table, how to add records to the table, and how to retrieve values from the table and put them into a CLV. Ask if you want to know more.

Thank you so much good sir

 

[darkz]

Here is an absolutely basic demonstrator, but hopefully it will get you started. It creates a simple database in File.DirInternal and then retrieves that data and displays it in a CustomListView. On subsequent invocations the database is retrieved directly from File.DirInternal.

The demo shows how to create a table, how to add records to the table, and how to retrieve values from the table and put them into a CLV. Ask if you want to know more.

Hello! I tried tinkering with your code, and while the logging part works fine as it correctly gets the ID and fruit name, I’m getting an error when trying to delete it.

Private Sub clvDemo_ItemLongClick (Index As Int, Value As Object)
itemid = Index + 1
Private sf As Object = xui.Msgbox2Async("Do you really want to delete the selected entry ?", "D E L E T E", "Yes", "", "No", Null)
    Wait For (sf) Msgbox_Result (Result As Int)
If Result = xui.DialogResponse_Positive Then
        sql.ExecNonQuery("DELETE FROM fruit WHERE id = '" & itemid & "'")
showData
End If
End Sub

Private Sub clvDemo_ItemClick (Index As Int, Value As Object)
    itemid = Index + 1
    RS1 = sql.ExecQuery("SELECT * FROM fruit WHERE id = '" & itemid & "'")
    RS1.Position = 0
    Log(RS1.GetString("name"))
'    Log(RS1.GetString("id"))
RS1.Close
End Sub

 

[Brian Dean]

The problem is here :

      sql.ExecNonQuery("DELETE FROM fruit WHERE id = '" & itemid & "'")

id is an integer; you are trying to put quotation marks around it as if it were a string. I could show you how to fix that, but I am not going to. That is because the format that I used may look clumsy but that is for a reason - it is the style that you should also be using. Firstly it avoids any need to handle quotation marks, but also it prevents a well-known weakness in SQL that can be exploited by "bad actors" to disrupt your code - not something that is likely to affect you, I know, but none the less good practice to avoid. Check this post item number 5.

Change the line back to this :

      sql.ExecNonQuery2("DELETE FROM fruit WHERE id = ?", Array As Int(Value))