BDE says "Field not found" but field exists

Question

I have the following query to one of my database tables:

select count(*) as mycount
  from mytable
 where fieldone = :fieldone
   and fieldtwo = :fieldtwo

Parameters are correctly loaded into the query (both of type String).

When I run this query outside the app (for instance, through the dbexplore) and replace the parameters with the actual values, I get the correct result. But when running it in the app, I get a Field 'fieldtwo' not found error, right on the Query.Open call.

Why would the BDE not find this field, when it actually exist?

Update: The following query, executed right after the first one (the one that fails), works fine in the app:

select *
  from mytable
 where fieldone = :fieldone
 order by fieldone, fieldtwo

Solution 1

Clear the query content using Query1.SQL.Clear; statement before opening it.

Other reason can be you are opening other database which may not have the specified field. Be sure that both the DatabaseName's in your app and dbexplore are same

Solution 2

The best guess is that you have populated the field list in the query, this overrides any concept of the underlying fields that are in the query and is a cause of countless confusion.

Right click on the query, pick the fields editor clear all the values that are there and then choose 'add all fields' that should cause the missing field to appear once the query is executed.

I think it should auto-populate the fields if there are no defined fields when the query is executed, so you may not need to choose 'add all fields' after clearing the fields.

Solution 3

Whenever we come across a problem like this we tend to remove the query from the form and create it dynamically at run time... It depends how ingrained into the form it is...

E.g. If you have a data aware control looking at "fieldtwo" which tries to fetch some data when the underlying data set gets updated then it'll trigger an error like this, but it's more obvious when you've written code such

SomeEdit.Text = Query.FieldByName("fieldtwo").AsString;

That way it falls over on the relevant line instead of the open (triggering a related event)

Comments

[Robert Love]

You changed the actual SQL, did you by chance remove the order by on the first SQL statement? As the fieldone & fieldtwo don't exist in the resulting statement and that would cause the problem.

[Pablo Venturino]

Mmm... nah, I didn't remove an order by clause from the first statement. I just changed the names of fields and tables to depict a generic scenario. Besides, there's no use for an order by clause in a statement that returns a single aggregate function.

[kludg]

There is no problem in the "generic scenario". SQL statement is executed OK. Problem is somewhere else, but the question does not give me a cue where to find it.

[Pablo Venturino]

Double checked: I'm already closing the query before executing this statement. About the database, I'm sure it´s the same one. The next query I execute on my app (which queries on the table ordering by those two columns) works fine.

[Pablo Venturino]

Checked that: by the time I'm calling Query.Call, Query.Fields is empty. Still, I couldn't find an "Add all fields" option on the query or the field list, through I'm not working GUI here.

[Bharat]

Although you closed the query, it wont clear the sql statement. So try clearing the query with clear statement after closing the query

[Pablo Venturino]

Checked that: while debugging, Query.SQL.Text gives me the exact query I'm trying to execute. I tried adding a Query.SQL.Clear before constructing my query, but I still got the same error.

[Pablo Venturino]

Looked like something worth trying. But I still got the error when executing the app alone. I also tried not using parameters, but the result didn't change. :( Thanks for the tips anyway.

[Pablo Venturino]

Well, I'm not using GUI here, so the Query is created at runtime. SQL statement is also created at runtime (it's dynamic for different tables), but I don't get an error when setting the parameters.

[Pablo Venturino]

Just tried with the parenthesis, but got the same error. Double quotes would as you say make it worse, since it turns parameters into literals. Not using parameters didn't work either. Thanks anyway, it was worth to try those.

[Pablo Venturino]

After many tests, I decided the error is on my app. I found a workaround, so I'm not testing anymore. I'll accept your answer since you were the first to answer and did follow up on the question. Thanks!

[RobertFrank]

What do you mean by "Not using parameters didn't work either." Did actual remove the where-statement, or change the :fieldone to fieldnames, or ??? What was the result?

[truthseeker]

I have similar experience. Populating queries runtime helps in this case, its not neccessary to allocate them dynamically. It seems like calling some stored procedures rewrites unrelated objects in memory, in this case field list of some TQuery object.

[1010]

@PabloVenturino I'm having a similar problem, what was the workaround you've found?