Problems with export of query results with function to XML
Posted: Mon 22 Dec 2008 11:09
TMSQuery have SelectSQL.Text:
SELECT A.AddressID, A.FirmID, A.RegionID, A.CityID, dbo.sf_GetFirmAddress(A.AddressID) FullAddress,
I.ContractNumber, I.ContractDate, I.InvoiceFirmID
FROM FirmAddresses A
LEFT JOIN IntInvoices I ON I.ID=dbo.sf_GetLastInvoiceIDByAddress(A.AddressID)
WHERE A.FirmID=:FirmID
ORDER BY 1
function dbo.sf_GetFirmAddress formats all fields of address into one nvarchar(1000).
In the program this query is used for two lookupcomboboxes, the first listsource TMSQuery, and the second - TVirtualTable. Virtual table is used so:
(qLegalAddress - TMSQuery, vtPostAddress - TVirtualTable)
qLegalAddress.ParamByName('FirmID').AsInteger:=Value;
if NOT DM.OpenTable(qLegalAddress) then
DM.ShowError
else begin
M:=TMemoryStream.Create;
try
qLegalAddress.SaveToXML(M);
M.Seek(0,0);
vtPostAddress.Active:=false;
vtPostAddress.Clear;
vtPostAddress.LoadFromStream(M);
vtPostAddress.Active:=true
finally
M.Free
end;
end;
However loading TVirtualTable (vtPostAddress.LoadFromStream) raises an exception "Field 'ID' must have a value".
After saving MemoryStream into a file and viewing of its maintenance I see that in structure field ID though in query it is not present is really described. What to do?
SELECT A.AddressID, A.FirmID, A.RegionID, A.CityID, dbo.sf_GetFirmAddress(A.AddressID) FullAddress,
I.ContractNumber, I.ContractDate, I.InvoiceFirmID
FROM FirmAddresses A
LEFT JOIN IntInvoices I ON I.ID=dbo.sf_GetLastInvoiceIDByAddress(A.AddressID)
WHERE A.FirmID=:FirmID
ORDER BY 1
function dbo.sf_GetFirmAddress formats all fields of address into one nvarchar(1000).
In the program this query is used for two lookupcomboboxes, the first listsource TMSQuery, and the second - TVirtualTable. Virtual table is used so:
(qLegalAddress - TMSQuery, vtPostAddress - TVirtualTable)
qLegalAddress.ParamByName('FirmID').AsInteger:=Value;
if NOT DM.OpenTable(qLegalAddress) then
DM.ShowError
else begin
M:=TMemoryStream.Create;
try
qLegalAddress.SaveToXML(M);
M.Seek(0,0);
vtPostAddress.Active:=false;
vtPostAddress.Clear;
vtPostAddress.LoadFromStream(M);
vtPostAddress.Active:=true
finally
M.Free
end;
end;
However loading TVirtualTable (vtPostAddress.LoadFromStream) raises an exception "Field 'ID' must have a value".
After saving MemoryStream into a file and viewing of its maintenance I see that in structure field ID though in query it is not present is really described. What to do?