Android Question Delete Row from B4X Table And SQL Database

mangojack

mangojack

Expert
Licensed User
Longtime User
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) ?
B4X: 
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 ..
B4X: 
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
 
Last edited:
Sort by date Sort by votes
M

Makumbi

Well-Known Member
Licensed User
mangojack said:
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) ?
B4X: 
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 ..
B4X: 
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
Click to expand...
thanks but why did you choose to use rowid instead of the customerid in your case
 
Upvote 0
mangojack

mangojack

Expert
Licensed User
Longtime User
Makumbi said:
thanks but why did you choose to use rowid instead of the customerid in your case
Click to expand...

My Mistake ... Have edited the above question .
 
Upvote 0
mangojack

mangojack

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

B4X: 
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.
 
Upvote 0
AnandGupta

AnandGupta

Expert
Licensed User
Longtime User
mangojack said:
Question is .. Do I need Both ExecNonQuery2 lines for this to work.
Click to expand...

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
 
Upvote 0
mangojack

mangojack

Expert
Licensed User
Longtime User
AnandGupta said:
I think, you need both queries here as data is not the db table.
Click to expand...

Yes understood .. but why a method ..
B4X: 
tbl.sql1.ExecNonQuery2("DELETE FROM data WHERE rowid = ?", Array (RowId))    'deletes record from B4X Table Only

rather than
B4X: 
tbl.RemoveRow(Data, RowId)
or something similar ? confused , thats all.
 
Upvote 0
M

Makumbi

Well-Known Member
Licensed User
mangojack said:
Yes understood .. but why a method ..
B4X: 
tbl.sql1.ExecNonQuery2("DELETE FROM data WHERE rowid = ?", Array (RowId))    'deletes record from B4X Table Only

rather than
B4X: 
tbl.RemoveRow(Data, RowId)
or something similar ? confused , thats all.
Click to expand...
what of
B4X: 
B4XTable1.Clear

then add to load the new record again thanks
B4X: 
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)
 
Upvote 0
M

Makumbi

Well-Known Member
Licensed User
mangojack said:
Yes understood .. but why a method ..
B4X: 
tbl.sql1.ExecNonQuery2("DELETE FROM data WHERE rowid = ?", Array (RowId))    'deletes record from B4X Table Only

rather than
B4X: 
tbl.RemoveRow(Data, RowId)
or something similar ? confused , thats all.
Click to expand...
what of
B4X: 
B4XTable1.Clear

then add to load the fresh record after deletion thanks
B4X: 
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)
 
Upvote 0
mangojack

mangojack

Expert
Licensed User
Longtime User
Makumbi said:
then add to load the fresh record after deletion thanks
Click to expand...

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 ...

B4X: 
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) ?
 

Attachments

  • B4XTable Delete Example2.zip
    337 KB · Views: 342
Last edited:
Upvote 0
M

Makumbi

Well-Known Member
Licensed User
mangojack said:
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 ...

B4X: 
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) ?
Click to expand...
surely your approach is better than the one i was using thank you very much
 
Upvote 0
You must log in or register to reply here.
Top