Error Query no rows

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.

  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.
  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:
image

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}

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'.

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.

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.

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

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.

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

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.

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.

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.

3 Likes

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.

2 Likes

Thanks that worked!

1 Like