Parameter linked to datasource problem

[M]

I've got a master/detail relationship setup between 2 TIBDataSet's. SQL is as follows:

Master (qryItem) SelectSQL:
SELECT ITEMID, TITLE, TITLE2, SORT_TITLE, GENREID, RATINGID,
DISTRIBUTORID, VIEW_FORMATID, CREATE_DATE, PRINT_IN_SUPP, PRINT_IN_PLATTERLOG, FILMYEAR, SECTIONID,
RPT_FIELDTOSHOW, INTELLICAPS, LAST_MODIFIED
FROM ITEM
WHERE ITEMID = :ITEMID

Detail (qryActors) SelectSQL:
SELECT ACTORS.DISPLAYNAME AS "Actor", ACTORITEM.ACTORID
FROM ACTORITEM
LEFT JOIN ACTORS ON ACTORITEM.ACTORID = ACTORS.ACTORID
WHERE ACTORITEM.ITEMID = :ITEMID
ORDER BY ACTORS.DISPLAYNAME ASC

Detail (qryActors) DeleteSQL:
DELETE
FROM ACTORITEM
WHERE ACTORID = :ACTORID
AND ITEMID = :ITEMID

I added a TIBSQLMonitor and it shows the following parameters for the preceeding queries when they
are executed:

qryItem/SelectSQL:
ITEMID = 282

qryActors/SelectSQL:
ITEMID = 282

qryActors/DeleteSQL:
ACTORID = 458
ITEMID = 0

qryActors' DataSource property is set to dsItem, who's DataSet property is set to qryItem. As you
can see there's no problem finding the corresponding ITEMID when qryActors is Open()ed, but when I
call Delete() the ITEMID parameter has changed to 0. Even if I try setting the value of the
parameter manually before Delete() it still shows ITEMID as being 0 in the SQLMonitor.

The only code in between qryActors being opened and Delete() being called is:

AnsiString Actor = qryActors->FieldByName("Actor")->AsString;
AnsiString Message = "Are you sure to want to delete \'" + Actor +"\'?";

if (MessageDlg(Message, mtConfirmation, mbOKCancel, 0) == mrOk) {

Does anyone have any idea what could be going wrong with the ITEMID paramter when Delete() is called
for qryActors?

TIA
Mark

[Jeff Overcash (TeamB)]

Post by M
SELECT ITEMID, TITLE, TITLE2, SORT_TITLE, GENREID, RATINGID,
DISTRIBUTORID, VIEW_FORMATID, CREATE_DATE, PRINT_IN_SUPP,
PRINT_IN_PLATTERLOG, FILMYEAR, SECTIONID,
RPT_FIELDTOSHOW, INTELLICAPS, LAST_MODIFIED
FROM ITEM
WHERE ITEMID = :ITEMID
SELECT ACTORS.DISPLAYNAME AS "Actor", ACTORITEM.ACTORID
FROM ACTORITEM
LEFT JOIN ACTORS ON ACTORITEM.ACTORID = ACTORS.ACTORID
WHERE ACTORITEM.ITEMID = :ITEMID
ORDER BY ACTORS.DISPLAYNAME ASC
DELETE
FROM ACTORITEM
WHERE ACTORID = :ACTORID
AND ITEMID = :ITEMID
I added a TIBSQLMonitor and it shows the following parameters for the
ITEMID = 282
ITEMID = 282
ACTORID = 458
ITEMID = 0
qryActors' DataSource property is set to dsItem, who's DataSet property
is set to qryItem. As you can see there's no problem finding the
corresponding ITEMID when qryActors is Open()ed, but when I call
Delete() the ITEMID parameter has changed to 0. Even if I try setting
the value of the parameter manually before Delete() it still shows
ITEMID as being 0 in the SQLMonitor.
AnsiString Actor = qryActors->FieldByName("Actor")->AsString;
AnsiString Message = "Are you sure to want to delete \'" + Actor +"\'?";
if (MessageDlg(Message, mtConfirmation, mbOKCancel, 0) == mrOk) {
Does anyone have any idea what could be going wrong with the ITEMID
paramter when Delete() is called for qryActors?
TIA
Mark

You have to also bring back ItemID in the detail query since you want to use
that FIELD in your delete statement. Parameters in the InsertSQL, ModifySQL,
DeleteSQL and RefreshSQL are always replaced by field values, but you do not
have a field called ItemID.

[M]

That worked. Thank you.

Mark