Android Question Delete Row from B4X Table And SQL Database

[mangojack]

following the example code here .. https://www.b4x.com/android/forum/threads/b4x-b4xtable-delete-row.103582/

Is the following line supposed to delete record from the Database also , or only from the Table , (list Data) ?

Sub DeleteRow (tbl As B4XTable, RowId As Long)
    tbl.sql1.ExecNonQuery2("DELETE FROM data WHERE rowid = ?", Array (RowId))

following this example ... https://www.b4x.com/android/forum/threads/b4x-b4xtable-load-data-from-sql-database.102520/

(and Including the Sub Delete Code from top links ... it only works / does not error with ..

Sub DeleteRow (tbl As B4XTable, RowId As Long)
    tbl.sql1.ExecNonQuery2("DELETE FROM data WHERE rowid = ?", Array (RowId))             'deletes from table

    'from RowId .. we extract value of 1st Column "Customer Id"
    sql.ExecNonQuery2("DELETE FROM customers WHERE customerId = ?", Array (customerId))  'Deletes from DB

what am I missing ... Thanks

 

[Makumbi]

following the example code here .. https://www.b4x.com/android/forum/threads/b4x-b4xtable-delete-row.103582/

Is the following line supposed to delete record from the Database also , or only from the Table , (list Data) ?

Sub DeleteRow (tbl As B4XTable, RowId As Long)
    tbl.sql1.ExecNonQuery2("DELETE FROM data WHERE rowid = ?", Array (RowId))

following this example ... https://www.b4x.com/android/forum/threads/b4x-b4xtable-load-data-from-sql-database.102520/

it only works / does not error with ..

Sub DeleteRow (tbl As B4XTable, RowId As Long)
    tbl.sql1.ExecNonQuery2("DELETE FROM data WHERE rowid = ?", Array (RowId))             'deletes from table
    sql.ExecNonQuery2("DELETE FROM customers WHERE rowid = ?", Array (RowId))

what am I missing ... Thanks

thanks but why did you choose to use rowid instead of the customerid in your case

 

[mangojack]

thanks but why did you choose to use rowid instead of the customerid in your case

My Mistake ... Have edited the above question .

 

[mangojack]

Just for clarity .. as I have edited my original post , here is the full code to delete a record from both Table & Database.

Private oSQL As SQL  'Process Global

Sub DeleteRow (tbl As B4XTable, RowId As Long)
   
    Dim RowData As Map = tbl.GetRow(RowId)
    Dim customerID As String = RowData.Get("Customer Id")
       
    tbl.sql1.ExecNonQuery2("DELETE FROM data WHERE rowid = ?", Array (RowId))    'deletes record from Table
    oSQL.ExecNonQuery2("DELETE FROM customers WHERE customerID = ?", Array (customerID))  'deletes from DB
   
    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

Question is .. Do I need Both ExecNonQuery2 lines for this to work.

 

[AnandGupta]

Question is .. Do I need Both ExecNonQuery2 lines for this to work.

Hi mangojack

I think, you need both queries here as data is not the db table.
You can delete from db first and then execute 'select' to get current data

Regards,

Anand

 

[mangojack]

I think, you need both queries here as data is not the db table.

Yes understood .. but why a method ..

tbl.sql1.ExecNonQuery2("DELETE FROM data WHERE rowid = ?", Array (RowId))    'deletes record from B4X Table Only

rather than

tbl.RemoveRow(Data, RowId)

or something similar ? confused , thats all.

 

[Makumbi]

Yes understood .. but why a method ..

tbl.sql1.ExecNonQuery2("DELETE FROM data WHERE rowid = ?", Array (RowId))    'deletes record from B4X Table Only

rather than

tbl.RemoveRow(Data, RowId)

or something similar ? confused , thats all.

what of

B4XTable1.Clear

then add to load the new record again thanks

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)

 

[Makumbi]

Yes understood .. but why a method ..

tbl.sql1.ExecNonQuery2("DELETE FROM data WHERE rowid = ?", Array (RowId))    'deletes record from B4X Table Only

rather than

tbl.RemoveRow(Data, RowId)

or something similar ? confused , thats all.

what of

B4XTable1.Clear

then add to load the fresh record after deletion thanks

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)

 

[mangojack]

then add to load the fresh record after deletion thanks

Personally .. I would use load the data once .. and if the user wants to delete a record , remove it from the Table and also the DB.

With your other question in mind it would be something like ...

Sub B4XTable1_CellClicked (ColumnId As String, RowId As Long)
    If ColumnId <> "Customer Id" Then Return
      
    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)
    Dim RowData As Map = tbl.GetRow(RowId)
    Dim customerID As String = RowData.Get("Customer Id")
      
    tbl.sql1.ExecNonQuery2("DELETE FROM data WHERE rowid = ?", Array (RowId))    'deletes record from Table
    oSQL.ExecNonQuery2("DELETE FROM customers WHERE customerID = ?", Array (customerID))  'deletes from DB
  
    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

Still not sure / understanding the tbl.sql1.ExecNonQuery2 call .. (should it be doing more than just removing the table entry) ?

 

[Makumbi]

Personally .. I would use load the data once .. and if the user wants to delete a record , remove it from the Table and also the DB.

With your other question in mind it would be something like ...

Sub B4XTable1_CellClicked (ColumnId As String, RowId As Long)
    If ColumnId <> "Customer Id" Then Return
     
    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)
    Dim RowData As Map = tbl.GetRow(RowId)
    Dim customerID As String = RowData.Get("Customer Id")
     
    tbl.sql1.ExecNonQuery2("DELETE FROM data WHERE rowid = ?", Array (RowId))    'deletes record from Table
    oSQL.ExecNonQuery2("DELETE FROM customers WHERE customerID = ?", Array (customerID))  'deletes from DB
 
    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

Still not sure / understanding the tbl.sql1.ExecNonQuery2 call .. (should it be doing more than just removing the table entry) ?

surely your approach is better than the one i was using thank you very much