# Update Table with Binded DB Query

**URL:** <https://forum.inductiveautomation.com/t/update-table-with-binded-db-query/61848>\
**Category:** Ignition\
**Tags:** ignition81, perspective\
**Created:** [July 13, 2022, 1:39pm UTC](https://forum.inductiveautomation.com/t/update-table-with-binded-db-query/61848 "2022-07-13T13:39:40Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![csilk](https://avatars.discourse-cdn.com/v4/letter/c/9e8a1a/32.png) [@csilk](https://forum.inductiveautomation.com/u/csilk)\
**Post date:** [July 13, 2022, 1:39pm UTC](https://forum.inductiveautomation.com/t/update-table-with-binded-db-query/61848/1 "2022-07-13T13:39:40Z")

</div>

I am sure that there have been other posts about this, but I just can’t seem to find anything that has worked. I have a table which displays the data from a database. I want to be able to edit cells and then save the changes to the database.

I have the following code:

```auto
	row = event.row
	col = event.column

	id = self.data.getValueAt(row, col)
	query = "UPDATE ReportTracker SET %s = ? WHERE id = ?" %(col)
	system.db.runPrepUpdate(query,id)
	system.db.refresh(self,"data")

```

But I am getting an error saying:

File “function:runAction”, line 5, in runAction AttributeError: ‘com.inductiveautomation.perspective.gateway.script’ object has no attribute ‘data’

Thank you in advance

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [July 13, 2022, 2:37pm UTC](https://forum.inductiveautomation.com/t/update-table-with-binded-db-query/61848/2 "2022-07-13T14:37:43Z")

</div>

You’re missing the reference to props. Also, to refresh a binding in perspective you call the `refreshBinding()` function on the component.

```auto
row = event.row
col = event.column

#assuming that self.props.data is a dataset and not an array of objects.
id = self.props.data.getValueAt(row,col)
query = "UPDATE ReportTracker SET %s = ? WHERE id = ?" % (col)
system.db.runPrepUpdate(query, [id])
self.refreshBinding('props.data')

```

---

<div class="post-metadata">

**Author:** ![csilk](https://avatars.discourse-cdn.com/v4/letter/c/9e8a1a/32.png) [@csilk](https://forum.inductiveautomation.com/u/csilk)\
**Post date:** [July 13, 2022, 2:42pm UTC](https://forum.inductiveautomation.com/t/update-table-with-binded-db-query/61848/3 "2022-07-13T14:42:43Z")

</div>

I updated my code and now am getting the following error:

> File "function:runAction", line 6, in runAction AttributeError: 'com.inductiveautomation.perspective.gateway.script' object has no attribute 'getValueAt'

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [July 13, 2022, 2:45pm UTC](https://forum.inductiveautomation.com/t/update-table-with-binded-db-query/61848/4 "2022-07-13T14:45:04Z")

</div>

How are you populating the `props.data` of the table? It appears it isn’t a dataset (notice the comment).

---

<div class="post-metadata">

**Author:** ![csilk](https://avatars.discourse-cdn.com/v4/letter/c/9e8a1a/32.png) [@csilk](https://forum.inductiveautomation.com/u/csilk)\
**Post date:** [July 13, 2022, 2:47pm UTC](https://forum.inductiveautomation.com/t/update-table-with-binded-db-query/61848/5 "2022-07-13T14:47:11Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/4/f4979f07e9254f45e613b59f843cf271e1b75c34.png)

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [July 13, 2022, 2:55pm UTC](https://forum.inductiveautomation.com/t/update-table-with-binded-db-query/61848/6 "2022-07-13T14:55:20Z")

</div>

Can you show the table properties?

---

<div class="post-metadata">

**Author:** ![csilk](https://avatars.discourse-cdn.com/v4/letter/c/9e8a1a/32.png) [@csilk](https://forum.inductiveautomation.com/u/csilk)\
**Post date:** [July 13, 2022, 3:01pm UTC](https://forum.inductiveautomation.com/t/update-table-with-binded-db-query/61848/7 "2022-07-13T15:01:29Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/0/e0b3afc8afd2a5c2ca729dc2026ae9fef6294832.png)

---

<div class="post-metadata">

**Author:** ![csilk](https://avatars.discourse-cdn.com/v4/letter/c/9e8a1a/32.png) [@csilk](https://forum.inductiveautomation.com/u/csilk)\
**Post date:** [July 13, 2022, 4:03pm UTC](https://forum.inductiveautomation.com/t/update-table-with-binded-db-query/61848/8 "2022-07-13T16:03:47Z")

</div>

I figured out the issue with that error, but now I am getting this error…

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/4/04b02ba11e05bec9256fa8e221018a927e6b2b7b.png)

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [July 13, 2022, 4:18pm UTC](https://forum.inductiveautomation.com/t/update-table-with-binded-db-query/61848/9 "2022-07-13T16:18:09Z")

</div>

You’re not sending any parameters in the parameters list.

---

<div class="post-metadata">

**Author:** ![csilk](https://avatars.discourse-cdn.com/v4/letter/c/9e8a1a/32.png) [@csilk](https://forum.inductiveautomation.com/u/csilk)\
**Post date:** [July 13, 2022, 4:42pm UTC](https://forum.inductiveautomation.com/t/update-table-with-binded-db-query/61848/10 "2022-07-13T16:42:29Z")

</div>

Is that where I need to add in `event.value` to my code?

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [July 13, 2022, 5:10pm UTC](https://forum.inductiveautomation.com/t/update-table-with-binded-db-query/61848/11 "2022-07-13T17:10:08Z")

</div>

Yes.

Try something like this:

```python
row = event.row
col = event.column

#assuming that self.props.data is a dataset and not an array of objects.
id = event.value
query = "UPDATE ReportTracker SET %s = ? WHERE id = ?" % (col)
system.db.runPrepUpdate(query, [id])
self.refreshBinding('props.data')

```

---

<div class="post-metadata">

**Author:** ![csilk](https://avatars.discourse-cdn.com/v4/letter/c/9e8a1a/32.png) [@csilk](https://forum.inductiveautomation.com/u/csilk)\
**Post date:** [July 13, 2022, 5:26pm UTC](https://forum.inductiveautomation.com/t/update-table-with-binded-db-query/61848/12 "2022-07-13T17:26:11Z")

</div>

So it seems like parameters are now being passed, but still receiving an error with this statement

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/8/c85ad1a84882ec6165bf6a904ff77d26776376df.png)

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [July 13, 2022, 5:35pm UTC](https://forum.inductiveautomation.com/t/update-table-with-binded-db-query/61848/13 "2022-07-13T17:35:25Z")

</div>

Sorry, wasn’t paying attention.

You need two parameters. Something like this, although it didn’t seem to work earlier.

```python
row = event.row
col = event.column

#assuming that self.props.data is a dataset and not an array of objects.
ds = self.props.data
id = ds.getValueAt(row,'id')

query = "UPDATE ReportTracker SET %s = ? WHERE id = ?" % (col)
system.db.runPrepUpdate(query, [event.value,id])
self.refreshBinding('props.data')

```

Your parameter list must have a value for each ‘?’ in the query.

---

<div class="post-metadata">

**Author:** ![csilk](https://avatars.discourse-cdn.com/v4/letter/c/9e8a1a/32.png) [@csilk](https://forum.inductiveautomation.com/u/csilk)\
**Post date:** [July 13, 2022, 5:57pm UTC](https://forum.inductiveautomation.com/t/update-table-with-binded-db-query/61848/14 "2022-07-13T17:57:20Z")

</div>

For my understanding, the first parameter is the new value entered in the cell and the second parameter is the selected cell in the table being edited?

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [July 13, 2022, 6:31pm UTC](https://forum.inductiveautomation.com/t/update-table-with-binded-db-query/61848/15 "2022-07-13T18:31:41Z")

</div>

I made an assumption that there is a column in the table (hidden or otherwise) which is named `'id'` where the id is stored. You’ll need to get that value from somewhere.

If you were to count the `'?'`'s in the query string starting from 0, then the index in the parameter list would coincide with the `'?'` that is being replaced. So the first parameter is the value that you are setting, and the second parameter is the id that you are filtering by.

---

<div class="post-metadata">

**Author:** ![csilk](https://avatars.discourse-cdn.com/v4/letter/c/9e8a1a/32.png) [@csilk](https://forum.inductiveautomation.com/u/csilk)\
**Post date:** [July 13, 2022, 7:17pm UTC](https://forum.inductiveautomation.com/t/update-table-with-binded-db-query/61848/16 "2022-07-13T19:17:46Z")

</div>

Right, that’s what I thought. I got it figured out, turns out you can’t have a space in the column header or else it throws everything off. Thank you for your help

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [July 13, 2022, 7:22pm UTC](https://forum.inductiveautomation.com/t/update-table-with-binded-db-query/61848/17 "2022-07-13T19:22:54Z")

</div>

While, I do recommend not having spaces in column names, you can do it, you just need to handle it.

In MS SQL you would do this by surrounding the column name with `[]`.

```auto
query = 'UPDATE ReportTracker SET [%s] = ? WHERE id = ?' % (col)

```

---

<div class="post-metadata">

**Author:** ![csilk](https://avatars.discourse-cdn.com/v4/letter/c/9e8a1a/32.png) [@csilk](https://forum.inductiveautomation.com/u/csilk)\
**Post date:** [July 15, 2022, 5:21pm UTC](https://forum.inductiveautomation.com/t/update-table-with-binded-db-query/61848/18 "2022-07-15T17:21:42Z")

</div>

Is it possible to do this if my table is an array of objects and not a dataset? I added a transform to `prop.data` to do some conditional cell background colors based on the cells values. In order to do so, I have to return the data as json and not a dataset. In doing so, I am now not able to edit cells.