# From Perspective, I want to edit a DB cell from a table selected cell

**URL:** <https://forum.inductiveautomation.com/t/from-perspective-i-want-to-edit-a-db-cell-from-a-table-selected-cell/85519>\
**Category:** Ignition\
**Tags:** perspective, database\
**Created:** [February 10, 2024, 10:55am UTC](https://forum.inductiveautomation.com/t/from-perspective-i-want-to-edit-a-db-cell-from-a-table-selected-cell/85519 "2024-02-10T10:55:37Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![Leandro\_Garcia\_Carva](https://avatars.discourse-cdn.com/v4/letter/l/6f9a4e/32.png) [@Leandro\_Garcia\_Carva](https://forum.inductiveautomation.com/u/Leandro_Garcia_Carva)\
**Post date:** [February 10, 2024, 10:55am UTC](https://forum.inductiveautomation.com/t/from-perspective-i-want-to-edit-a-db-cell-from-a-table-selected-cell/85519/1 "2024-02-10T10:55:37Z")

</div>

Hi I just want to write a selected cell of DB from a table. Could you give me a simple script sample?

Regards,  
Leandro

---

<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:** [February 10, 2024, 12:05pm UTC](https://forum.inductiveautomation.com/t/from-perspective-i-want-to-edit-a-db-cell-from-a-table-selected-cell/85519/2 "2024-02-10T12:05:28Z")

</div>

If you want to edit a database table using a Perspective table then the basic sequence is:

- Add the table to the view.
- Create a binding on the table's data property to the database.
- Fill in the `columns.X.field` properties so the table knows which database column to use for each table column.
- Set the `columns.X.editable` properties as required.
- Right-click on the table and select Configure Events. Create an onCellEdit event. This is where you will write your script.
- In the script, do any necessary data validation and then run an UPDATE query.
- Finally, refresh the table's data properties.

You should be able to find further details on the forum.

---

<div class="post-metadata">

**Author:** ![Leandro\_Garcia\_Carva](https://avatars.discourse-cdn.com/v4/letter/l/6f9a4e/32.png) [@Leandro\_Garcia\_Carva](https://forum.inductiveautomation.com/u/Leandro_Garcia_Carva)\
**Post date:** [February 10, 2024, 12:49pm UTC](https://forum.inductiveautomation.com/t/from-perspective-i-want-to-edit-a-db-cell-from-a-table-selected-cell/85519/3 "2024-02-10T12:49:22Z")

</div>

Hi thank you it was very helpful. I’m stuck at the point 5 and 6.

I don’t know how to send the parameters to named query since it’s a selected cell. Could you give me a sample?

Thanks

---

<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:** [February 10, 2024, 1:02pm UTC](https://forum.inductiveautomation.com/t/from-perspective-i-want-to-edit-a-db-cell-from-a-table-selected-cell/85519/4 "2024-02-10T13:02:54Z")

</div>

I might be able to dig out a sample later. Meanwhile have a look at the docs.

[https://docs.inductiveautomation.com/display/DOC81/Perspective+-+Table+Scripting](https://docs.inductiveautomation.com/display/DOC81/Perspective+-+Table+Scripting)

Then have a look at [Quick help - editable Perspective Table - #3 by PGriffith](https://forum.inductiveautomation.com/t/quick-help-editable-perspective-table/31961/3) and pay particular attention to the posts by the IA staff (identifiable by the IA icon on their photographs).

---

<div class="post-metadata">

**Author:** ![Leandro\_Garcia\_Carva](https://avatars.discourse-cdn.com/v4/letter/l/6f9a4e/32.png) [@Leandro\_Garcia\_Carva](https://forum.inductiveautomation.com/u/Leandro_Garcia_Carva)\
**Post date:** [February 10, 2024, 1:34pm UTC](https://forum.inductiveautomation.com/t/from-perspective-i-want-to-edit-a-db-cell-from-a-table-selected-cell/85519/5 "2024-02-10T13:34:39Z")

</div>

Hi,

Thank you, sorry for bothering you about this. I’m not so skilled.

Regards,  
Leandro

---

<div class="post-metadata">

**Author:** ![Leandro\_Garcia\_Carva](https://avatars.discourse-cdn.com/v4/letter/l/6f9a4e/32.png) [@Leandro\_Garcia\_Carva](https://forum.inductiveautomation.com/u/Leandro_Garcia_Carva)\
**Post date:** [February 10, 2024, 5:42pm UTC](https://forum.inductiveautomation.com/t/from-perspective-i-want-to-edit-a-db-cell-from-a-table-selected-cell/85519/6 "2024-02-10T17:42:42Z")

</div>

Hi,

I tried this but isn't working,

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/9/d9791c39fd3e855881177cd2e32404cd03fdd77e.png)  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/4/64d77349379277ee92f091935dfe41b80d5fe7fd.png)

Could you help me?

---

<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:** [February 10, 2024, 5:51pm UTC](https://forum.inductiveautomation.com/t/from-perspective-i-want-to-edit-a-db-cell-from-a-table-selected-cell/85519/7 "2024-02-10T17:51:48Z")

</div>

You're missing the closing `)` on the runPrepUpdate command.

Tip: when showing code it's a good idea to post formatted code rather than pictures of code. That way we can edit it / test it. If the context is relevant then post a cropped screengrab as well.

---

<div class="post-metadata">

**Author:** ![Leandro\_Garcia\_Carva](https://avatars.discourse-cdn.com/v4/letter/l/6f9a4e/32.png) [@Leandro\_Garcia\_Carva](https://forum.inductiveautomation.com/u/Leandro_Garcia_Carva)\
**Post date:** [February 10, 2024, 6:03pm UTC](https://forum.inductiveautomation.com/t/from-perspective-i-want-to-edit-a-db-cell-from-a-table-selected-cell/85519/8 "2024-02-10T18:03:06Z")

</div>

> [@Leandro\_Garcia\_Carva](#):
>
> Leandro

Hi, got it  
Now this error message is appearing

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

Please look the code

```auto
def runAction(self, event):
	r = event['row']
	c = event['column']
	v = event['value']
	
	id = self.props.data[r].ID
	
	system.db.runPrepUpdate("UPDATE holdLog SET reasonforHold = ? WHERE ID=?", [v, id])
	
	self.refreshBinding("props.data")

```

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/a/9acb98e71c348342980e1afc5731205466163874.png)

Its still not working. COuld you help me?

---

<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:** [February 10, 2024, 6:25pm UTC](https://forum.inductiveautomation.com/t/from-perspective-i-want-to-edit-a-db-cell-from-a-table-selected-cell/85519/9 "2024-02-10T18:25:11Z")

</div>

It looks like you re-posted the old screen grab. The ) is still missing and there is an error warning red rectangle on the right. Your pasted code is correct though.

1. Is there an `ID` column in the table?
2. Is it actually `ID` and not `id` or `Id`?

---

<div class="post-metadata">

**Author:** ![Leandro\_Garcia\_Carva](https://avatars.discourse-cdn.com/v4/letter/l/6f9a4e/32.png) [@Leandro\_Garcia\_Carva](https://forum.inductiveautomation.com/u/Leandro_Garcia_Carva)\
**Post date:** [February 10, 2024, 6:32pm UTC](https://forum.inductiveautomation.com/t/from-perspective-i-want-to-edit-a-db-cell-from-a-table-selected-cell/85519/10 "2024-02-10T18:32:58Z")

</div>

Yes, there is an ID column.

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

Please, take a look

---

<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:** [February 10, 2024, 6:36pm UTC](https://forum.inductiveautomation.com/t/from-perspective-i-want-to-edit-a-db-cell-from-a-table-selected-cell/85519/11 "2024-02-10T18:36:24Z")

</div>

And all the ID entries are `NULL`.  
That's your problem. You can't identify the row and you won't be able to update it.

---

<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:** [February 10, 2024, 7:06pm UTC](https://forum.inductiveautomation.com/t/from-perspective-i-want-to-edit-a-db-cell-from-a-table-selected-cell/85519/12 "2024-02-10T19:06:34Z")

</div>

Can you modify the table and set the ID column to "primary key" and "auto-increment". (The names may vary depending on the database - MySQL / MS SQL, etc.) I don't know if you can apply this retrospectively.

Here's a procedure for MySQL:

> **[How to Add Auto Increment Column in Existing Table in MYSQL? - Scaler Topics](https://www.scaler.com/topics/how-to-add-auto-increment-column-in-existing-table-in-mysql/)**
>
> Auto Increment columns automatically increase in value as you add more rows to the table. In this article, we will look at how to add an auto-increment column in the MySQL

The idea is that the ID column is unique number for each row. That way the database engine knows which row to modify with the UPDATE query and there is no danger of having duplicate IDs.

---

<div class="post-metadata">

**Author:** ![Leandro\_Garcia\_Carva](https://avatars.discourse-cdn.com/v4/letter/l/6f9a4e/32.png) [@Leandro\_Garcia\_Carva](https://forum.inductiveautomation.com/u/Leandro_Garcia_Carva)\
**Post date:** [February 10, 2024, 7:45pm UTC](https://forum.inductiveautomation.com/t/from-perspective-i-want-to-edit-a-db-cell-from-a-table-selected-cell/85519/13 "2024-02-10T19:45:11Z")

</div>

Hi thank you,

I just made it and stills not working.  
This is my table

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/9/e9de6c41c64c9c9745e2a661a65ccff089d7fa5e.png)  
this is my code:

```auto
def runAction(self, event):
	r = event['row']
	c = event['column']
	v = event['value']
	
	id = self.props.data[r].id
	
	system.db.runPrepUpdate("UPDATE HoldLog2 SET reasonforHold = ? WHERE id=?", [v, id])
	
	self.refreshBinding("props.data")

```

Check the fault message below

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/d/5d0b8153df3332838a6c6d7b06297eb08ec60cbb.png)  
Could you help me again?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 10, 2024, 7:53pm UTC](https://forum.inductiveautomation.com/t/from-perspective-i-want-to-edit-a-db-cell-from-a-table-selected-cell/85519/14 "2024-02-10T19:53:18Z")

</div>

It seems your table data is in `Dataset` format? If so, you will need line #6 to be

```auto
	id = self.props.data.getValueAt(r, 'id')

```

---

<div class="post-metadata">

**Author:** ![Leandro\_Garcia\_Carva](https://avatars.discourse-cdn.com/v4/letter/l/6f9a4e/32.png) [@Leandro\_Garcia\_Carva](https://forum.inductiveautomation.com/u/Leandro_Garcia_Carva)\
**Post date:** [February 10, 2024, 7:57pm UTC](https://forum.inductiveautomation.com/t/from-perspective-i-want-to-edit-a-db-cell-from-a-table-selected-cell/85519/15 "2024-02-10T19:57:49Z")

</div>

> [@pturmel](#):
>
> `	id = self.props.data.getValueAt(r, 'id')`

I`ve got this fault now

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/a/dab82686838f0fc16e647598c2ce6e12adf50ed3.png)

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 10, 2024, 7:59pm UTC](https://forum.inductiveautomation.com/t/from-perspective-i-want-to-edit-a-db-cell-from-a-table-selected-cell/85519/16 "2024-02-10T19:59:34Z")

</div>

You need to provide the database connection name as a 3rd argument to `runPrepUpdate()`.

---

<div class="post-metadata">

**Author:** ![Leandro\_Garcia\_Carva](https://avatars.discourse-cdn.com/v4/letter/l/6f9a4e/32.png) [@Leandro\_Garcia\_Carva](https://forum.inductiveautomation.com/u/Leandro_Garcia_Carva)\
**Post date:** [February 10, 2024, 8:04pm UTC](https://forum.inductiveautomation.com/t/from-perspective-i-want-to-edit-a-db-cell-from-a-table-selected-cell/85519/17 "2024-02-10T20:04:09Z")

</div>

Sorry, this is the name CD\_HUBTRAININGEXPORT.

where should I place it?

SOmething like this?  
`system.db.runPrepUpdate("UPDATE HoldLog2 SET reasonforHold = ? WHERE id=?", [v, id], "CD_HUBTRAININGEXPORT")`

---

<div class="post-metadata">

**Author:** ![Leandro\_Garcia\_Carva](https://avatars.discourse-cdn.com/v4/letter/l/6f9a4e/32.png) [@Leandro\_Garcia\_Carva](https://forum.inductiveautomation.com/u/Leandro_Garcia_Carva)\
**Post date:** [February 10, 2024, 8:09pm UTC](https://forum.inductiveautomation.com/t/from-perspective-i-want-to-edit-a-db-cell-from-a-table-selected-cell/85519/18 "2024-02-10T20:09:57Z")

</div>

That works!!!! Amazing

Thank you all  
@pturmel and @Transistor

sorry for disturbing you !!!!  
See you!!!