# Updating a query from a table

**URL:** <https://forum.inductiveautomation.com/t/updating-a-query-from-a-table/96193>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [September 19, 2024, 4:12pm UTC](https://forum.inductiveautomation.com/t/updating-a-query-from-a-table/96193 "2024-09-19T16:12:14Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Alex\_Ford-King](https://avatars.discourse-cdn.com/v4/letter/a/ebca7d/32.png) [@Alex\_Ford-King](https://forum.inductiveautomation.com/u/Alex_Ford-King)\
**Post date:** [September 19, 2024, 4:12pm UTC](https://forum.inductiveautomation.com/t/updating-a-query-from-a-table/96193/1 "2024-09-19T16:12:14Z")

</div>

I have table that is bound to a database but i want to be able to edit any cell and have it update the database. Im using the onEditCellCommit action of the table and have this coded so far.  
Def runAction(self, event):

```
valueToSet = event.value

self.props.data = system.dataset.setValue(self.props.data, event.row, event.column, valueToSet)

query= "EditData"

system.db.runNamedQuery(query)

```

And my query looks like this  
UPDATE EvisData1

Set  
Trailer = :Trailer,  
Batch = :Batch

Right now it just deletes the data. I was trying to copy the data entry example from the example quick start. Im very new to scripting so i couldnt really follow how they were doing it. Any suggestions would be appriciated!

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [September 19, 2024, 4:19pm UTC](https://forum.inductiveautomation.com/t/updating-a-query-from-a-table/96193/2 "2024-09-19T16:19:16Z")

</div>

> [@Alex\_Ford-King](#):
>
> ```python
> system.db.runNamedQuery(query)
> 
> ```

You're not passing any parameters to your query, so it just sends nulls instead.  
You need to pass a dict containing keys that match your query parameters:

```python
params = {
  'Trailer': value_for_trailer,
  'Batch': value_for_batch
}
system.db.runNamedQuery(query, params)

```

Also, take a look at this:

> [@Wiki - how to post code on this forum](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831):
>
> This thread is intended as a short help file for all those struggling to post formatted code on the forum. Code block formatting Posting formatted code on the forum (and most others) is as simple as selecting the code and pressing the \</\> button. It works the same as applying Bold or Italic styles to your document. It preserves indentation and applies syntax highlighting. [How to post code] Note that all the \</\> button does is add three backticks ``` on the line before the code block and anot…

---

<div class="post-metadata">

**Author:** ![Alex\_Ford-King](https://avatars.discourse-cdn.com/v4/letter/a/ebca7d/32.png) [@Alex\_Ford-King](https://forum.inductiveautomation.com/u/Alex_Ford-King)\
**Post date:** [September 19, 2024, 4:41pm UTC](https://forum.inductiveautomation.com/t/updating-a-query-from-a-table/96193/3 "2024-09-19T16:41:04Z")

</div>

What do i have to pass to the query to allow the values to stay. I need to define the value\_for\_trailer but how do i define it for whatever value is entered in the cell?

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [September 19, 2024, 4:50pm UTC](https://forum.inductiveautomation.com/t/updating-a-query-from-a-table/96193/4 "2024-09-19T16:50:59Z")

</div>

Get the data from the dataset, using the selected row and and column like you did to update the dataset.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [September 19, 2024, 5:18pm UTC](https://forum.inductiveautomation.com/t/updating-a-query-from-a-table/96193/5 "2024-09-19T17:18:37Z")

</div>

Your original query should be returning the `id` column (or whatever the unique key is for each row) and pass that back to the UPDATE query so that it knows which row to update. It would then look something like this:

```python
UPDATE EvisData1
SET
    Trailer = :Trailer,
    Batch = :Batch
WHERE
    id = :id

```

The id column can be displayed in the table (handy for debug) or not.

---

<div class="post-metadata">

**Author:** ![Alex\_Ford-King](https://avatars.discourse-cdn.com/v4/letter/a/ebca7d/32.png) [@Alex\_Ford-King](https://forum.inductiveautomation.com/u/Alex_Ford-King)\
**Post date:** [September 19, 2024, 5:36pm UTC](https://forum.inductiveautomation.com/t/updating-a-query-from-a-table/96193/6 "2024-09-19T17:36:28Z")

</div>

```python
# Get the table component
	table = self.parent.parent.getChild("Table")

# Get the selected row index
	row = table.props.selection.selectedRow

# Check if a row is selected
	if row is not None:
    # Get the data from the selected row
    		rowData = table.props.data[row]

    # Populate input fields with selected row data
    	self.parent.parent.getChild("ColumnContainer").getChild("BatchInput").props.value = rowData

```

I Couldnt get it with the editcellcommand so im trying with an edit button but its saying `rowdata = table.props.data[row]` is unscriptable. What am i doing wrong?

---

<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:** [September 20, 2024, 11:55am UTC](https://forum.inductiveautomation.com/t/updating-a-query-from-a-table/96193/7 "2024-09-20T11:55:59Z")

</div>

> [@Alex\_Ford-King](#):
>
> What do i have to pass to the query to allow the values to stay. I need to define the value\_for\_trailer but how do i define it for whatever value is entered in the cell?

Once you've updated the database, you need to update the binding on the table to get it to pull new data containing the just edited values.

`self.refreshBinding('props.data')`

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [September 20, 2024, 12:12pm UTC](https://forum.inductiveautomation.com/t/updating-a-query-from-a-table/96193/8 "2024-09-20T12:12:54Z")

</div>

Shouldn't be necessary, since it seems like he's updating the database from the data in the table.

> [@Alex\_Ford-King](#):
>
> I Couldnt get it with the editcellcommand so im trying with an edit button but its saying `rowdata = table.props.data[row]` is unscriptable. What am i doing wrong?

`table.props.data` is a dataset. You can't use the `[]` operator on those.  
You need to use `table.props.data.getValueAt(row, col)`