I'm not sure if the problem is ADO or the VCL, but here it is...
If you have the following SQL in ADODataset
Quote
>SELECT TLSCaseApplication.*, 0 AS RadioGroup, NULL
> AS TLSCaseTypeID
>FROM TLSCaseApplication
>WHERE TLSCaseApplicationID NOT IN
> (SELECT TLSCaseApplication.TLSCaseApplicationID
> FROM TLSCaseApplication INNER JOIN
> TLSCaseTypeLApplication ON
> TLSCaseApplication.TLSCaseApplicationID = TLSCaseTypeLApplication.TLSCaseApplicationID
> WHERE TLSCaseTypeID = :TLSCaseTypeID)
>UNION
>SELECT TLSCaseApplication.*, RadioGroup, TLSCaseTypeID
>FROM TLSCaseApplication INNER JOIN
> TLSCaseTypeLApplication ON
> TLSCaseApplication.TLSCaseApplicationID = TLSCaseTypeLApplication.TLSCaseApplicationID
>WHERE TLSCaseTypeID IS NULL OR
> TLSCaseTypeID = :TLSCaseTypeID
The first thing that happens is the parameters get screwed, I guess
because ADO or VCL cannot work out the types correctly from the
database?.. OK so I change them manually from the OI using the
parameters editor to ...
Quote
> Parameters = <
> item
> Name = 'TLSCaseTypeID'
> DataType = ftString
> Size = 16
> Value = Null
> end
> item
> Name = 'TLSCaseTypeID'
> DataType = ftString
> Size = 16
> Value = Null
> end>
Then the IDE changes the first parameter value to
Quote
> Parameters = <
> item
> Name = 'TLSCaseTypeID'
> DataType = ftString
> Size = 16
> Value = 'a '
> end
Sometimes it changes it to ftFixedstring... there seems to be a
problem with the VCL going back to the ADO and changing the values..
even when you don't want it to?
So I edit it as source, then view as form... it changes it back!
The value is comming from the database, as its the first key in the
master dataset, which I can understand as this is the way it can show
records while designing forms...
Then I try to set up a Master to this ADO "Detail"...
First I can't have 1 field name from the master to the duplicate field
names in the detail...
So I set the master IndexFieldNames to "TLSCaseTypeID;TLSCaseTypeID"..
Again when the detail query is fired at the server the resultant SQL
is:
Quote
>sp_executesql N'SELECT TLSCaseApplication.*, 0 AS RadioGroup, NULL
> AS TLSCaseTypeID
>FROM TLSCaseApplication
>WHERE TLSCaseApplicationID NOT IN
> (SELECT TLSCaseApplication.TLSCaseApplicationID
> FROM TLSCaseApplication INNER JOIN
> TLSCaseTypeLApplication ON
> TLSCaseApplication.TLSCaseApplicationID = TLSCaseTypeLApplication.TLSCaseApplicationID
> WHERE TLSCaseTypeID = @P1)
>UNION
>SELECT TLSCaseApplication.*, RadioGroup, TLSCaseTypeID
>FROM TLSCaseApplication INNER JOIN
> TLSCaseTypeLApplication ON
> TLSCaseApplication.TLSCaseApplicationID = TLSCaseTypeLApplication.TLSCaseApplicationID
>WHERE TLSCaseTypeID IS NULL OR
> TLSCaseTypeID = @P2', N'@P1 varchar(16),@P2 varchar(16)', 'a ', Null
I'm guessing that the update of the parameter in the detail dataset is
being done by name, finds the first name, sets the value, then doesn't
bother with the next?
If it's not ADO then it would be useful if there was a flag/additional
property that would work by sequence...
eg:
MasterFields[Afield;Afield;Afield2]
MasterDetailBySequence := True;
DetailKeyFields[AdiffField;AdiffField;Afield2]
The detail key fields being the parameters, not fields.
Is there anyway in the detail datasets TDataModule, or the Master
datasets TDataModule to hardwire the parameter settings by index... so
that when the masterdataset changes it changes the details values
correctly?
I cannot find an event that fires before the SQL of the detail's
dataset gets sent... all the OnBefore's etc are based on the dataset,
not the SQL being sent prior to the dataset being filled by the return
data. In fact it seems as though when you have a master/detail setup
then the OnXXX are not begin fired at all? (but I've not looked into
this in detail, so may be just getting frustrated!)
Is there perhaps an un-exposed event such as...
OnMasterChangedDetailAboutToBeChanged, into which I could hook some
manual code?
The problem is made even more frustrating as there doesn't seem any
way to set parameters unless they have the same field names as the
dataset...
You get a circular problem, the fields are not found in the master
dataset, so you change the name(s) to that of the master and then the
details are not found.... Arggg...
Is there no way to have parameters with different names to the fields
and have master/detail processing?
If I can't resolve this, then I'm going to have to make the screen
controls change the detail (now unlinked from the master) manually, in
god knows how many places...
Any help would be much appreciated
Jon
--
Integrated 400 Solutions Ltd.
Jonathan Wilson, AS/400 consultant/director
24 Hours: 07775 638904
