Android Question UPDATE SQL not working using ExecQuery2

[mohdosala]

Hi there.
I'm having trouble updating my sql database using ExecQuery2.
It works when I'm trying to use SELECT command. But I think it's not working when I'm trying to use UPDATE command.

This is my table:

and here is the interface of my app:

users can add their own titles using that plus button below to set a title, a limit (optional), and the current number. Then they can use the plus button or the minus button to increase or decrease the current number.

My problem shows up when I'm trying to write the codes related to the plus button next to the number.

here's the code:

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Private xui As XUI
    Type ItemValue (lblTitle As Label, lblNumber As Label, lblID As Label)
End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    Private lblUp As Label
    Private clv As CustomListView
    Private mainPanel As Panel
    
    Private lblTitle As Label
    Private lblNumber As Label
    Private lblMinus As Label
    Private lblPlus As Label
    Private lblID As Label
End Sub

Sub Activity_Create(FirstTime As Boolean)
    Activity.LoadLayout("Layout")
    LoadPanels
End Sub

Sub LoadPanels
    Dim rs As ResultSet
    rs = Starter.db.ExecQuery("SELECT * from TITLES order by ID asc")
    PanelCreator(rs)
    
    Dim panelAdd As B4XView = xui.CreatePanel("")
    panelAdd.SetLayoutAnimated(100, 0, 0, 100%x, 103dip)
    panelAdd.LoadLayout("addLayout")
    clv.Add(panelAdd, 0)
End Sub

Sub PanelCreator (cursor As ResultSet)
    clv.Clear
    
    Do While cursor.NextRow
        Dim ID As Int = cursor.GetInt("ID")
        Dim iv As ItemValue
        iv.Initialize
        clv.Add(CreateItem(ID, cursor, iv),iv)
    Loop
    cursor.Close
    
End Sub

Sub CreateItem (ID As Int, cursor As ResultSet, iv As ItemValue) As B4XView
    Dim panel As B4XView = xui.CreatePanel("")
    panel.SetLayoutAnimated(100, 0, 0, 100%x, 93dip)
    panel.Tag = ID
    panel.LoadLayout("itemLayout")
    
    iv.lblTitle = lblTitle
    iv.lblNumber = lblNumber
    iv.lblID = lblID
    iv.lblTitle.Text = cursor.GetString("ZEKR_NAME")
    iv.lblNumber.Text = cursor.GetInt("ZEKR_CURRENT")
    iv.lblID.Text = cursor.GetInt("ID")
    
    lblPlus.Tag = cursor.Getint("ID")
    Return panel
End Sub

Private Sub lblPlus_Click
    Dim index As Int = clv.GetItemFromView(Sender)
    Dim iv As ItemValue = clv.GetValue(index)
    Dim id As Int = iv.lblID.Text
    Dim rs As ResultSet = Starter.db.ExecQuery2("SELECT * FROM TITLES WHERE ID = ?", Array As String(id))
    rs.NextRow
    If rs.GetInt("ZEKR_LIMIT") = 0 Then
        Dim currentNum As Int = rs.GetInt("ZEKR_CURRENT")
        Log("currentNum inSQL = "&currentNum)
        currentNum = currentNum + 1
        Log("Temp CurrentNum = " & currentNum)
        iv.lblNumber.Text = currentNum
        rs.Close
        rs = Starter.db.ExecQuery2("UPDATE TITLES SET ZEKR_CURRENT = ? WHERE ID = ?",Array As String(currentNum-1, id))       
        rs.Close
        
    else if rs.GetInt("ZEKR_CURRENT") < rs.GetInt("ZEKR_LIMIT") Then
        Dim a As Int = rs.GetInt("ZEKR_CURRENT")
        a = a + 1
        iv.lblNumber.Text = a
        rs.Close
        rs = Starter.db.ExecQuery2("UPDATE TITLES SET ZEKR_CURRENT = ? WHERE ID = ?", Array As String(a-1, id))
        rs.Close
    End If
End Sub

in fact this line of code is not working:

rs = Starter.db.ExecQuery2("UPDATE TITLES SET ZEKR_CURRENT = ? WHERE ID = ?",Array As String(currentNum-1, id))

Thank you in advance.

 

[mohdosala]

as the result of the code above,
when I click on lblPlus for item1, the number changes from 5 to 6. but when I click again, nothing happens.
but in face, something happens, it starts reading the current number from database (which is 5) and then increases it and it becomes 6 again.
it shows, that it can't update it in database.

and the same thing goes for item 2, the number changes from 6 to 7. then it gets stuck in 7.

 

[mohdosala]

Oh I found it. That was a silly mistake.
So I put the solution for those who have the same problem I've got.

For this case that I want to UPDATE something in database, I don't need to return anything to a cursor.
So I should use ExecNonQuery2 command instead of ExecQuery2.

The code will be like this:

Private Sub lblPlus_Click
    Dim index As Int = clv.GetItemFromView(Sender)
    Dim iv As ItemValue = clv.GetValue(index)
    Dim id As Int = iv.lblID.Text
    Dim rs As ResultSet = Starter.db.ExecQuery2("SELECT * FROM TITLES WHERE ID = ?", Array As String(id))
    rs.NextRow
    If rs.GetInt("ZEKR_LIMIT") = 0 Then
        Dim currentNum As Int = rs.GetInt("ZEKR_CURRENT")
        Log("currentNum inSQL = "&currentNum)
        currentNum = currentNum + 1
        Log("Temp CurrentNum = " & currentNum)
        iv.lblNumber.Text = currentNum
        rs.Close
        Starter.db.ExecnonQuery2("UPDATE TITLES SET ZEKR_CURRENT = ? WHERE ID = ?",Array As String(currentNum, id))       
        
        
    else if rs.GetInt("ZEKR_CURRENT") < rs.GetInt("ZEKR_LIMIT") Then
        Dim a As Int = rs.GetInt("ZEKR_CURRENT")
        a = a + 1
        iv.lblNumber.Text = a
        rs.Close
        Starter.db.ExecnonQuery2("UPDATE TITLES SET ZEKR_CURRENT = ? WHERE ID = ?", Array As String(a, id))
    End If
End Sub

 

[mohdosala]

and also the code above could be shorter than that. In this way:

Private Sub lblPlus_Click
    Dim index As Int = clv.GetItemFromView(Sender)
    Dim iv As ItemValue = clv.GetValue(index)
    Dim id As Int = iv.lblID.text
    Dim rs As ResultSet = Starter.db.ExecQuery2("SELECT * FROM TITLES WHERE ID = ?", Array As String(id))
    rs.NextRow
    Dim currentNum As Int = rs.GetInt("ZEKR_CURRENT")
    If rs.GetInt("ZEKR_LIMIT") = 0 Then
        currentNum = currentNum + 1
    else if rs.GetInt("ZEKR_CURRENT") < rs.GetInt("ZEKR_LIMIT") Then
        currentNum = currentNum + 1
    End If
    rs.Close
    iv.lblNumber.Text = currentNum
    Starter.db.ExecnonQuery2("UPDATE TITLES SET ZEKR_CURRENT = ? WHERE ID = ?", Array As String(currentNum, id))
End Sub