BorlandTalk.com

Neil Macintyre · Guest

I am trying to develop an ADOQuery that consists of a JOIN on two
tables and can be updated via a Grid. However the query is a LEFT
JOIN and the table to be updated is the RIGHT one.

For example, suppose I have a table called ITEM:

ITEMID ITEM
1 APPLES
2 BANANAS
3 CARROTS

I then have another table called EXAM:

EXAMID REF_ITEMID DATE
1 3 Yesterday
2 2 Today

Thus my Grid would look like:

ITEMID ITEM EXAMID REF_ITEMID DATE
1 APPLES
2 BANANAS 2 2 Today
3 CARROTS 1 3 Yesterday

This query allows me to see all my items and which ones have been
examined. Unfortunately I cannot enter the EXAM details on the grid
via this query.
For example, if I tried to enter a date for the first row (APPLES).

I am using Delphi Professional 6 connecting to an Access 2000 database
via ADO.

Any suggestions?

Ryan · Guest

You would need to slightly re-think your approach. Your Item table
would need a field that stores the reference from the other table
which you can update. With your grid component, use a DB lookup combo
box (or similar) to give you the values from your EXAM table and
return the ID.

Example

Item Table
----------
ItemId, Item, ExamId

Exam Table
----------
EXAMID, REF_ITEMID, DATE

Your grid component should point to your Item table. Your lookup
should point to your Exam table.

Hope that helps

Ryan

[email]bellfieldpark (AT) hotmail (DOT) com[/email] (Neil Macintyre) wrote in message news:<33dd8898.0402170727.77f18785 (AT) posting (DOT) google.com>...

Neil Macintyre · Guest

Ryan,

Thanks for your help. I am not wanting to select my EXAM record since
it will automatically be the one where EXAM.REF_ITEMID = ITEM.ITEMID.

The problem arises with the auto-numbers (ITEM.ITEMID and EXAM.EXAMID)
combined with the LEFT JOIN.

Suppose I have a row where the EXAM details are missing (i.e. LEFT
JOIN but not INNER JOIN). When I enter information into any of the
columns relating to the EXAM, this means a new record must be added to
the EXAM table, the value of EXAM.EXAMID would be automatically
incremented (as auto-numbers are) and the value of EXAM.REF_ITEMID is
set to ITEM.ITEMID automatically in order to preserve the integrity of
the query join.

When I do this in the MS Access database directly, which uses DAO, it
is able to figure this out and there is no problem. However, when I
attempt to do this in Delphi using ADO, it protests because the value
of the primary key for EXAM (i.e. EXAMID) has changed and ADO has not
detected that this EDIT operation is actually an INSERT operation and
a new record should be added to the outer table.

Neil

[email]ryanofford (AT) hotmail (DOT) com[/email] (Ryan) wrote in message news:<7802b79d.0402190035.4089663e (AT) posting (DOT) google.com>...

Neil Macintyre · Guest

I have resolved this by attaching a method to the BeforeEdit stage of
the joined query (instead of trying to recover at the BeforePost
stage).

procedure QryJoin.BeforeEdit;
begin
if QryJoin.FieldByName('REF_ITEMID').AsInteger = 0 then
QryItem.Append;
QryItem.FieldByName('REF_ITEMID') :=
QryJoin.FieldByName('ITEMID');
QryItem.Post;

QryJoin.Requery
end
end;

This is not ideal since it automatically adds a new (and incomplete)
record to QryItem.

Also, it loses the keystroke that I used on the grid to get the Edit
mode