Master/detail issue

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
ccmcbride
Posts: 101
Joined: Tue 01 May 2007 16:36

Master/detail issue

Post by ccmcbride » Tue 22 Jul 2008 19:13

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
Posts: 2098
Joined: Tue 14 Feb 2006 10:14

Post by Antaeus » Wed 23 Jul 2008 07:52

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?
Last edited by Antaeus on Thu 24 Jul 2008 06:23, edited 1 time in total.

ccmcbride
Posts: 101
Joined: Tue 01 May 2007 16:36

Post by ccmcbride » Wed 23 Jul 2008 15:33

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
Posts: 101
Joined: Tue 01 May 2007 16:36

Post by ccmcbride » Thu 24 Jul 2008 05:00

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
Posts: 2098
Joined: Tue 14 Feb 2006 10:14

Post by Antaeus » Thu 31 Jul 2008 06:34

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
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Mon 04 Aug 2008 09:44

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
Posts: 101
Joined: Tue 01 May 2007 16:36

Post by ccmcbride » Fri 29 Aug 2008 00:57

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

Post Reply