I am trying to get OnCellEdited to update my db with new values for my power table and have referenced help section and searched this bulletin. I am having difficulty based on what i have found. Can someone provide a sample script that will work. I am using version Ignition 7.8
Below is the code in ignition help section and I have put import system at beginning. I also checked the console for error output and is complaining about event. I suspect this code is not the most up to date in inductions online help. Can someone assist me please.
Thank you.
The row index of the edited row
row = event.row
The column index of the edited column
col = event.column
The column’s name
colName = event.source.data.getColumnName(col)
The new value
value = event.newValue
The primary key’s value (first column), so that the appropriate row can be updated
in the db
id = event.source.data.getValueAt(row, 0)
Run an update query to the table that is being edited to reflect any changes
query = “UPDATE customers SET %s = ? WHERE ID = ?”
system.db.runPrepStmt(query % colName, [value, id])
Code from this forum
import system
dataSource = “CapitalProjectsDB”
The row index of the edited row
row = rowIndex
The column index of the edited column
col = colIndex
The name of the edited column
colName = colName
The new value entered by the user
value = newValue
get the ID (primary key)
ID = self.data.getValueAt(row,0) #string that is the query section below in the runPrepUpdate
query = “UPDATE [WBS Table Detail] SET Comments = (?) WHERE ID = (?)”
list of values represented in string above by ‘?’ marks in the values portion of runPrepUpdate
Try commenting out the try / except block and just run the query. You should get an exception message that will hopefully have some useful troubleshooting information.
Post the exception message here if you can’t spot the problem.
FYI, when posting code select the entire code block and then click on the “Code” button above the post editing window to wrap your code in code formatting tags. That will preserve the formatting making it easier to read and spot errors.
I am learning the differences between the standard Table Component and the Power Table Component… Slowly.
I am trying to update a dB table using the onCellEdited Extension Function and having no luck.
Please look at the scripting below and tell me what I have done wrong. (vdk is Table Name).
row = rowIndex
col = colIndex
colName = colName
value = newValue
ndx= self.data.getValueAt(row,0
query = “Update vdk SET %s = ? WHERE ndx = ?”
system.db.runPrepUpdate(query % colName, [value, ndx], ‘history’)
Excellent. Thank you JGJohnson.
Thanks also for the posting tip and the link.
I’m a rookie using ignition but have had good success with the standard Table Component.
Using the Power Table Component is an entirely different beast (from my stand point).
Why so different?
The Power Table has a lot more features and makes it easy to do things that would not be possible in a standard table without diving into Java. It takes some time to learn how to use it, but it’s worth it!
No, that’s a simple python indentation error. It’s not running your script at all, since it doesn’t compile. You’ll have to show your complete script with indentation showing. Use a line with just three backquotes (these: `) above and below your pasted code so that it formats clearly.
def onCellEdited(self, rowIndex, colIndex, colName, oldValue, newValue):
"""
Called when the user has edited a cell in the table. It is up to the
implementation of this function to alter the underlying data that drives
the table. This might mean altering the dataset directly, or running a SQL
UPDATE query to update data in a database.
Arguments:
self: A reference to the component that is invoking this function.
rowIndex: Index of the row that was edited, relative to the underlying
dataset
colIndex: Index of the column that was edited, relative to the
underlying dataset
colName: Name of the column in the underlying dataset
oldValue: The old value at the location, before it was edited
newValue: The new value input by the user.
"""
Index = self.data.getValueAt(rowIndex,0)
query = "UPDATE Production_Schedule_Table SET %s = ? WHERE Index = ?" % colName
system.db.runPrepUpdate(query,[newValue,Index],'13290_Statistics')
system.db.refresh(self,data)
Now it is not giving me errors but when I change the data in the Power Table it does not save.
def onCellEdited(self, rowIndex, colIndex, colName, oldValue, newValue):
"""
Called when the user has edited a cell in the table. It is up to the
implementation of this function to alter the underlying data that drives
the table. This might mean altering the dataset directly, or running a SQL
UPDATE query to update data in a database.
Arguments:
self: A reference to the component that is invoking this function.
rowIndex: Index of the row that was edited, relative to the underlying
dataset
colIndex: Index of the column that was edited, relative to the
underlying dataset
colName: Name of the column in the underlying dataset
oldValue: The old value at the location, before it was edited
newValue: The new value input by the user.
"""
Index = self.data.getValueAt(rowIndex,0)
query = "UPDATE Production_Schedule_Table SET %s = ? WHERE Index = ?" % colName
system.db.runPrepUpdate(query,[newValue,Index],'13290_Statistics')
system.db.refresh(self,data)
run your query in the query browser and make sure your column names etc are correct. Is your primary key column in the db table really named ‘Index’? If it does work in the query browser, then something else is amiss. make sure the primary key is in the table dataset and is the first column for the way your script is written.