Subject RE: [IBO] Lookup Refresh (was Lookup problem)
Author Norman Dunbar
Hi Malcolm,

I'm pretty sure that you should be using a query/datasource for the lookup
which is separate from the ione used to provide the editing interface.

I had a similar problem in a change management system I wrote here at work
(running on an Oracle database) in which I used a single query to populate
drop downs and also to select data from tables to be edited. It basically
worked, but, when I changed the current row in an editing form, I eneded up
having a different row showing in my drop downs. It all got so messy that I
binned all the drop downs, and wrote separate queries for each one.

So I had a edit form populated from 'select rowid, loc.* from location order
by location_name' and another form had a drop down list of locations on it,
I was using the same query to populate the drop down, (based on the user
seeing LOCATION_NAME and the column in the record getting the LOCATION_ID -
you know the drill). However, when changing between forms, the data used to
change - which was unacceptable.

To fix it, I left the edit form with its current query and added a new one
for the drop dow - 'select locatio_id, location_name from location order by
location_name' and it all just worked. In addition, the drop down queries
get to have AutoFetchAll set true so you see all the locations in the drop
down. The original query doesn't have the AutoFetchAll set true, so it
doesn't pull everything down from the server.


Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
Tel: 0113 289 6265
Fax: 0113 289 3146

-----Original Message-----
From: Malcolm Smith [mailto:mjfreelancing@...]
Sent: Wednesday, September 04, 2002 1:18 PM
Subject: RE: [IBO] Lookup Refresh (was Lookup problem)

I'm "running before I walk" through necessity, not desire (regrettably). I
have less than a week to finish our product and I thought adding these 3
simple tables would be a piece of cake. Once I sort out this lookup issue I
can move on with the remainder of my development. Since all editing and
lookup occurs on the same form and by the same user I will try again based
on comments made.

Since my previous email used the wrong terms and hence gave the wrong
picture, I'll make one last question:

If my form has a datasource for each query can I use the SAME
query/datasource for the TIB_LookupCombo as the grid that provides the
editing interface for the table used as a lookup.

Boy, I hope I got the words right this time <g>

Thanks for the patience.


-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Wednesday, 4 September 2002 19:10
Subject: RE: [IBO] Lookup Refresh (was Lookup problem)

At 04:34 PM 04-09-02 +1000, you wrote:
>I went through demos 1 -> 3 in the GSG and compared the settings for
>/ PRODUCTTYPE with my SPRITEDETAILS / IPLOOKUP and even designed my SQL
>statements based on the demo. If my SQL statements are wrong then the GSG
>isn't very clear (it appears very clear). I white-boarded the GSG demos
>converted the names just to make sure I had done the same.
>I asked once before whether I needed two datasets (hence transactions)

Dataset <> transaction. A typical task is several datasets being worked on
inside a single transaction. Other (non-synchronous) tasks can be going on
in other transactions.

Also, Post <> Commit.

> and
>never got an answer so I have been playing with both options. On my form I
>have a data entry screen to enter new records into IPLOOKUP and then there
>is another page that has data entry for the GROUPS/SPRITEDETAILS tables.
>Each query has its' own dataset. This bit is fine.

Or not...your problems LOOK like concurrency problems.

>Then I needed to implement a lookup for one of the fields. I made the
>assumption I needed a different query/dataset for the lookup
>(TIB_LookupCombo) so that is why you see two datasets. May be this is the
>point where I went wrong.

Maybe. If the lookup dataset is being edited inside a different
transaction to the one where it is being looked up, then you need separate
datasets; and you will also have to time your commits and refreshes so
that they happen in the right order. If the editing is happening inside
the same transaction, then one dataset will be fine.

>You said:
>This looks wrong:
> KeyDescLinks.Strings = (
>Not according to the GSG.

Yes, good catch on KeyDescLinks....I think you can't put table identifiers
in there at all (for either dataset) because KeyDescLinks refers to output,
not input.

So I think you need to change it to

>If you look at the previos email IP_IPADDRESS is marked as a computer

Yes, I see that in the DFM. Sorry, I was looking at the wrong dataset
when I made that comment.

>I get the feeling my questions are causing some frustration. If this is
>case then I apologise.

It's frustrating when you don't provide enough information. For example,
although you provided an excerpt from the DFM, you have omitted to tell us
anything about your transactions. It's emerging now, after several emails,
that transaction isolation is at the bottom of your problems.

I guess also that, as one who tries to respond when possible, I do get
frustrated with self-confessed newbies who try to run before they can
walk. OK, it's your business how you go about self-teaching...but here you
are trying to do some quite complex stuff at a point when apparently you
don't have transactions clearly in concept yet. And, in my case, I have
had a couple of "all-nighters" in recent days which tends to get me a bit
impatient - even with the birds that tweet in my back yard! :-)

>Don't think I'm not putting in the effort. This is
>all I have done for the last 2 days - not very productive this week.

Sometimes it can be a great saver to get stuck into some lateral research
when the thing you are focused on won't go forward. I urge you to get your
head around transactions, since it is the stuff Firebird and Interbase are
made of - and therefore is crucial to understanding what you are doing with

>Either bare with me or tell me I'm using
>up too much bandwidth and I'll disappear. < it has been a bad week >

You chose a bad time to do the curly stuff - a holiday weekend in the USA
and Europe and also one where the Aussies are up to their ears in work and
several of us here are currently deeply engaged in a demanding exercise
related to the Firebird infrastructure. You couldn't know that, of
course...but we are all volunteers here on this list and we give as much as
we can spare.

>I get the feeling we are going around in circles with my problem. I've
>obviously done something fundamentally wrong.
>I could write another small demo application doing a copy/paste from the
>real project and ask someone to take a closer look at it but does anyone
>have the time......

I don't - but if your project is in Delphi, zip it up with a *small* gbk of
the tables you are using and I'll try to take a look at it.


IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________ - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !

Your use of Yahoo! Groups is subject to

IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________ - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !

Your use of Yahoo! Groups is subject to

This email is intended only for the use of the addressees named above and
may be confidential or legally privileged. If you are not an addressee you
must not read it and must not use any information contained in it, nor copy
it, nor inform any person other than Lynx Financial Systems or the
addressees of its existence or contents. If you have received this email
and are not a named addressee, please delete it and notify the Lynx
Financial Systems IT Department on 0113 2892990.