At 11:36 AM 29-06-01 +0200, you wrote:

>Hi Sven,
>
>You are perfectly correct, I do not wish to modify any of the fields in the
>TEAMS table but I do wish to add, edit and remove rows in the SCHEDULE
>table. I'll explain to you what happens when I set the KeyRelation to
>SCHEDULE and the KeyLinks to SCHEDULE.MATCH_NO:
>
>I get the following exception as soon as I try to post the changes made to
>the record:
>
>Multiple rows in singleton fetch
>
>Check KeyLinks and JoinLinks properties
>
>SELECT SCHEDULE.MATCH_NO, SCHEDULE.OPP_TEAM_ID. SCHEDULE.MATCH_DATE,
> SCHEDULE.MATCH_TIME, SCHEDULE.COURT, SCHEDULE.PLAYED,
> TEAMS.TEAM_ID, TEAMS.NAME
>FROM SCHEDULE, TEAMS
>WHERE SCHEDULE.MATCH_NO=? /*BIND_0*/
>FOR UPDATE
>
>I fail to figure out why this exception occurs.

The query that is being submitted to the server here has no join criteria, thus there is no way to get a singleton.

Here was your SQL statement:

SELECT SCHEDULE.MATCH_NO, SCHEDULE.OPP_TEAM_ID,
SCHEDULE.MATCH_DATE,
SCHEDULE.MATCH_TIME, SCHEDULE.COURT, SCHEDULE.PLAYED,
TEAMS.TEAM_ID, TEAMS.NAME
FROM SCHEDULE, TEAMS
WHERE SCHEDULE.OPP_TEAM_ID = TEAMS.TEAM_ID
ORDER BY SCHEDULE.MATCH_NO

This query uses the implicit join syntax so, for this reason, IBO can't parse it correctly if you don't define the joinlinks.

MyDataset.JoinLinks.Clear;
MyDataset.JoinLinks.Add('SCHEDULE.OPP_TEAM_ID=TEAMS.TEAM_ID');

If KeyRelation is SCHEDULE, then your KeyLinks should be SCHEDULE.MATCHNO. The TEAMS_IDs are irrelevant to the uniqueness of this dataset if the join is formed correctly.

If you are only updating columns in SCHEDULE which are present in this dataset, you shouldn't need any custom SQL. Set RequestLive to True and everything should happen as expected.

Two tips:
1. Don't use implicit joins. This would be quite unequivocal to the IBO Parser AND you wouldn't have to care about JoinLinks:

SELECT SCHEDULE.MATCH_NO, SCHEDULE.OPP_TEAM_ID,
SCHEDULE.MATCH_DATE,
SCHEDULE.MATCH_TIME, SCHEDULE.COURT, SCHEDULE.PLAYED,
TEAMS.TEAM_ID, TEAMS.NAME
FROM SCHEDULE
JOIN TEAMS
ON SCHEDULE.OPP_TEAM_ID = TEAMS.TEAM_ID
ORDER BY SCHEDULE.MATCH_NO

2. Constructing the SQL statement as you have done is NOT a good idea. Those SQL sections are for internal use by the parser and get hacked around internally at different times by the parser. Simply throw the whole statement into the SQL property using one or more repeats of MyDataset.SQL.Add('something') and then use SQLWhereItems in the OnPrepareSQL to pass a restricting value into a WHERE clause.

hth
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________