Thank you so much Helen, you have hit the nail right on the head. I finally
got it working properly. Sometimes the IBObjects examples can lead you in a
wrong direction, I have found that many of the examples showed SQL
statements being added to a dataset using the method I first showed instead
of adding it once off through the SQL property. I will from now on also use
properly joined SQL syntax. Thanks for your valuable input.

Best Regards,
Don Schoeman



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
_______________________________________________________



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/