Error Query no rows

[Stef_Velers]

Hello,
short question, is there a way to disable this error popup?

Exception: Query returned no rows.
For query: "EXEC ProductDefinition.GetMaterialName 0"

I'm okay with that it is not returning any rows at this value but it is a bit frustrating that this error pops up every time you save or change something.

[Transistor]

1. Vision or Perspective?
2. Is this a named query?
3. Is it a binding on a component?
4. Where and when does the error pop up? In Designer? In runtime / browser?
5. Is it run from a script? If so please post the code and format it using the </> button.

[Stef_Velers]

1. Vision
2. SQL query
3. Custom parameter Binding
4. in the designer only when I change something or update the project
5. It is a script run in the SQL Query of the property binding of a text field with the following code:
   EXEC ProductDefinition.GetMaterialName {Root Container.Text Field 1.code}

This is the popup up:

[Transistor]

I don't know the answer, but what happens if you do,

try:
  EXEC ProductDefinition.GetMaterialName {Root Container.Text Field 1.code}
except:
  None

Or, if the problem is because {Root Container.Text Field 1.code} is empty then,

if {Root Container.Text Field 1.code}:
  EXEC ProductDefinition.GetMaterialName {Root Container.Text Field 1.code}

[Stef_Velers]

Okay, but maybe we can figure this out together if you agree.
Both solutions didn't work but with the if statement I got this error:

Exception: Error running query:
SQLQuery(query=if (0):
  EXEC ProductDefinition.GetMaterialName 0, database=OptiMes_roosendaal)@0ms
On: 4_1_0_lijn4_Productielijn.Root Container.Text Field 1.ProductName
	caused by GatewayException: Incorrect syntax near ':'.
	caused by SQLServerException: Incorrect syntax near ':'.

Ignition v8.1.33 (b2023101913)
Java: Azul Systems, Inc. 17.0.8

It sees the parameter instantly as a number so maybe the problem lies there. Cause I think you mean that when the parameter is a number and not just 0 then it has to do the stored procedure?

for the first error, it has something to do with "except"

Exception: Error running query:
SQLQuery(query=try:
  EXEC ProductDefinition.GetMaterialName 0
except:
  None, database=OptiMes_roosendaal)@0ms
On: 4_1_0_lijn4_Productielijn.Root Container.Text Field 1.ProductName
	caused by GatewayException: Incorrect syntax near the keyword 'except'.
	caused by SQLServerException: Incorrect syntax near the keyword 'except'.

[Stef_Velers]

Thanks for your first answer. I appreciate the time and effort you guys put into people. But if you think it is not something you think you can solve then it's fine. I can try it myself or it is what it is.

[bkarabinchak.psi]

Are you trying to do try/excpet in a sql query binding? Don't do that that won't work. That is why you are getting caused by GatewayException: Incorrect syntax near the keyword 'except'.

The original error from before the try and except -

	caused by GatewayException: Incorrect syntax near ':'.
	caused by SQLServerException: Incorrect syntax near ':'.

Can you run EXEC ProductDefinition.GetMaterialName 0 in your DB workbench or the Database Query Browser? Seems like the issue may lie inside the stored procedure itself.

[pascal.fragnoud]

Show us exactly what you're doing, because so far your description is pretty vague:

@Stef_Velers

It is a script run in the SQL Query of the property binding of a text field with the following code:
EXEC ProductDefinition.GetMaterialName {Root Container.Text Field 1.code}

That doesn't make much sense. Is it a property binding ? Is it a SQL binding ? Is it a script binding ?

There are several ways of handling lack of data, but to figure out the best/right one, we'll need to know what expects data, where it should come from, and what should happen if there is none.

@bkarabinchak.psi

The original error from before the try and except

I think he tried to use a python if statement in the query itself, which is why he got that error.

[bkarabinchak.psi]

He did say It is a script run in the SQL Query of the property binding of a text field with the following code so I assumed it was a SQL Query binding.

It's still not exactly clear to me though.

[Stef_Velers]

This is what I made:

But when the parameter passes 0 I get the following error:

Exception: Query returned no rows.
For query: "EXEC ProductDefinition.GetMaterialName 0
"
On Component: 4_1_0_lijn4_Productielijn.Root Container.Text Field 1.ProductName

It is not a problem to me that it is not returning anything when the value is 0 because that is exactly what I want. But I don't want this error to show up every time I update my project in the designer. So is there a solution to disable this error or at least that it is not giving me this error.

[bkarabinchak.psi]

Ah ok. You can enable the "Fallback Value" there and put something in there - that is what will be returned in the event of no rows returned.

[pturmel]

Bindings are expected to return a value. If a query might not return a value, you are expected to define the fallback value to return instead.

You have to return something to deliver to the bound property.

[Stef_Velers]

Thanks that worked!