Android Question android.database.sqlite.SQLiteException: unrecognized token: "'0" (code 1): , while compiling: DELET

[fifiddu70]

hello, i have this problem when delete:
android.database.sqlite.SQLiteException: unrecognized token: "'0" (code 1): , while compiling: DELETE FROM listview where ID = '0
this my code.

Sub add_Click
   
       

            Dim NewID As Int
            Try
                NewID = SQL1.ExecQuerySingleResult("SELECT max(id) FROM listview ") + 1
            Catch
                NewID = 1
            End Try
       
            Dim qry As String
            Dim MyFields() As Object
            MyFields=Array As Object(txttavolo.Text,txtoperatore.Text,txteuro.Text,txttotaleeuro.Text ,txtnote.Text,NewID) 
            qry = "INSERT INTO listview VALUES (?,?,?,?,?,?)"   'Account for all fields
            SQL1.ExecNonQuery2(qry,MyFields)
            lswordine.Clear
            lswordine2.Clear
            txttavolo.Text=""
            txteuro.Text=""
            txttotaleeuro.Text=""
            txtnote.Text=""
           
   
    

    read_Click
End Sub

Sub read_Click
   
    If File.Exists(File.DirInternal,"listview.db") = True Then
       
        mp1.Load(File.DirAssets,"click.wav")
        mp1.Play
       
        pnlbase.Visible=False
        pnlsetup.Visible=False
        pnlaiuto.Visible=False
        pnltecnico.Visible=False
        pnlresoconto.Visible=False
        pnldata.Visible=False
        pnloperatore.Visible=False
        pnlsetup2.Visible=False
        pnlblocco.Visible=False
        pnllist.Visible=True
        pnltavoli.Visible=False
        lvdb.Clear
       
        cursor1 = SQL1.ExecQuery("SELECT * FROM listview")
        Log(cursor1.RowCount)
        For i = 0 To cursor1.RowCount - 1
            cursor1.Position = i
            lvdb.AddSingleLine(cursor1.GetString("id")& " : " & "Tavolo " & cursor1.GetString("tavolo"))
           
        Next
        cursor1.Close
    Else
   
        Msgbox("Nessun database presente, Prego riavvia il programma","AVVISO !!")
   
    End If
   
End Sub

Sub lvdb_ItemClick (Position As Int, Value As Object)
   
    mp1.Load(File.DirAssets,"click.wav")
    mp1.Play
    Dim idvalue As String
    idvalue = Value
    countIt = idvalue.IndexOf(":") 'find location of sperator
    idvalue = idvalue.SubString2(0,countIt) 'find first part of label text
    id = idvalue
    cursor1 = SQL1.ExecQuery("SELECT * FROM listview where id = " & id )
    For i = 0 To cursor1.RowCount - 1
        cursor1.Position = i
        txttavolo.text=cursor1.getString("tavolo")
        txtoperatore.text=cursor1.getstring("operatore")
        txteuro.text=cursor1.getString("totaleeuro")
        txtnote.text=cursor1.getstring("note")
       
       
   
       
    Next
    pnllist.Visible=False
    pnlbase.Visible=True
    pnlsetup.Visible=False
    pnlaiuto.Visible=False
    pnltecnico.Visible=False
    pnlresoconto.Visible=False
    pnldata.Visible=False
    pnloperatore.Visible=False
    pnlsetup2.Visible=False
    pnlblocco.Visible=False
    pnltavoli.Visible=False
   
    switch = 1
    txtswitch.Text= switch
   
   
End Sub
Sub lvdb_ItemLongClick (Position As Int, Value As Object)
   
    mp1.Load(File.DirAssets,"click.wav")
    mp1.Play
   
    Dim Selection As Short
    Selection = Msgbox2("Sei sicuro di voler cancellare l'elemento selezionato?".ToUpperCase, "A T T E N Z I O N E !!", "Si", "", "No", Null)
    Select Selection
        Case DialogResponse.POSITIVE
           
            If Msgbox2(Value, "Confermi l'eliminazione di", "Sì", "Annulla", "No", Null) = DialogResponse.POSITIVE Then
                SQL1.ExecNonQuery("DELETE FROM listview where ID = '"  & id )
                lvdb.RemoveAt(Position)
               
                Msgbox("Tavolo rimosso dalla lista","ESEGUITO !!!")
                pnlbase.Visible=True
                pnlsetup.Visible=False
                pnlaiuto.Visible=False
                pnltecnico.Visible=False
                pnlresoconto.Visible=False
                pnldata.Visible=False
                pnloperatore.Visible=False
                pnlsetup2.Visible=False
                pnlblocco.Visible=False
                pnllist.Visible=False
                pnltavoli.Visible=False
                txttavolo.Text=""
                txteuro.Text=""
                txteuro.Text=0
            End If

           
        Case DialogResponse.CANCEL, DialogResponse.NEGATIVE
           
    End Select
    Return True
   
   
    cancel_Click
   
End Sub

 

[fixit30]

SQL1.ExecNonQuery("DELETE FROM listview where ID = '" & id )

ID is normally numeric therefore the ' is not required. It should be:

SQL1.ExecNonQuery("DELETE FROM listview where ID = " & id )

 

[fifiddu70]

thanks fixit30 now does not give me more mistake but I do not delete, when I open the database in the listview I see again the same deleted record.

 

[imbault]

You didn't set your id variable in your lvdb_ItemLongClick like you did in your lvdb_ItemClick

    Dim idvalue As String
    idvalue = Value
    countIt = idvalue.IndexOf(":") 'find location of sperator
    idvalue = idvalue.SubString2(0,countIt) 'find first part of label text
    id = idvalue

 

[fifiddu70]

thank imbault.