# Issue with vision scripting and SQL queries

**URL:** <https://forum.inductiveautomation.com/t/issue-with-vision-scripting-and-sql-queries/78748>\
**Category:** Ignition\
**Tags:** ignition81, vision, scripting, database\
**Created:** [September 5, 2023, 6:42am UTC](https://forum.inductiveautomation.com/t/issue-with-vision-scripting-and-sql-queries/78748 "2023-09-05T06:42:42Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Surya\_Chandra\_Mohan](https://avatars.discourse-cdn.com/v4/letter/s/a9adbd/32.png) [@Surya\_Chandra\_Mohan](https://forum.inductiveautomation.com/u/Surya_Chandra_Mohan)\
**Post date:** [September 5, 2023, 6:42am UTC](https://forum.inductiveautomation.com/t/issue-with-vision-scripting-and-sql-queries/78748/1 "2023-09-05T06:42:42Z")

</div>

Hello,  
I am trying to insert data into the table and there is user\_id in the table. I am selecting username from dropdown and the id of that username should be entered into the table but when inserting, the user\_id is entered as 0

**sensor\_name = event.source.parent.getComponent('Text Field').text**  
**sensor\_description = event.source.parent.getComponent('Text Field 1').text**  
**selected\_user = event.source.parent.getComponent('Dropdown').selectedValue**  
**selected\_station = event.source.parent.getComponent('Dropdown 1').selectedValue**  
**assign\_time = event.source.parent.getComponent('Popup Calendar').date**

**# Query to retrieve the user\_id based on the selected username**  
**user\_query = "SELECT s.sensor\_id, s.sensor\_name, s.sensor\_description, guu.id AS user\_id, s.station\_number, s.assign\_date FROM sensors s JOIN users g ON s.user\_id = g.id WHERE g.username = ?"**  
**user\_args = [selected\_user]**

**# Execute the query to get the user\_id**  
**user\_id\_result = system.db.runPrepQuery(user\_query, user\_args, "Database")**

**# Check if a user\_id was found**  
**if user\_id\_result.getRowCount() \> 0:**  
\*\* user\_id = user\_id\_result.getValueAt(0, "user\_id")\*\*  
**else:**  
\*\* # Handle the case where the selected username was not found\*\*  
\*\* user\_id = None\*\*

**payload = [6, sensor\_name, sensor\_description, selected\_user, selected\_station, assign\_time, None]**  
**args = [sensor\_name, sensor\_description, selected\_user, selected\_station, assign\_time]**

**#Adding to database**  
**query = "INSERT INTO sensors(sensor\_name,sensor\_description,user\_id,station\_number,assign\_date) VALUES (?,?,?,?,?)"**  
**system.db.runPrepUpdate(query, args, "Database")**  
**system.db.refresh(event.source.parent.getComponent('Table'), "data")**

**event.source.parent.getComponent('Text Field').text = None**  
**event.source.parent.getComponent('Text Field 1').text = None**  
**event.source.parent.getComponent('Dropdown').selectedValue = None**  
**event.source.parent.getComponent('Dropdown 1').selectedValue = None**  
**event.source.parent.getComponent('Popup Calendar').date = None**

This is the script used.  
It would be a great help if somebody could assist me with some help and thank you in advance

The error says - Problem running system.db.runPrepUpdate and that the current user does not have the required roles for this operation

---

<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:** [September 5, 2023, 11:48am UTC](https://forum.inductiveautomation.com/t/issue-with-vision-scripting-and-sql-queries/78748/2 "2023-09-05T11:48:58Z")

</div>

> [@Surya\_Chandra\_Mohan](#):
>
> current user does not have the required roles for this operation

You are probably lacking legacy DB permissions. You should **not** turn on legacy DB permissions, but convert to a named query.

---

<div class="post-metadata">

**Author:** ![Surya\_Chandra\_Mohan](https://avatars.discourse-cdn.com/v4/letter/s/a9adbd/32.png) [@Surya\_Chandra\_Mohan](https://forum.inductiveautomation.com/u/Surya_Chandra_Mohan)\
**Post date:** [September 5, 2023, 12:31pm UTC](https://forum.inductiveautomation.com/t/issue-with-vision-scripting-and-sql-queries/78748/3 "2023-09-05T12:31:01Z")

</div>

Thanks! Appreciate it!