Android Question Error while trying to delete row

[Makumbi]

iam trying to delete a single row from the list. i have also attached the extract please help
here is the error

*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
Error occurred on line: 60 (Main)
android.database.sqlite.SQLiteException: no such table: customers (code 1): , while compiling: DELETE FROM  customers WHERE rowid = ?
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
    at android.database.sqlite.SQLiteDatabase.compileStatement(SQLiteDatabase.java:996)
    at anywheresoftware.b4a.sql.SQL.ExecNonQuery2(SQL.java:85)
    at b4a.example.main._deleterow(main.java:587)
    at b4a.example.main$ResumableSub_B4XTable1_CellClicked.resume(main.java:562)
    at anywheresoftware.b4a.shell.DebugResumableSub$DelegatableResumableSub.resumeAsUserSub(DebugResumableSub.java:48)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:732)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:351)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:176)
    at anywheresoftware.b4a.shell.DebugResumableSub$DelegatableResumableSub.resume(DebugResumableSub.java:43)
    at anywheresoftware.b4a.BA.checkAndRunWaitForEvent(BA.java:250)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:137)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:176)
    at anywheresoftware.b4a.keywords.Common$1.onClick(Common.java:492)
    at com.android.internal.app.AlertController$ButtonHandler.handleMessage(AlertController.java:164)
    at android.os.Handler.dispatchMessage(Handler.java:102)
    at android.os.Looper.loop(Looper.java:154)
    at android.app.ActivityThread.main(ActivityThread.java:6077)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:866)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:756)
** Activity (main) Pause, UserClosed = true **

#Region  Project Attributes
    #ApplicationLabel: B4A Example
    #VersionCode: 1
    #VersionName:
    'SupportedOrientations possible values: unspecified, landscape or portrait.
    #SupportedOrientations: unspecified
    #CanInstallToExternalStorage: False
#End Region

#Region  Activity Attributes
    #FullScreen: False
    #IncludeTitle: True
#End Region

Sub Process_Globals
    Private sql As SQL
End Sub

Sub Globals
    Private B4XTable1 As B4XTable
    Private XUI As XUI
End Sub

Sub Activity_Create(FirstTime As Boolean)
    If FirstTime Then
        CopyDBIfNeeded("chinook.db")
        sql.Initialize(File.DirInternal, "chinook.db", False)
    End If
    Activity.LoadLayout("1")
    B4XTable1.AddColumn("Customer Id", B4XTable1.COLUMN_TYPE_NUMBERS)
    B4XTable1.AddColumn("Name", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("Company", B4XTable1.COLUMN_TYPE_TEXT)
    B4XTable1.AddColumn("Address", B4XTable1.COLUMN_TYPE_TEXT)

    Dim Data As List
    Data.Initialize
    Dim rs As ResultSet = sql.ExecQuery("SELECT CustomerId, FirstName, LastName, Company, Address FROM customers")
    Do While rs.NextRow
        Dim row(4) As Object
        row(0) = rs.GetDouble("CustomerId")
        row(1) = rs.GetString("FirstName") & " " & rs.GetString("LastName")
        row(2) = rs.GetString("Company")
        'Some of the fields are Null. We need to convert them to empty strings:
        If row(2) = Null Then row(2) = ""
        row(3) = rs.GetString("Address")
        Data.Add(row)
    Loop
    rs.Close
    B4XTable1.SetData(Data)

End Sub
Sub B4XTable1_CellClicked (ColumnId As String, RowId As Long)
    Dim sf As Object = XUI.Msgbox2Async("Delete row?", "Title", "Yes", "Cancel", "No", Null)
    Wait For (sf) Msgbox_Result (Result As Int)
    If Result = XUI.DialogResponse_Positive Then
        DeleteRow(B4XTable1, RowId)
    End If
End Sub
Sub DeleteRow (tbl As B4XTable, RowId As Long)
    tbl.sql1.ExecNonQuery2("DELETE FROM  customers WHERE rowid = ?", Array (RowId))

    Dim page As Int = tbl.CurrentPage
    Dim FirstIndex As Int = tbl.FirstRowIndex
    tbl.ClearDataView 'Updates the rows count.
    If FirstIndex + 1 >= tbl.mCurrentCount Then
        page = page - 1
    End If
    tbl.CurrentPage = page
End Sub
Sub CopyDBIfNeeded (Filename As String)
    If File.Exists(File.DirInternal, Filename) = False Then
        File.Copy(File.DirAssets, Filename, File.DirInternal, Filename)
    End If
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

 

[Computersmith64]

I can see a couple of issues with your code, however the error says:

android.database.sqlite.SQLiteException: no such table: customers (code 1): , while compiling: DELETE FROM customers WHERE rowid = ?

Which indicates that the table "customers" doesn't exist...

- Colin.

 

[mangojack]

In the Sub .. DeleteRow

tbl.sql1.ExecNonQuery2("DELETE FROM  customers WHERE rowid = ?", Array (RowId))

Should be ..

sql.ExecNonQuery2("DELETE FROM  customers WHERE CustomerId = ?", Array (RowId))

But this will also cause problems ..
After a single delete ... The b4xTable RowID will not Align with the DB (customers) CustomerID.

On Table Click you will have to get the value of CustomerID column and use that to delete record from the DB.

something like ..

Sub B4XTable1_CellClicked (ColumnId As String, RowId As Long)

    Dim m As Map = B4XTable1.GetRow(RowId)
    '..........................  
    DeleteRow(B4XTable1, m.Get("Customer Id"))

Sub DeleteRow (tbl As B4XTable, CustomerId As Int)
    sql.ExecNonQuery2("DELETE FROM  customers WHERE CustomerId = ?", Array (CustomerId))

There are other issues in your code showing updated table .. but one problem at a time.