Referencing a field entry within named query

Ignition
1

I am trying to use a numeric field to specify which employee to add an hour to using a button,
but i am stuck as my best attempt (picture below) was unsuccessful.

image
image1281Ă—716 39.7 KB

Any advice or alternate methods would be appreciated

2

A named query isn’t a view component so it doesn’t have a self property.

Try
WHERE employeeid = :employeeid

See Named Queries - Ignition User Manual 8.1 - Ignition Documentation.

2 Likes
3

You can’t. Not like this. A named query can’t reference elements from your UI.

You need to do it the other way around: call that name query on your button’s events, where you can reference something like a numeri entry field.

3 Likes
4

I have done that and it works but only when i specify in the query that employeeid = 1208 (or any valid ID)
My problem is wanting the numeric field entry to be the thing to specify what employeeid to select
this is my script event for a button
image

5

This works but where and how do i specify what employeeid is

6

You need to pass the parameter to the named query:

employee_id = self.getSibling("NumericEntryField").props.value
params = {
    'employeeid': employee_id 
}
system.db.runNamedQuery('query_name', params)
2 Likes
7

Tip: Please post code using the </> code formatting button. That way we can copy and edit it into our answers. If you only post a picture then we have to type it all out.

1 Like
8

Ok thank you for your help

9

I tried inserting this into my query and got the errors below,
I thought the query’s couldn’t reference the UI elements?
Should i make a separate query to define these parameters and link that to the components?

com.inductiveautomation.ignition.common.script.JythonExecException
Traceback (most recent call last):
  File "<function:runAction>", line 3, in runAction
java.lang.Exception: java.lang.Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'employee_id'.

	caused by org.python.core.PyException
Traceback (most recent call last):
  File "<function:runAction>", line 3, in runAction
java.lang.Exception: java.lang.Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'employee_id'.

	caused by Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'employee_id'.
	caused by SQLServerException: Incorrect syntax near 'employee_id'.

Ignition v8.1.15 (b2022030114)
Java: Azul Systems, Inc. 11.0.14.1
10

Put the code in the button’s onActionPerformed event, not in the query.

1 Like
11

Hey sorry to keep asking but this is what i put on the button

employee_id = self.getSibling("NumericEntryField_0").props.value
	params = {
	'employeeid': employee_id 
	}
	system.db.runNamedQuery('test', params)

But i get syntax errorslike these

com.inductiveautomation.ignition.common.script.JythonExecException
Traceback (most recent call last):
  File "<function:runAction>", line 7, in runAction
java.lang.Exception: java.lang.Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'employeeid'.

	caused by org.python.core.PyException
Traceback (most recent call last):
  File "<function:runAction>", line 7, in runAction
java.lang.Exception: java.lang.Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'employeeid'.

	caused by Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'employeeid'.
	caused by SQLServerException: Incorrect syntax near 'employeeid'.
12

The error is in the query. Show us what it looks like now.

1 Like
13 
update hoursworked
set hours = hours + 1   
where employeeid = :employeeid
14

Make sure the type your pass to the namedQuery matches the one it’s supposed to receive.

image
image1209Ă—187 11.9 KB

The numeric entry field’s value property is a long.

Apart from that, I don’t know.

1 Like
15

It worked! I changed it to int4 and removed a binding I had on my numeric entry and it works exactly as i wanted now!
Thank you for your time Pascal I really appreciate it, you are a real hero.