Android Question How to restrict selected to one colum b4xtable

Makumbi

Well-Known Member
Licensed User
Please help i would like to delete using the customerid only how can i restrict this using b4xtable
B4X:
#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)
        Dim kkt As String = RowId
        Log(kkt)
        Dim column As B4XTableColumn = B4XTable1.GetColumn(ColumnId)
        Dim value As String = B4XTable1.GetRow(RowId).Get(ColumnId)
        Dim kt As String
        Dim ktn As String
    kt = value
    ktn= column.Id
        Log(kt)
        Log(ktn)
        'Dim m As Map = B4XTable1.GetRow(RowId)

        DeleteRow(B4XTable1, kt)
    End If
End Sub

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

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

mangojack

Well-Known Member
Licensed User
Longtime User
Your thread title is a bit confusing versus your In thread follow up question.
But based on previous unanswered / ignored threads... I am thinking you just want to delete a table row using the Customer ID column.

First result from a search for ... b4xTable Delete Row. https://www.b4x.com/android/forum/threads/b4x-b4xtable-delete-row.103582/#content

If this is the case .. there is no need to rebuild the table in the Delete sub.. (maybe a follow up post to let us know .. hmm)

tip. I noticed in an example project you uploaded recently (and in the code above), the SQL object is initialized in Main activity in a If FirstTime ..
Ideally this should be done in the Starter service.
It is possible your are already doing this in your Main Project ...


PS : It would also be nice if you occasionally responded to help given by other members ... maybe even a 'Like'.
To Many times I have been left wondering if I have solved your current problem or totally misunderstood the question.
 
Upvote 0

Makumbi

Well-Known Member
Licensed User
Your thread title is a bit confusing versus your In thread follow up question.
But based on previous unanswered / ignored threads... I am thinking you just want to delete a table row using the Customer ID column.

First result from a search for ... b4xTable Delete Row. https://www.b4x.com/android/forum/threads/b4x-b4xtable-delete-row.103582/#content

If this is the case .. there is no need to rebuild the table in the Delete sub.. (maybe a follow up post to let us know .. hmm)

tip. I noticed in an example project you uploaded recently (and in the code above), the SQL object is initialized in Main activity in a If FirstTime ..
Ideally this should be done in the Starter service.
It is possible your are already doing this in your Main Project ...


PS : It would also be nice if you occasionally responded to help given by other members ... maybe even a 'Like'.
To Many times I have been left wondering if I have solved your current problem or totally misunderstood the question.
iam sorry for that . but my problem was that according to the code provided above when the user clicks on customerId colum he can be in position to delete a record successfully but my problem i wanted to restrict the user to only delete using the customerid because that is were the deletion is based on that id not any other columns that the user might click on .
 
Upvote 0

Makumbi

Well-Known Member
Licensed User
B4X:
Sub B4XTable1_CellClicked (ColumnId As String, RowId As Long)
 If ColumnId <> "SomeColumn" Then Return

this is what i use for deletion please help on how to refer to that column

B4X:
Sub DeleteRow (tbl As B4XTable, CustomerId As Int)
    Dim kk As String = CustomerId
    Log(kk)
    sql.ExecNonQuery2("DELETE FROM  customers WHERE CustomerId = ?", Array (CustomerId))

    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
    B4XTable1.Clear
    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
 
Upvote 0

mangojack

Well-Known Member
Licensed User
Longtime User
I had not seen example of B4xTable from SQL .. https://www.b4x.com/android/forum/threads/b4x-b4xtable-load-data-from-sql-database.102520/

I now understand your posted code better . If you are basing your project on that example ,there is no need to refer to that column / columnID
(as I had previously suggested.)

Again ... https://www.b4x.com/android/forum/threads/b4x-b4xtable-delete-row.103582/#content and with @Erel code in the previous post ..

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
 

Attachments

  • b4xTablefromSQL Example.zip
    337 KB · Views: 168
Upvote 0

mangojack

Well-Known Member
Licensed User
Longtime User
this is what i use for deletion please help on how to refer to that column
I have been playing with the example ... https://www.b4x.com/android/forum/threads/b4x-b4xtable-load-data-from-sql-database.102520/

Adding in the example sub to Delete a record does not error ... but does not delete the record from the db.
(Only from the list Data ? )
Changing the table name to customers (as it is in DB ) results in same error you posted in previous thread 'no such table .. Customers'

Investigating it further.
 
Upvote 0

AnandGupta

Expert
Licensed User
Longtime User
Upvote 0

mangojack

Well-Known Member
Licensed User
Longtime User
i wanted to restrict the user to only delete using the customerid because that is were the deletion is based on that id not any other columns that the user might click on .

Personally , I would be using a cell_Long Click for any deletion procedure. (still checking for correct column if you need).

That way you don't have delete confirmation messages appearing frequently.. and also you still have a clean cell_Click event to handle other procedures.
 
Upvote 0
Top