Lookup fields do not return data.

[taazz]

How many fields you have in the peopleQuery with the name fktitleid?

I can see 2 references 1 a fielddef reference of data type ftInteger and 2) a Tfield Object with field kind fklookup. You can't have
1) 2 fields with the same name in any single Query object.
2) a lookup field that has its FieldKeys = Fieldname.
Also the keyfields property must have fields on the dataset that the lookup field belongs to.

From the form definition you have shown it seems that the keyfields property is not connected to an existing data field.
 

[codifies]

theres only on fktitleid field, theres a query field def with the same name but that happens automatically as with the other query (not record) fields

So I'm really not following you ??

[taazz]

I'm sorry my, english fu is not as good as it should be.

You have a field fktitleid, which is of fieldkind fklookup correct?

Which is the keyfield in peopleQuery that fktitleid should update with data?

[codifies]

no worries its better than anyother language of mine that English!

Code: [Select]

    object peopleQueryfktitleid: TLongintField
      DisplayWidth = 10
      FieldKind = fkLookup
      FieldName = 'fktitleidISNTTHISTHEQUERYFIELDNAME??'
      Index = 1
      KeyFields = 'AAA'
      LookupCache = True
      LookupDataSet = titleQuery
      LookupKeyFields = 'BBB'
      LookupResultField = 'CCC'
      ProviderFlags = [pfInUpdate, pfInWhere]
      ReadOnly = False
      Required = False
    end
people table has

peopleid <- AI PK
fktitleid <- foreign key to titles.titleid
name
address
etcetc

titles table

titleid <- AI PK
title <- string to display instead of foreign key way displaying a person record

what values should be in AAA, BBB, CCC

[taazz]

OK then fkTitleID is a physical field (aka exists in database) which you can not define as a lookup field.
You need to change it back to fkData and add a new field as the look up.

[codifies]

ah right! (thats, how its *supposed* to work!!!)

how ever when I try to make the people query active I get

peopleQuery : Field not found : "titleid"

which makes no sense at ALL !

does it mean the peopleQuery is failing because it can't find titleid in the foreign table ???

the titles query IS active and when I run its query I DO get a titleid field, the titleQuery does have a titleid field defined...

[codifies]

Finally I'm back to the none working but not crashing and allowing the query to be active condition of the previous project that I scrapped PHEW!

please see the attachment

this will show a drop down with the correct selection of fields
but without a value in the cells

if I turn cache on I get an exception with a similar error to the one mentioned earlier and the IDE exits....

[taazz]

I have impression that you need to use persistent fields in both queries but I need some time to test it later today.

[codifies]

I have impression that you need to use persistent fields in both queries but I need some time to test it later today.

if by persistent fields you mean that both queries have fielddefs then yes they both do!

[taazz]

I have impression that you need to use persistent fields in both queries but I need some time to test it later today.

if by persistent fields you mean that both queries have fielddefs then yes they both do!

Sorry Chris it was a long night tonight didn't had the time to take a closer look on your form.
Is it possible to send me an sql query to recreate the tables and a few lines of data for testing?
As always my test will be done with firebird but I do not expect any differences between the 2 databases.
I just need to have your fields in those 2 queries so you can just replace the IBConnection with a mysql connection and see how things work for you. Saturday to today I'll have more time to spend on the issue.

[codifies]

I've taken the liberty of attaching the whole project so you can see the the problem, its just a single form with a single grid. (a mysql dump is included)

Please don't misunderstand I'm not demanding you fix my project, rather I have created (and recreated a number of times) this project so I can understand DBGrid better, it is my intention to create a tutorial but without complete understanding of this issue this isn't possible!

I think there are a great number of people find this issue troublesome and would greatly appreciate some kind of basic tutorial ...

really no .xz attachments ??

[taazz]

I'm working on this as I type this. I have created the database and I'm trying to identify the problem.
At first I thought that your problem was the keyfield/listfield/listResultfield combination which was wrong then I realized that they ended up that way after you tried to identify the problem your self.

I am at the point where I see the exception in design time and trying to find why it behaves like this, so hopefully it want be long now.

[codifies]

oh I do hope you don't end up with the same blood stains on the wall as mine....

It would be really nice to get to the bottom of this though and know specifically what causes it and why, so we can work out what to do and just as importantly what NOT to do!

[taazz]

I am almost there (seen blood stains that is) there are a number of really weird checks in all the wrong places inside the Bufdataset. In one hand getting an error is better than silently failing to show data on the other hand it shouldn't fail while connecting the dataset to the database unless there is some kind of network or library error. I am still trying to figure out where this error originates and not being able to step in to sqldb code is a major pain in my back.
Building a debug ide as we speak so that might help to get things going a bit faster.
NEVER MIND that failed miserably I need to re install everything.

[codifies]

Thank for taking the time, I for one certainly appreciate it, I'm determined to make good use of your work to make a really good wiki tutorial so your effort will not be wasted!