# Power Table Dataset From Named Query - How to runPrepUpdate?

**URL:** <https://forum.inductiveautomation.com/t/power-table-dataset-from-named-query-how-to-runprepupdate/24025>\
**Category:** Ignition\
**Tags:** ignition79, database, scripting\
**Created:** [April 11, 2019, 1:06am UTC](https://forum.inductiveautomation.com/t/power-table-dataset-from-named-query-how-to-runprepupdate/24025 "2019-04-11T01:06:10Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![paulm1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paulm1/32/5900_2.png) [@paulm1](https://forum.inductiveautomation.com/u/paulm1)\
**Post date:** [April 11, 2019, 1:06am UTC](https://forum.inductiveautomation.com/t/power-table-dataset-from-named-query-how-to-runprepupdate/24025/1 "2019-04-11T01:06:10Z")

</div>

I have a Power Table Component who’s Data Property is bound to a Named Query. Nothing Fancy a real simple query.

```
SELECT ndx, t_stamp AS Date, new AS New, repair AS Repair, prevent AS Prevent, inspect AS 
Inspect, note
FROM maintlog
WHERE id LIKE :SelectedCell
ORDER BY t_stamp ASC

```

How do I run an update from this Power Table’s Component Scripting - onCellEdited?  
I currently trying to use the following script.

```
#onCellEdited Update Query
row = rowIndex
col = colIndex
colName = colName
value = newValue
		
ndx = self.data.getValueAt(row,0)
query = "UPDATE maintlog SET %s = ? WHERE ndx = ?" % colName
system.db.runPrepUpdate(query,[value,ndx],'quality')
system.db.refresh(self.data)

```

This script has always worked for me on Power Tables when the Data property is bound to a SQL Query.

---

<div class="post-metadata">

**Author:** ![c.bertulli](https://avatars.discourse-cdn.com/v4/letter/c/f19dbf/32.png) [@c.bertulli](https://forum.inductiveautomation.com/u/c.bertulli)\
**Post date:** [April 11, 2019, 6:41am UTC](https://forum.inductiveautomation.com/t/power-table-dataset-from-named-query-how-to-runprepupdate/24025/2 "2019-04-11T06:41:23Z")

</div>

Which is the error message that this script gives you?  
Also, why did you do this `colName = colName`?

---

<div class="post-metadata">

**Author:** ![paulm1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paulm1/32/5900_2.png) [@paulm1](https://forum.inductiveautomation.com/u/paulm1)\
**Post date:** [April 11, 2019, 12:36pm UTC](https://forum.inductiveautomation.com/t/power-table-dataset-from-named-query-how-to-runprepupdate/24025/3 "2019-04-11T12:36:50Z")

</div>

I’m not getting any error msg. When your in the client and attempt to edit a cell, it appears like everything is working. The table is simply not getting updated and the cell returns to its original state.  
I have the cells set for “Editable” in the custom table properties.

As for the script, I am a complete rookie and I found this script to work when a power table is filled with a normal SQL query binding, so I have stuck with it.

---

<div class="post-metadata">

**Author:** ![c.bertulli](https://avatars.discourse-cdn.com/v4/letter/c/f19dbf/32.png) [@c.bertulli](https://forum.inductiveautomation.com/u/c.bertulli)\
**Post date:** [April 11, 2019, 1:50pm UTC](https://forum.inductiveautomation.com/t/power-table-dataset-from-named-query-how-to-runprepupdate/24025/5 "2019-04-11T13:50:11Z")

</div>

Ok, I got it!  
your problem is this line `system.db.refresh(self.data)`, which should be `system.db.refresh(self,'data')`

PS: Use this code instead, it’s cleaner 😉

```auto
	ndx = self.data.getValueAt(rowIndex,0)
	query = "UPDATE maintlog SET %s = ? WHERE ndx = ?" % colName
	system.db.runPrepUpdate(query,[newValue,ndx],'quality')
	system.db.refresh(self,'data')

```

---

<div class="post-metadata">

**Author:** ![paulm1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paulm1/32/5900_2.png) [@paulm1](https://forum.inductiveautomation.com/u/paulm1)\
**Post date:** [April 11, 2019, 5:27pm UTC](https://forum.inductiveautomation.com/t/power-table-dataset-from-named-query-how-to-runprepupdate/24025/6 "2019-04-11T17:27:04Z")

</div>

Thank you for your help.  
This is a little embarrassing, there must have been something wrong with our gateway last night.  
Everything was working fine this morning.  
I went ahead and made the change you suggested and it improved the client response time incredibly.  
With my old script, the client would edit a cell, hit enter or tab out of the cell and a second or two later the change would be displayed. By changing the last line to (self, ‘data’) the client edits are reflected immediately.  
Thanks again.