Power Table OnCellEdited

#1

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

values = [value,ID]

try:
system.db.runPrepUpdate(query,values,dataSource)
except:
system.gui.warningBox(“SQL Insert Failed”)

0 Likes

#2

Welcome to the forum.

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.

0 Likes

#3

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’)

0 Likes

#4

I think you’re close.

First off correct this line by closing the parenthesis

ndx= self.data.getValueAt(row,0

The column name has to be added to the query string when it is defined, not inside the runPrepUpdate function.

query = “Update vdk SET %s = ? WHERE ndx = ?” % colName

Fix those things and try again.

When posting code on the forum type 3 back quote characters (Upper left key on most keyboards) before and after the code to make it readable.

0 Likes

#5

What have I neglected this time, Still nothing being written to dB. No errors either.

 row = rowIndex
 col = colIndex
 colName = colName
 value = newValue
	
 ndx = self.data.getValueAt(row,0)
 query = "UPDATE vdk SET %s = (?) WHERE ndx = (?)" %colName
 system.db.runPrepUpdate([value,ndx],'history')
0 Likes

#6

The first parameter in runPrepUpdate has to be the query string.

If you update the line below all of your syntax should be correct, assuming that “history” is the name of your database.

system.db.runPrepUpdate(query, [value,ndx],'history')

https://docs.inductiveautomation.com/display/DOC79/system.db.runPrepUpdate

0 Likes

#7

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?

0 Likes

#8

You’re welcome.

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!

0 Likes

#9

Any tips on what I have done wrong?

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)
0 Likes

Updating data in SQL with Power Table
#10

Check your indentation. Python is picky about that. If you are getting an error, please share the full details.

0 Likes

#11

This is the error I am getting after indenting

Parse error for extension function “onCellEdited” SyntaxError: (“mismatched input ‘’ expecting DEDENT”, (’ ', 19, 2, ‘\t\tIndex = self.data.getValueAt(rowIndex,0)\n’))

Some of the values in my table are datetime. Would that affect it?

0 Likes

#12

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.

0 Likes

#13
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)
0 Likes

#14

Remove one level of indentation – it has to match the indentation of the pre-defined method comment.

0 Likes

#15

line your script up with the last quotes (red) as pturmel just mentioned

0 Likes

#16

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)
0 Likes

#17

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.

0 Likes

#18

system.db.refresh() takes the second argument as a string.
system.db.refresh(self,'data') is correct

0 Likes