# Problems with dynamic filtering of DB query - "Value is not set for parameter x" **URL:** \ **Category:** Ignition\ **Tags:** vision, scripting, database\ **Created:** [July 17, 2023, 2:43pm UTC](https://forum.inductiveautomation.com/t/problems-with-dynamic-filtering-of-db-query-value-is-not-set-for-parameter-x/76459 "2023-07-17T14:43:30Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Mason\_Stevens](https://avatars.discourse-cdn.com/v4/letter/m/edb3f5/32.png) [@Mason\_Stevens](https://forum.inductiveautomation.com/u/Mason_Stevens)\ **Post date:** [July 17, 2023, 2:43pm UTC](https://forum.inductiveautomation.com/t/problems-with-dynamic-filtering-of-db-query-value-is-not-set-for-parameter-x/76459/1 "2023-07-17T14:43:31Z")
Hey all, have a little hang up that I'm not sure how to resolve. I am trying to set up a filtering system for a log. Presently, each input field for the filter has a checkbox that will enable or disable that field. Interacting with the checkbox will also alter the query that is run on button press to update the table. To do this, I have created a set of labels to hold each piece of the query, and a final label that simply sums the pieces. As a result, my query can look like either option below, with any possible combination of AND statements in between: _SELECT \* FROM rcp\_cleaninglog WHERE 0 = ? AND Reactor = ? AND Product = ? AND Step = ? AND cID = ? AND Initials = ?_ _SELECT \* FROM rcp\_cleaninglog WHERE 0 = ?_ The same is done for the arguments, as follows: _[0,reactor,prod,step,cid,init]_ _[0]_ I have included the 0 = 0 argument to allow a return of the entire dataset if no filters are applied, and to help with the flexibility (i.e., all actual arguments now require an 'AND' or preceding comma, no argument is "first" in the list, thus the formatting is standardized). However, there seems to be some disconnect that appears to occur only on the 'arguments' side. When running the following script: ```auto reactor = event.source.parent.getComponent('ReactorSlct').selectedLabel prod = event.source.parent.getComponent('ProductSlct').selectedLabel step = event.source.parent.getComponent('StepSlct').selectedLabel cid = event.source.parent.getComponent('CIDSlct').text init = event.source.parent.getComponent('InitialSlct').text filter = event.source.parent.getComponent('QueryTxt').text arg = event.source.parent.getComponent('ArgTxt').text ret = system.db.runPrepQuery(filter,arg) event.source.parent.getComponent('Wash Log').data = ret ``` The system will return the following error: | | caused by Exception: Error executing system.db.runPrepQuery(SELECT \* FROM rcp\_cleaninglog WHERE 0 = ? AND Reactor = ? AND Product = ? AND Step = ? AND cID = ? AND Initials = ?, , [0,reactor,prod,step,cid,init], ) | | --- | --- | | | caused by GatewayException: The value is not set for the parameter number 2. | | | caused by SQLServerException: The value is not set for the parameter number 2. | I have triple checked the component names/links and they all appear to be correct. It is also fully independent of what parameter(s) are chosen to filter by, I have tested each param by itself and with others. The error message above has the full argument contained within it, meaning the query should see each necessary parameter, I am completely unsure why they aren't making it all the way through. My best guess is an issue within the system.db.runPrepQuery(x,y,z) line, maybe a missing argument, but I've formatted the query script itself no different than any other in the past. Any help on this would be greatly appreciated! Thanks. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [July 17, 2023, 2:51pm UTC](https://forum.inductiveautomation.com/t/problems-with-dynamic-filtering-of-db-query-value-is-not-set-for-parameter-x/76459/2 "2023-07-17T14:51:36Z")
Look into short circuit filtering [How to run Script faster - Ignition - Inductive Automation Forum](https://forum.inductiveautomation.com/t/how-to-run-script-faster/54343/6) ---
**Author:** ![Mason\_Stevens](https://avatars.discourse-cdn.com/v4/letter/m/edb3f5/32.png) [@Mason\_Stevens](https://forum.inductiveautomation.com/u/Mason_Stevens)\ **Post date:** [July 17, 2023, 2:54pm UTC](https://forum.inductiveautomation.com/t/problems-with-dynamic-filtering-of-db-query-value-is-not-set-for-parameter-x/76459/3 "2023-07-17T14:54:04Z")
Something small that I noticed while looking back over the error message: It appears that my 'arguments' clause might be inserted in the 'database' parameter instead of the 'arguments' parameter for the query. The script's syntax is " **system.db.runPrepQuery(query, args, database, tx)**" but the error message seems to show my query (condensed) as "**system.db.runPrepQuery(query, _NULL_, args, _NULL_)**" could this be the issue? is my 'arguments' set somehow empty because it's being assigned to the wrong parameter? If so I'm unsure of the fix. Thanks! ---
**Author:** ![Mason\_Stevens](https://avatars.discourse-cdn.com/v4/letter/m/edb3f5/32.png) [@Mason\_Stevens](https://forum.inductiveautomation.com/u/Mason_Stevens)\ **Post date:** [July 17, 2023, 2:54pm UTC](https://forum.inductiveautomation.com/t/problems-with-dynamic-filtering-of-db-query-value-is-not-set-for-parameter-x/76459/4 "2023-07-17T14:54:24Z")
I'll take a look at it, thank you ---
**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\ **Post date:** [July 17, 2023, 2:55pm UTC](https://forum.inductiveautomation.com/t/problems-with-dynamic-filtering-of-db-query-value-is-not-set-for-parameter-x/76459/5 "2023-07-17T14:55:08Z")
> [@Mason\_Stevens](#): > > _SELECT \* FROM rcp\_cleaninglog WHERE 0 = ? AND Reactor = ? AND Product = ? AND Step = ? AND cID = ? AND Initials = ?_ > [@Mason\_Stevens](#): > > ```auto > arg = event.source.parent.getComponent('ArgTxt').text > > ret = system.db.runPrepQuery(filter,arg) > > ``` It looks to me like _args_ is a single string, not an array. So that is used for the first ? in `0 = ?` and there are no more parameters. The error is that there is no value provided for parameter number 2. Assuming (?) that they are counting parameters starting from 1 that would make sense. Create list of arguments to pass in the second parameter. Or use a named query (with named parameters).