Master/detail issue

[ccmcbride]

I have set a master detail between two datasets.
I have this code to set the linking field of the master to the linked field of the detail :
DidSearch := dlgSearch.ShowModal = mrOK;
if didSearch then
begin
dsDetail.DataSet.FieldByName('VendorUID').AsString := dlgSearch.FoundUID;
dmLookup.tblVendors.Refresh;
The value is returned properly.
On the refresh, dbmonitor does this (from the log):
C:\Bin62\sme5.exe
7/22/2008 11:33:08 AM 0:0.0 SQL Execute: SELECT * FROM Vendor
WHERE UID = :VendorUID
:VendorUID(String[0],IN)='' Complete

as you can see, the input value is null.

The dsdetail.dataset.FieldByName('VendorUID') IS correctly set, the tables are all enabled, there is no filter or filterSQL on the vendor table (which is the linked detail), and I am not getting any data returned.

Am I missing something?

Delphi 7
Microsoft SQL Server: 09.00.3068
Microsoft SQL Native Client: 9.00.3042.00
sdac 4.50.0.36

[Antaeus]

The explanation is not clear for me. Please specify what role play dmLookup.tblVendors and dsDetail.DataSet datasets. In what way have you performed the master/detail link? What role plays the VendorUID field in dsDetail.DataSet?

[ccmcbride]

dsDetail is (for in this case) a material item added to an order.
The screen is used to select an inventory item, which initializes the material record. Part of the material record is the vendor, which the user can change from the vendor table (because they may have to order the item from a different vendor than the one in inventory).

So, the VendorUID is the link between the mat record and the vendor table.
The master/detail source is set in the usual way, and it works when opening/viewing the screen, and it works when the record is first created, but it runs that query (with the empty parameter) when I change it from a search screen, even though the VendorUID on the material record has changed.

I 'fixed' it by removing the master detail link and adding a filterSQL when they change the vendor, (because the vendor name, from the dmLookup.tblVendor table needs to be shown on the screen) but that really isn't a good solution.

[ccmcbride]

Another issue

Master is a table.
Detail is a union query :
select a.RecUID, a.UID, a.ItemUID, a.LinkType, a.ItemName, a.ItemQty, a.ItemDesc, '' as checkList, a.EditTimeStamp
from InvKits a
where (a.LinkTypeCode = 0)

union all
select a.RecUID, a.UID, a.ItemUID, a.LinkType, a.ItemName, a.ItemQty, a.ItemDesc, b.CheckList, a.EditTimeStamp
from InvKits a, inventory b
where a.LinkTypeCode = 1 and b.UID = a.LinkUID

union all
select a.RecUID, a.UID, a.ItemUID, a.LinkType, a.ItemName, a.ItemQty, a.ItemDesc, b.CheckList, a.EditTimeStamp
from InvKits a, ServiceItem b
where a.LinkTypeCode = 2 and b.UID = a.LinkUID
order by ItemName


I am setting the master/detail link on the itemUID.
The query shown in dbmonitor is :
select a.RecUID, a.UID, a.ItemUID, a.LinkType, a.ItemName, a.ItemQty, a.ItemDesc, '' as checkList, a.EditTimeStamp
from InvKits a
where (a.LinkTypeCode = 0)

union all
select a.RecUID, a.UID, a.ItemUID, a.LinkType, a.ItemName, a.ItemQty, a.ItemDesc, b.CheckList, a.EditTimeStamp
from InvKits a, inventory b
where a.LinkTypeCode = 1 and b.UID = a.LinkUID

union all
select a.RecUID, a.UID, a.ItemUID, a.LinkType, a.ItemName, a.ItemQty, a.ItemDesc, b.CheckList, a.EditTimeStamp
from InvKits a, ServiceItem b
where (a.LinkTypeCode = 2 and b.UID = a.LinkUID) AND ItemUID = :ItemUID
order by ItemName

with the result that the records being returned is incorrect.

[Antaeus]

I cannot reproduce the first problem. Please, send us a complete small test sample to sdac*devart*com that demonstrates the problem.
As to the second problem, we will investigate the possibility to fix it. As soon as we have results, we will let you know.

[Dimon]

In order to solve the problem with using a union query in detail table you can use local filtering for establishing master-detail relationship. For this you should set the TMSQuery.Options.LocalMasterDetail property to True. You can find more detailed information about this property in the MSDAC help.

If you don't want to use local filtering, please modify your query like the following:

Code: Select all

select * from (

select a.RecUID, a.UID, a.ItemUID, a.LinkType, a.ItemName, a.ItemQty, a.ItemDesc, '' as checkList, a.EditTimeStamp
from InvKits a
where (a.LinkTypeCode = 0)

union all 
select a.RecUID, a.UID, a.ItemUID, a.LinkType, a.ItemName, a.ItemQty, a.ItemDesc, b.CheckList, a.EditTimeStamp
from InvKits a, inventory b
where a.LinkTypeCode = 1 and b.UID = a.LinkUID

union all
select a.RecUID, a.UID, a.ItemUID, a.LinkType, a.ItemName, a.ItemQty, a.ItemDesc, b.CheckList, a.EditTimeStamp
from InvKits a, ServiceItem b
where a.LinkTypeCode = 2 and b.UID = a.LinkUID

) as t
order by ItemName

[ccmcbride]

I cannot use local master/detail.
I get 'row handle' error all over the place when I attempt to do that.