Android Question Add and Update data in SQlite (solved)

[f0raster0]

Hey pals,

My code read a QR code (email) save it in a table and if the QR code read is already in the DB then sum one and update it in the DB, if the QR code read isn't in DB then add it in DB.

I added in DB two emails (manually for start) it works fine when I read a QR code(email) that already is saved.
But when it is a new QR code([email protected]), yes, save it in DB but when I try read the same ([email protected]) just save it like a new QR code.

Sub Condition

    Dim Cursor3 As Cursor
    Dim a As String 'Int
   
    Cursor3 = SQL1.ExecQuery("SELECT email, ID, total FROM QR")
    For i=0 To Cursor3.RowCount-1
    Cursor3.Position = i
        'EditText1.Text = Cursor3.GetString("FirstName")   
                If Label1.Text = Cursor3.GetString("email") Then
                        Msgbox("Hello world1","ok")
                        Label3.Text=Cursor3.GetString("ID")
                        EditText2.Text= Cursor3.GetString("total")
                        Msgbox("Hello world2","ok")
                        EditText2.Text=(EditText2.Text)+1
                        Msgbox("Hello world3","ok")
                        readDB 'CurrentIndex
                        Update1'update in DB the new total
                        'SQL1.Close
                        'Exit
                        'Label1.Text=""
                        Return
                        'Listar
                    Else
                                           
                        add 'if email isn't in Db then Add it
                        'Label1.Text=""
                        Msgbox("Hello world4","ok")
                        'Listar
                        'Cursor3.Close
                        Msgbox("Hello world5","ok")
                        'Label1.Text=""
                        'Exit
                        'SQL1.Close
                        Return
                        'Msgbox("Hello world6","ok")
                       
                End If           
    Next
    Cursor3.Close
End Sub

Sub Update1

    Dim Query1 As String   
    readDB 'CurrentIndex is in label1..read from DB
    Query1 = "UPDATE QR Set total = ? WHERE ID = " & IDList.Get(CurrentIndex)
    SQL1.ExecNonQuery2(Query1, Array As String(EditText2.Text))
    Msgbox ("Entry updated", "Thanks")

End Sub

Sub add
'to add new email read
Dim a As Int'String 'Int
a=1
'Avec index auto increment
SQL1.ExecNonQuery2("INSERT INTO QR VALUES(Null, ?, ?)", Array As Object( a, Label1.Text))

End Sub

 

[tremara1]

not sure if this will help but I think you need

SQL1.EndTransaction

in the update.

 

[mangojack]

Try This .. Again ,unsure if this will solve your problem .. Don 't think it is needed to loop thru all records to find match.

Sub Condition
   
  Dim recID As Int
  Dim cursor1 As Cursor
  Dim query As String = "SELECT id FROM qr WHERE email = ?"
  cursor1 = SQL1.ExecQuery2(query,Array As String(Label1.Text)
     
  If cursor1.RowCount > 0 Then
     cursor1.Position = 0
     recID = cursor1.GetInt("id") '@@ should be .GetInt ??
     Label3.Text=recID
     EditText2.Text= cursor1.GetString("total") +1 '@@ total field maybe Int as well ??
     UpdateRecord(recID)
  Else     
     AddNew
  End If
       
  cursor1.Close
   
End Sub

Sub UpdateRecord(ID )

  Dim Query As String   
  Query = "UPDATE qr SET total = ? WHERE ID = " & ID
  SQL1.ExecNonQuery2(Query, Array As String(EditText2.Text ))
  Msgbox ("Entry updated", "Thanks")

End Sub

Sub AddNew
   SQL1.ExecNonQuery2("INSERT INTO QR VALUES(Null, ?, ?)", Array As Object( 1, Label1.Text))
End Sub

 

[f0raster0]

Thanks mangojack it was that I was looking for but didn't work then I did try the tremara1's option and yes, it works perfect.. thanks..