Sequence Field Problem with Midas

[anucha]

I'm write 3 tier application by use Midas with delphi 5
I have 2 tables PO_Header and PO_Details
PO_Header has primary key is PO_NO fields it's sequence Filed that update by before insert trigger on database server

When I insert data to PO_Header and get PO_NO for insert to PO_Details
PO_Header Client Dataset can't get PO_NO Value
How do i solve this problem

 

[Marco Kalter]

I'm not sure if this can be resolved. If I understand correctly, the primary key column gets a value in a before insert trigger on the server. In other words, the client application cannot identify the record. Normally you would use a returning clause to obtain the primary key value, but midas does not generate that.

------------------
Marco Kalter
Allround Automations

 

[anucha]

I use dummy value for PO_NO and set inUpdate property of this field to False
because PO_NO will generate by before insert trigger on db server, After i insert on PO_Header i will use PO_NO from PO_Header to insert in PO_LinesItem But i can't get PO_NO value after i post PO_Header.

My Code is List in Bellow

function TcoGeneralPurchase.GeneratePO(const POIssueUser: WideString;
PRSLine_No: OleVariant): WideString;
var dToday: TDateTime;
i, intPONo,intPOLineNo: Integer;
BookMark: TBookMark;
begin

intPONo:=0;
dToday:=Date;
try
cdsPRSForGenPO.Close;
cdsPRSForGenPO.Open;

cdsPRSLinesItemForUpdate.Close;
cdsPRSLinesItemForUpdate.Open;

cdsPOHeader.Close;
cdsPOHeader.Open;

cdsPOLinesItem.Close;
cdsPOLinesItem.Open;

for i := 0 to VarArrayHighBound(PRSLine_No,1) do
begin
if cdsPRSForGenPO.Locate('LINE_NO',PRSLine_No,[]) then
begin
if not cdsPOHeader.Locate('Vendor_Code;Gen_PO_Date;Requster',
VarArrayOf([cdsPRSForGenPO.FieldByName('Vendor_Code').AsString,
dToday,
cdsPRSForGenPO.FieldByName('Reqester').AsString]),[]) then
begin
intPONo:=intPONo+1;

try
cdsPOHeader.Insert;

cdsPOHeader.FieldByName('PO_NO').AsFloat
:=intPONo;
cdsPOHeader.FieldByName('Vendor_Code').AsString
:=cdsPRSForGenPO.FieldByName('Vendor_Code').AsString;
cdsPOHeader.FieldByName('Gen_PO_Date').AsDateTime
:=dToday;
cdsPOHeader.FieldByName('Generate_PO_By').AsString
:=POIssueUser;
cdsPOHeader.FieldByName('Requster').AsString
:=cdsPRSForGenPO.FieldByName('Reqester').AsString;

cdsPOHeader.FieldByName('Currency_Code').AsString
:=cdsPRSForGenPO.FieldByName('Currency_code').AsString;

cdsPOHeader.FieldByName('Amount').AsFloat
:=cdsPOHeader.FieldByName('Amount').AsFloat
+cdsPRSForGenPO.FieldByName('Amount').AsFloat;
cdsPOHeader.Post;
BookMark:=cdsPOHeader.GetBookmark;
cdsPOHeader.ApplyUpdates(0);
cdsPOHeader.Refresh;
cdsPOHeader.GotoBookmark(BookMark);

cdsPOHeader.FreeBookmark(BookMark);
except
raise;
end;//try
end;//if

if cdsPOHeader.Locate('Vendor_Code;Gen_PO_Date;Requster',
VarArrayOf([cdsPRSForGenPO.FieldByName('Vendor_Code').AsString,
dToday,
cdsPRSForGenPO.FieldByName('Reqester').AsString]),[]) then

begin
try
intPONo:=cdsPOHeader.FieldByName('PO_No').AsInteger;
cdsPOLinesItem.Insert;
cdsPOLinesItem.FieldByName('Line_No').AsInteger:=i;
cdsPOLinesItem.FieldByName('PO_No').AsInteger
:=cdsPOHeader.FieldByName('PO_NO').AsInteger;
cdsPOLinesItem.FieldByName('Part_No').AsString
:=cdsPRSForGenPO.FieldByName('Part_No').AsString;
cdsPOLinesItem.FieldByName('Description').AsString
:=cdsPRSForGenPO.FieldByName('Description').AsString;
cdsPOLinesItem.FieldByName('Remain_Quantity').AsInteger
:=cdsPRSForGenPO.FieldByName('Quantity').AsInteger;
cdsPOLinesItem.FieldByName('Quantity').AsInteger
:=cdsPRSForGenPO.FieldByName('Quantity').AsInteger;
cdsPOLinesItem.FieldByName('Unit_Code').AsString
:=cdsPRSForGenPO.FieldByName('Unit_Code').AsString;
cdsPOLinesItem.FieldByName('Price_Per_Unit').AsFloat
:=cdsPRSForGenPO.FieldByName('Price_Per_Unit').AsFloat;
cdsPOLinesItem.FieldByName('Amount').AsFloat
:=cdsPRSForGenPO.FieldByName('Amount').AsFloat;
cdsPOLinesItem.Post;

BookMark:=cdsPOLinesItem.GetBookmark;
cdsPOLinesItem.ApplyUpdates(0);
cdsPOLinesItem.Refresh;
cdsPOLinesItem.GotoBookmark(BookMark);

intPOLineNO := cdsPOLinesItem.FieldByName('LINE_NO').AsInteger;

cdsPOLinesItem.FreeBookmark(BookMark);

except
raise;
end;//try
end;//if cdsPOHeader Locate

// Update PO_Status in PRS_Lines_item Table

if cdsPRSLinesItemForUpdate.Locate('LINE_NO',cdsPRSForGenPO.FieldByName('LINE_NO').AsInteger,[]) then
begin
try
cdsPRSLinesItemForUpdate.Edit;
cdsPRSLinesItemForUpdate.FieldByName('PRS_STATUS').AsString:='1';
cdsPRSLinesItemForUpdate.FieldByName('PO_NO').AsInteger:=intPONo;
cdsPRSLinesItemForUpdate.FieldByName('PO_LINE_NO').AsInteger:=intPOLineNO;//cdsPOLinesItem.FieldByName('PO_LINE_NO').AsInteger;
cdsPRSLinesItemForUpdate.Post;
cdsPRSLinesItemForUpdate.ApplyUpdates(0);
cdsPRSLinesItemForUpdate.Refresh;
except
raise;
end;//try
end;//if

end;//if Locate cdsPRSForGenPO
end;//for
result:='Success';
except
result:='Fail';
end;//try
end;

 

[anucha]

Please answer my question

 

[Marco Kalter]

Like I said, I'm not sure if this can be resolved if you post a record through a TClientDataSet and obtain a value for the primary key in a before insert trigger on the database server.

------------------
Marco Kalter
Allround Automations

 

[jimpoe]

Originally posted by anucha:
Please answer my question

You would probably find your answer on borland.public.datasnap.

You could get the generated value by querying the database in the AfterUpdateRecord event and setting poPropagate changes to True on the DataSetProvider