Android Question SQL and B4xTable updating

[Scantech]

I have SQL in starter and B4XTable which uses B4XTable1.SetData(Data) from SQL. I want to edit SQL and B4XTable but do not know how to refresh the data in B4XTable. I can update the SQL with

Starter.SQL1.ExecNonQuery2("UPDATE diagnosticlog SET RO = ?, Date = ?, FirstName = ?, LastName = ?, VIN = ?, Year = ?, Make = ?, Model = ?, FileName = ? WHERE rowid = ?", Array As String(WhatRo, WhatDate, WhatFirstName, WhatLastName, WhatVin, WhatYear, WhatMake, WhatModel, WhatFilename, RowId))

How do i refresh the B4XTable? Do i have to setdata again? I just want to update the row which was edited.

 

[mangojack]

I recall this code to Update a single cell ... https://www.b4x.com/android/forum/threads/b4xtable-update-a-single-cell.105365/post-660034

Unsure / have not tried to expand it to update multiple row cells. Possible you might have to run multiple times in a loop or similar.

 

[mangojack]

Another possible option ..

Delete that row ... https://www.b4x.com/android/forum/threads/b4x-b4xtable-delete-row.103582/post-649300
And reinsert the row with updated data ... https://www.b4x.com/android/forum/threads/b4xtable-add-lines-rows.104549/post-655142

Keeping in mind @Erel 's comment later .. "Call B4XTable1.ClearDataView after you add or remove rows. This will update the counter."

But this might not be a good solution , as I am sure the new row is just added to the end of Table.

 

[Scantech]

I recall this code to Update a single cell ... https://www.b4x.com/android/forum/threads/b4xtable-update-a-single-cell.105365/post-660034

Unsure / have not tried to expand it to update multiple row cells. Possible you might have to run multiple times in a loop or similar.

Thanks I will try it later on.

Or maybe its similar to my code above but use data instead of diagnosticlog with B4XTable.sql1

 

[Erel]

You should call B4XTable.UpdateTableCounters after you modify the internal db.

 

[Scantech]

You should call B4XTable.UpdateTableCounters after you modify the internal db.

I can't find UpdateTableCounters?

This is the code i use but does not refresh the data.

        B4XTable1.SQL1.ExecNonQuery2("UPDATE data SET c1 = ?, c2 = ?, c3 = ?, c4 = ?, c5 = ?, c6 = ?, c7 = ?, c8 = ?, c9 = ? WHERE rowid = ?", Array As String(RowId, WhatRo, WhatDate, WhatFirstName, WhatLastName, WhatVin, WhatYear, WhatMake, WhatModel, WhatFilename))
        B4XTable1.Refresh

My first coloumn is 4 buttons, second coloumn is RowId. How can i make this work?

 

[Scantech]

Updated to 1.21 and used these codes. It did not update the b4xtable values. Note: i am just changing the RO and year values just for testing purpose.

Sub btnEdit_Click
    'Finish this later.  maybe add form to fill out
    Try
        Dim RowId As Long = GetRowId(Sender)
        Dim Item As Map = B4XTable1.GetRow(RowId)

        Dim WhatRo, WhatDate, WhatFirstName, WhatLastName, WhatVin, WhatYear, WhatMake, WhatModel, WhatFilename As String

        WhatRo = Item.GetDefault("RO", "")
        WhatDate = Item.GetDefault("Date", "")
        WhatFirstName = Item.GetDefault("First", "")
        WhatLastName = Item.GetDefault("Last", "")
        WhatVin = Item.GetDefault("VIN", "")
        WhatYear = Item.GetDefault("Year", "")
        WhatMake = Item.GetDefault("Make", "")
        WhatModel = Item.GetDefault("Model", "")
        WhatFilename = Item.GetDefault("File Name", "")
   
        Dim id As InputDialog
        Dim sf As Object = id.ShowAsync("", "Enter RO Update", "Ok", "", "Cancel", Null, False)
        Wait For (sf) Dialog_Result(Result As Int)
        If Result = DialogResponse.POSITIVE Then
            WhatRo = id.Input
            WhatYear = "1111"
        End If
   
'        Private Query As String
'        Private Cursor1 As Cursor
'
'        'first we check if the entry already does exist
'        Query = "SELECT * FROM diagnosticlog WHERE RO = ? AND Date = ? AND FirstName = ? AND LastName = ? AND VIN = ? AND Year = ? AND Make = ? AND Model = ? AND FileName = ?"
'        Cursor1 = Starter.SQL1.ExecQuery2(Query, Array As String (WhatRo, WhatDate, WhatFirstName, WhatLastName, WhatVin, WhatYear, WhatMake, WhatModel, WhatFilename))
'  
'        If Cursor1.RowCount > 0 Then
'            Log("yes")
'        End If
   
   
        Starter.SQL1.ExecNonQuery2("UPDATE diagnosticlog SET RO = ?, Date = ?, FirstName = ?, LastName = ?, VIN = ?, Year = ?, Make = ?, Model = ?, FileName = ? WHERE rowid = ?", Array As String(WhatRo, WhatDate, WhatFirstName, WhatLastName, WhatVin, WhatYear, WhatMake, WhatModel, WhatFilename, RowId))
        B4XTable1.SQL1.ExecNonQuery2("UPDATE data SET c1 = ?, c2 = ?, c3 = ?, c4 = ?, c5 = ?, c6 = ?, c7 = ?, c8 = ?, c9 = ? WHERE rowid = ?", Array As String(RowId, WhatRo, WhatDate, WhatFirstName, WhatLastName, WhatVin, WhatYear, WhatMake, WhatModel, WhatFilename))
        B4XTable1.UpdateTableCounters
   
'        Cursor1.Close
    Catch
        Msgbox2Async("The following error occurred" & Chr(10) & Chr(10) & LastException.Message, "Database Edit Error", "OK", "", "", LoadBitmap(File.DirAssets, "disclaimer.png"), True)
    End Try
End Sub

The Starter SQL gets updated successfully. When i exit my b4xTable form and reenter b4xtable form using SetData, the values are shown to be updated.

 

[Scantech]

I found the solution.

B4XTable1.SQL1.ExecNonQuery2("UPDATE data SET c2 = ?, c3 = ?, c4 = ?, c5 = ?, c6 = ?, c7 = ?, c8 = ?, c9 = ?, c10 = ? WHERE rowid = ?", Array As String(WhatRo, WhatDate, WhatFirstName, WhatLastName, WhatVin, WhatYear, WhatMake, WhatModel, WhatFilename, RowId))
B4XTable1.Refresh

I am uncertain if B4XTable.UpdateTableCounters is neccessary. I use B4XTable.Refresh and works ok. I can't use C0, C1. Those are Buttons and RowID. C2 to C10 are the data that needs updating.

 

[rodmcm]

My approach has been to upload the data to B4xTable then do all the editing/adding in B4xTable and then save back to the DB table.
in this way all the edit routines are common for all DBtables entered
I would be interested in comments as to this approach and the double sort method shown above