Edit Data in Database

A table component can do more than show data from a database. A properly configured table can make the data of the table accessible to the client and allow the user to edit the data in realtime. Suppose your database had a table like this:

id
UserName
FirstName
LastName
Notes

1

JS

John

Smith

Likes bikes

2

LJ

Luke

Johnson

Lives in town

3

PB

Peter

Burke

Enjoys cooking

The following is an example of using the Table component as opposed to the Power Table component.

  1. Start with a window that has a Table on it.

  2. Bind the Table's Data property to a SQL table.
    The data will appear in the table.

  3. Right-click on the Table and select Customizers > Table Customizer.

  4. Select the Editable check box for each of the table's columns that the user will be editing.

  5. Right-click the table and select Scripting.

  6. Creat e a script in the table's cell > cellEdited event handler.
    Using the following script will allow your table to be editable from a Client. The script works with a table that looks like the table above. Your table may be different.

    Edit Database
    id = event.source.data.getValueAt(event.row, 'id') #Get the id of the database column.
    headers = system.dataset.getColumnHeaders(event.source.data) #Get the headers
    query = "UPDATE User SET %s = ? WHERE id = ?" % (headers[event.column])
    args = [event.newValue, id]
    system.db.runPrepUpdate(query, args)

The following is an example of using the Power Table component.

  1. Start with a window that has a Table on it.

  2. Bind the Table's Data property to a SQL table.
    The data will appear in the table.

  3. Right-click on the Table and select Customizers > Table Customizer.

  4. Select the Editable check box for each of the table's columns that the user will be editing.

  5. Create a script in the table's onCellEdited extension function by selecting the onCellEdited extension function. Enable the function.
    Using the following script will allow your table to be editable from a Client. The script works with a table that looks like the table above. Your table may be different.

    Edit Database
    id = self.data.getValueAt(rowIndex, 'id') #Get the id of the database column.
    query = "UPDATE User SET %s = ? WHERE id = ?" % (colName)
    args = [newValue, id]
    system.db.runPrepUpdate(query, args)
    system.db.refresh(self,"data")