# Trouble with .getSelectedValues() from a List component

**URL:** <https://forum.inductiveautomation.com/t/trouble-with-getselectedvalues-from-a-list-component/17205>\
**Category:** Ignition\
**Tags:** ignition79, scripting\
**Created:** [March 2, 2018, 8:12pm UTC](https://forum.inductiveautomation.com/t/trouble-with-getselectedvalues-from-a-list-component/17205 "2018-03-02T20:12:43Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![JoshM](https://avatars.discourse-cdn.com/v4/letter/j/4af34b/32.png) [@JoshM](https://forum.inductiveautomation.com/u/JoshM)\
**Post date:** [March 2, 2018, 8:12pm UTC](https://forum.inductiveautomation.com/t/trouble-with-getselectedvalues-from-a-list-component/17205/1 "2018-03-02T20:12:43Z")

</div>

I am new to Ignition so please forgive my hopefully basic questions but I have been hunting for an answer for a couple hours now and seem to be no closer to finding a solution.

The documentation for a List component says that .getSelectedValues() returns an Object[], but what is this object?It does not seem to behave like standard DataSet or a pyDataSet.

When I try to convert it to a pyDataSet [(as suggested here)](https://support.inductiveautomation.com/index.php?/Knowledgebase/Article/View/52/0/how-to-work-with-datasets-in-ignition) I am getting a '1st arg can’t be coerced" error.

When I try to use it as standard DataSet (to list in a table) I am getting a “can’t convert to com.inductiveautomation.ignition.coomon.Dataset” error.

I can reference one data point at a time explicitly calling out the array location but I am not sure how that is helpful without being able to use the other functions such as rowCount to manually correct the output. The list is comprised of a simple sql query of string data points.

Here is my test script that I think should work(this is set to happen on property change of the list)  
dataset = event.source.getSelectedValues()  
event.source.parent.getComponent(‘Table5’).data = dataset

Any guidance would we wonderful.

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [March 2, 2018, 8:30pm UTC](https://forum.inductiveautomation.com/t/trouble-with-getselectedvalues-from-a-list-component/17205/2 "2018-03-02T20:30:55Z")

</div>

It is returning an array.  
So you can loop it and convert it if you want it in a table,  
Off the top of my head here so bear with me

> hdr = ['Values']  
> ds =   
> for r in event.source.getSelectedValues():  
> row = [r]  
> ds.append(row)  
> event.source.parent.getComponent('Table5').data = system.dataset.toDataSet(hdr,ds)

---

<div class="post-metadata">

**Author:** ![JoshM](https://avatars.discourse-cdn.com/v4/letter/j/4af34b/32.png) [@JoshM](https://forum.inductiveautomation.com/u/JoshM)\
**Post date:** [March 2, 2018, 8:39pm UTC](https://forum.inductiveautomation.com/t/trouble-with-getselectedvalues-from-a-list-component/17205/3 "2018-03-02T20:39:58Z")

</div>

Yep, knew it would be an easy solution. Thank you for such a perfect and prompt response!

---

<div class="post-metadata">

**Author:** ![JoshM](https://avatars.discourse-cdn.com/v4/letter/j/4af34b/32.png) [@JoshM](https://forum.inductiveautomation.com/u/JoshM)\
**Post date:** [March 2, 2018, 10:35pm UTC](https://forum.inductiveautomation.com/t/trouble-with-getselectedvalues-from-a-list-component/17205/4 "2018-03-02T22:35:34Z")

</div>

So maybe you can help with one more thing.

Now that it is a Dataset it is almost behaving the way I would expect, but not exactly. Instead of listing those returned values on a table, I want to use that data set in a WHERE IN SQL query. As I have it when I have only 1 thing select in the list it runs as it queries correctly and returns the data to the table. However as soon as I select a second item it fails to build the query correctly with the following error.

My Code:

> hdr = ['BatchID']  
> ds=

> for r in event.source.getSelectedValues():  
> row =[r]  
> ds.append(row)

> /# Intermediate Troubleshooting Step   
> dataset = system.dataset.toDataSet(hdr,ds)  
> event.source.parent.getComponent('Table5').data = dataset

> /# Desired Bulk Query  
> databaseconnection ="DBManual"

> selectquery = "SELECT SampleID from batchsamples where BatchID IN (?)"  
> selectargs = dataset   
> sampleselectquery = system.db.runPrepQuery(selectquery, selectargs, databaseconnection)

> event.source.parent.getComponent('TableExp').data = sampleselectquery

Error when selecting two items.

> Traceback (most recent call last):  
> File "event:mouseClicked", line 20, in   
> at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)

> at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepQuery(AbstractDBUtilities.java:287)

> at sun.reflect.GeneratedMethodAccessor421.invoke(Unknown Source)

> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

> at java.lang.reflect.Method.invoke(Unknown Source)

> java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepQuery(SELECT SampleID from batchsamples where BatchID IN (?), DBManual, [TT368, TT369], )

> at org.python.core.Py.JavaError(Py.java:495)  
> at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)  
> at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepQuery(AbstractDBUtilities.java:287)  
> at sun.reflect.GeneratedMethodAccessor421.invoke(Unknown Source)  
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)  
> at java.lang.reflect.Method.invoke(Unknown Source)  
> at org.python.core.PyReflectedFunction. **call** (PyReflectedFunction.java:186)  
> at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction. **call** (ScriptManager.java:429)  
> at org.python.core.PyObject. **call** (PyObject.java:422)  
> at org.python.core.PyObject. **call** (PyObject.java:426)  
> at org.python.pycode.\_pyx635.f$0(event:mouseClicked:22)  
> at org.python.pycode.\_pyx635.call\_function(event:mouseClicked)  
> at org.python.core.PyTableCode.call(PyTableCode.java:165)  
> at org.python.core.PyCode.call(PyCode.java:18)  
> at org.python.core.Py.runCode(Py.java:1275)  
> at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:634)  
> at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:180)  
> at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:271)  
> at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:57)  
> at com.sun.proxy.$Proxy31.mouseClicked(Unknown Source)  
> at com.inductiveautomation.factorypmi.application.components.util.EventDelegateDispatcher$MouseEventDispatcher.mouseClicked(EventDelegateDispatcher.java:97)  
> at java.awt.AWTEventMulticaster.mouseClicked(Unknown Source)  
> at java.awt.Component.processMouseEvent(Unknown Source)  
> at javax.swing.JComponent.processMouseEvent(Unknown Source)  
> at java.awt.Component.processEvent(Unknown Source)  
> at java.awt.Container.processEvent(Unknown Source)  
> at java.awt.Component.dispatchEventImpl(Unknown Source)  
> at java.awt.Container.dispatchEventImpl(Unknown Source)  
> at java.awt.Component.dispatchEvent(Unknown Source)  
> at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)  
> at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)  
> at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)  
> at java.awt.Container.dispatchEventImpl(Unknown Source)  
> at java.awt.Window.dispatchEventImpl(Unknown Source)  
> at java.awt.Component.dispatchEvent(Unknown Source)  
> at java.awt.EventQueue.dispatchEventImpl(Unknown Source)  
> at java.awt.EventQueue.access$500(Unknown Source)  
> at java.awt.EventQueue$3.run(Unknown Source)  
> at java.awt.EventQueue$3.run(Unknown Source)  
> at java.security.AccessController.doPrivileged(Native Method)  
> at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)  
> at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)  
> at java.awt.EventQueue$4.run(Unknown Source)  
> at java.awt.EventQueue$4.run(Unknown Source)  
> at java.security.AccessController.doPrivileged(Native Method)  
> at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)  
> at java.awt.EventQueue.dispatchEvent(Unknown Source)  
> at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)  
> at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)  
> at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)  
> at java.awt.EventDispatchThread.pumpEvents(Unknown Source)  
> at java.awt.EventDispatchThread.pumpEvents(Unknown Source)  
> at java.awt.EventDispatchThread.run(Unknown Source)  
> Caused by: java.lang.Exception: Error executing system.db.runPrepQuery(SELECT SampleID from batchsamples where BatchID IN (?), DBManual, [TT368, TT369], )  
> ... 52 more  
> Caused by: com.inductiveautomation.ignition.client.gateway\_interface.GatewayException: Parameter index out of range (2 \> number of parameters, which is 1).  
> at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.newGatewayException(GatewayInterface.java:341)  
> at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.sendMessage(GatewayInterface.java:315)  
> at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.sendMessage(GatewayInterface.java:268)  
> at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:819)  
> at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.runQuery(GatewayInterface.java:789)  
> at com.inductiveautomation.ignition.client.script.ClientDBUtilities.\_runPrepQuery(ClientDBUtilities.java:204)  
> at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepQuery(AbstractDBUtilities.java:284)  
> ... 50 more  
> Caused by: java.sql.SQLException: Parameter index out of range (2 \> number of parameters, which is 1).  
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)  
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)  
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)  
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)  
> at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3813)  
> at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3795)  
> at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4616)  
> at com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:4046)  
> at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166)  
> at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.setObject(SRConnectionWrapper.java:1055)  
> at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunQuery.run(RunQuery.java:118)  
> at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:77)  
> at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:405)  
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)  
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)  
> at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)  
> at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:837)  
> at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)  
> at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)  
> at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)  
> at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)  
> at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1160)  
> at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)  
> at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)  
> at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1092)  
> at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)  
> at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)  
> at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)  
> at org.eclipse.jetty.server.Server.handle(Server.java:518)  
> at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)  
> at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:244)  
> at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)  
> at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)  
> at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)  
> at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:246)  
> at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:156)  
> at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)  
> at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)  
> at java.lang.Thread.run(null)

> Ignition v7.9.5 (b2017111615)  
> Java: Oracle Corporation 1.8.0\_161

---

<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:** [March 2, 2018, 11:41pm UTC](https://forum.inductiveautomation.com/t/trouble-with-getselectedvalues-from-a-list-component/17205/5 "2018-03-02T23:41:06Z")

</div>

A question mark substitution can only provide a single value to the SQL query. You’ll have to use the length of the list to produce the correct number of question marks.

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [March 3, 2018, 12:11am UTC](https://forum.inductiveautomation.com/t/trouble-with-getselectedvalues-from-a-list-component/17205/6 "2018-03-03T00:11:00Z")

</div>

```auto
WhereClause=''
for r in event.source..getSelectedValues():
	WhereClause = WhereClause + "'" + str(r) + "',"
finalWhere = '('+WhereClause.rstrip(',')+')'
selectstring = "SELECT FROM MyTable WHERE MyField in " + finalWhere
print selectstring

```

You can build up your where clause into a comma separated list and then use it as a WHERE IN