Recent

Author Topic: Master Detail Single button for commit  (Read 4435 times)

anis2505

  • Full Member
  • ***
  • Posts: 201
Master Detail Single button for commit
« on: November 26, 2015, 07:15:56 pm »
Hi all,

Simply I need to know how is it possible to commit all changes with single button click.

I have two SQLQuery components. and a 1 SQLTransaction. So I want to click a button and all data will be commited to database.

Sorry to bother. I don't have so many time to dig the forum.

regads
GH.Anis
Dear Confucius you said {A picture is worth a thousand words}
I say {a good example is worth a thousand words}

LacaK

  • Hero Member
  • *****
  • Posts: 677
Re: Master Detail Single button for commit
« Reply #1 on: November 26, 2015, 08:11:58 pm »
SQLTransaction.Commit or SQLTransaction.CommitRetaining

anis2505

  • Full Member
  • ***
  • Posts: 201
Re: Master Detail Single button for commit
« Reply #2 on: November 26, 2015, 09:53:27 pm »
SQLTransaction.Commit or SQLTransaction.CommitRetaining

Hi thanks :)
My problem how to commit all SQLQuery component's updates.

Code: [Select]

//Master SQLQuery component
SQLMaster.ApplyUpdates;

// Detail SQLQuery component
SQLDetail.ApplyUpdates;

//Commit Updates
SQLTrasaction.Commit;


The problem only the master's updates gets commited, the detail's updates gets are ignored
Dear Confucius you said {A picture is worth a thousand words}
I say {a good example is worth a thousand words}

LacaK

  • Hero Member
  • *****
  • Posts: 677
Re: Master Detail Single button for commit
« Reply #3 on: November 27, 2015, 07:34:17 am »
It should work.
Do you have on database side defined any referential integrity constraints ?
When you in same transaction close and open datasets data are there ?
(if not it means that they are not written to database...so it is primary not problem of commiting transaction, but applying updates)

kapibara

  • Hero Member
  • *****
  • Posts: 574
Re: Master Detail Single button for commit
« Reply #4 on: November 27, 2015, 09:46:28 am »
Are you posting the detail changes before calling ApplyUpdates?
Lazarus trunk / fpc 3.2.2 / Kubuntu 21.10 - 64 bit

anis2505

  • Full Member
  • ***
  • Posts: 201
Re: Master Detail Single button for commit
« Reply #5 on: November 27, 2015, 12:18:16 pm »
Are you posting the detail changes before calling ApplyUpdates?

I don't understand what do you mean exactly. do you mean calling the Post method from th SQLDetail component.
I'm using DBNav to handle the posting
Dear Confucius you said {A picture is worth a thousand words}
I say {a good example is worth a thousand words}

rvk

  • Hero Member
  • *****
  • Posts: 5016
Re: Master Detail Single button for commit
« Reply #6 on: November 27, 2015, 01:07:59 pm »
How are SQLMaster and SQLDetail connected?
If you insert a new record in SQLMaster you need to get a unique ID for that record. You need to use that unique ID and put it in the foreign key of SQLDetail. Otherwise, if you don't assign the foreign key in SQLDetail, it gets saved as NULL and you can only reach the detail records if you do a complete SELECT (but the connection with the SQLMaster is lost).

If you do a SELECT * on your SQLDetail (in a SQL manager without the master table) do you see your posted records from before?

anis2505

  • Full Member
  • ***
  • Posts: 201
Re: Master Detail Single button for commit
« Reply #7 on: November 27, 2015, 01:34:30 pm »
Hi  :),

Here is a working example.

 :-[ What I need to achieve is to use one button to post all data from the master and the detail

regards
GH.Anis
Dear Confucius you said {A picture is worth a thousand words}
I say {a good example is worth a thousand words}

rvk

  • Hero Member
  • *****
  • Posts: 5016
Re: Master Detail Single button for commit
« Reply #8 on: November 27, 2015, 01:56:40 pm »
:-[ What I need to achieve is to use one button to post all data from the master and the detail
The problem is that you can only post detail records if the master is already posted. You now have a CUSTOMER_ID. Do you assign that manually in your master-table?

You now have this:
Code: Pascal  [Select][+][-]
  1. procedure TDataCenter.SQLArticleAfterInsert(DataSet: TDataSet);
  2. begin
  3.   DataSet.FieldByName('CUSTOMER_ID').Value := SQLCustomer.FieldByName('CUSTOMER_ID').Value;
  4. end;
I see now that you haven't even connected the SQLArticleAfterInsert to any SQLArticle dataset. So it doesn't even work.
You don't have ANY of those event connected.

If you connect the events you can create a new button with applyupdates and commit and it should work.

When entering a new customer a post needs to be done before inserting a new article (otherwise the detailrecord can't see the new master). In the DBGrid this is done automatically if you select another row or with the green check-sign.

chtilux

  • Newbie
  • Posts: 3
Re: Master Detail Single button for commit
« Reply #9 on: September 20, 2022, 09:27:11 am »
Hi,

I've got the same problem.
Database : Firebird 4.0
Components : TSQLConnector, TSQLTransaction, TSQLQuery.
Master Query and DetailQuery are linked by Datasource.
Displaying records id ok.
But when I insert more detail records for the same master record, only one is there when I reopen the form.
The same if I insert a new master record then a new detail (first master.post of course), the master is inserted into the database but the detail not.

Thanks.

Code: Pascal  [Select][+][-]
  1. procedure TProductW.SalesQueryNewRecord(DataSet: TDataSet);
  2. begin
  3.   Dataset.FieldByName('serprd').Value:=Query.FieldByName('serprd').AsInteger;
  4.   Dataset.FieldByName('dateff').Value:= Validity.Date;
  5.   Dataset.FieldByName('ptype').Value:='S';
  6.   Dataset.FieldByName('qtymin').Value:=1;
  7. end;
  8.  
  9. procedure TProductW.saveContent;
  10. begin
  11.   try
  12.     if DataObject.Transaction.Active then
  13.     begin
  14.       Query.ApplyUpdates;
  15.       SalesQuery.ApplyUpdates;
  16.       DataObject.Transaction.Commit;
  17.     end;
  18.   except
  19.     on E:EDatabaseError do
  20.     begin
  21.       DataObject.Transaction.Rollback;
  22.       MessageDlg(E.Message, mtError, [mbOk], 0);
  23.     end;
  24.   end;
  25. end;  
  26. [code=pascal]
« Last Edit: September 20, 2022, 09:40:12 am by chtilux »

rvk

  • Hero Member
  • *****
  • Posts: 5016
Re: Master Detail Single button for commit
« Reply #10 on: September 20, 2022, 09:42:52 am »
But when I insert more detail records for the same master record, only one is there when I reopen the form.
You need to show more of the relevant code.

If you only call TProductW.saveContent once, then yes, you'll only get one record.

You need to post and applyupdates for every subrecord you want to save.

chtilux

  • Newbie
  • Posts: 3
Re: Master Detail Single button for commit
« Reply #11 on: September 20, 2022, 10:32:32 am »
@rvk
Thank you for the answer, it helps me ;-) I thought ApplyUpdates would apply all updatesPrending of the dataset to the database.

Quote
If you only call TProductW.saveContent once, then yes, you'll only get one record.

You need to post and applyupdates for every subrecord you want to save.

Zvoni

  • Hero Member
  • *****
  • Posts: 1523
Re: Master Detail Single button for commit
« Reply #12 on: September 20, 2022, 12:21:00 pm »
…… and people laugh at me, when i say i don‘t bother with that, but do everything via SQL-Statements……
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018