Use text field value in a where clause of a select query

Archived Topics
1

I can get the binding to work, the only problem I have is when I try to change the value of the text field and there is an istance where the value is NUL I get an error. What would be a possible solution?

2

Please elaborate, I’m not quite understanding the situation that you have set up.

What value is null, and what error do you get?

Thanks,

3

I have a text field to accept a value I’m looking to see exist in my database, if I try to change the value in the text field and PMI goes to run the query and there is nothing typed into the text field I get Gateway Error 301

4

Ok, a Gateway error 301 means that the gateway recieved an error from the database while trying to run a query. It seems like there is something wrong with your query - the database can’t run it. Please report back with the query that is using the text box’s text property, and the error text that you are getting when you try to run it.

5

select PART, WIDTH AS “DIAMETER”,
LENGTH AS “LENGTH”
from inventory_mst2
WHERE ({Root Container.Text Field.text} >= (length-({Root Container.Dropdown.selectedValue}/1000))
AND {Root Container.Text Field.text} << ??? >>= (length-(20/1000))
AND <= (length+(20/1000)))

For Query: select PART, WIDTH AS “DIAMETER”,
LENGTH AS “LENGTH”
from inventory_mst2
WHERE ( >= (length-(20/1000))
AND <= (length+(20/1000)))

On Component: Feeder Tube Production.Root Container.Table.data

It will work until I change to change the value in the text box, when the box is empty I get the error, is there a way to have a default value of zero in the text box and put a button on the screen that will execute the Select Query.

6

For the code block, use and instead of and .

See my next post for a way to have a “default value” of zero with a dynamic property.

7

  1. Create a dynamic string variable dropdownDefault.

  2. dropdownDefault should be bound to an expression that sets itself to zero when the bound value is blank, or sets itself to the bound value by default.

IF({Root Container.Dropdown.selectedValue}="",
"0",
{Root Container.Dropdown.selectedValue})

note: you can do more data manipulation with the switch expression function.

  1. Now use the new variable, dropdownDefault, inside your SQL query. It will assume its bound value of the dropdown’s selected value or 0.

  2. Repeat as necessary for your Text Fields text property.

I recommend taking the time to name your objects. It will make your life easier as your projects grow.