Help needed with Exception (1) 1

[delphiman]

I am using Delphi 6 Enterprise and Interbase 6 and have a working model of a 3-Tier system.

Can anyone please tell me why I get the exception at point "A" in the following procedure?

procedure TParentDM.IBFatherDataSetAfterScroll(DataSet: TDataSet);
begin
with ParentDM.IBFatherDataSet do
begin
ParentDM.IBdsTrnsfr.Active := True;
if ParentDM.IBdsTrnsfr.state in dsEditModes then
else
ParentDM.IBdsTrnsfr.edit;
ParentDM.IBdsTrnsfrP_NO.value := ParentDM.IBFatherDataSetP_NO.value;

ParentDM.IBdsTrnsfr.ModifySQL.Clear;
ParentDM.IBdsTrnsfr.ModifySQL.Add('UpDate');
ParentDM.IBdsTrnsfr.ModifySQL.Add('TRNSFR');
ParentDM.IBdsTrnsfr.ModifySQL.Add('set');
ParentDM.IBdsTrnsfr.ModifySQL.Add('P_NO = _NO');

ParentDM.IBdsTrnsfr.ApplyUpdates;

'Cannot Update. (No UpDate Query)'......................A

Thanks in advance.

Terry

 

[Svanhooft]

hi,

You donn't execute the query.

ParentDM.IBdsTrnsfr.ApplyUpdates;

should read

ParentDM.IBdsTrnsfr.ExecSQL;

ApplyUpdates you use after the execution of an Sql.

Steph

 

[McMerfy]

Terry, if you need to change your SQL text in runtime, then you must do it only when your dataset is inactive. Otherwise if you change it in active dataset a method Close would be be called, so by the moment you call ApplyUpdates your dataset is closed. And so you get an exception.

--- markus.

 

[delphiman]

I remain puzzled because the following works in a TDataModule - except for the code between A and B having no effect.

procedure TParentDM.IBFatherDataSetAfterScroll(DataSet: TDataSet);
begin
with ParentDM.IBFatherDataSet do
begin
ParentDM.IBdsTrnsfr.Active := True;
if ParentDM.IBdsTrnsfr.state in dsEditModes then
else
ParentDM.IBdsTrnsfr.edit;
ParentDM.IBdsTrnsfrP_NO.value := ParentDM.IBFatherDataSetP_NO.value;

ParentDM.IBdsTrnsfr.ApplyUpdates;

ParentDM.IBdsChild.Open; / / .............A
ParentDM.IBdsChild.ApplyUpdates;
ParentDM.IBdsGChild.Open;
ParentDM.IBdsGChild.ApplyUpdates;
ParentDM.IBdsGGChild.Open;
ParentDM.IBdsGGChild.ApplyUpdates; / / .............B
end;
end;

However similar code (as below) works perfectly in a TButton eventhandler (on a TForm) .

procedure TfrmFather.btnUpdChildClick(Sender: TObject);
begin
ParentDM.IBdsTrnsfr.ApplyUpdates;
ParentDM.IBFatherDataSet.ApplyUpdates;
ChildrenDM.IBdsChild.ApplyUpdates;
GChildrenDM.IBdsGChild.ApplyUpdates;
GGChildrenDM.IBdsGGChild.ApplyUpdates;
end;

My problem being that I don't want to have to push a button to get the Updates above. I need it to be automatic as in AfterScroll as in the top code sequence.

 

[delphiman]

>ParentDM.IBdsChild.ExecSQL;

Thanks for this suggestion.

But could you please be more specific about how (where) to apply this statement bearing in mind that the IBDataSet only has the following SQL properties.

DeleteSQL;
InsertSQL;
ModifySQL;
RefreshSQL;
SelectSQL

I have tried your statement as follows in the above code :

ParentDM.IBdsChild.Open;
ParentDM.IBdsChild.ExecSQL;
ParentDM.IBdsChild.ApplyUpdates;

But it produces the following exception:

'use Open for a Select Statement'. Process stopped. Use Step or Run to continue.

 

[McMerfy]

Ok, look. Suppose you have a table ATABLE with only one field AFIELD, so a SelectSQL for your TIBDataSet would look like "SELECT * FROM ATABLE" and your ModifySQL should be "UPDATE ATABLE SET AFIELD = :AFIELD WHERE AFIELD = :OLD_AFIELD". Then you would have to take following steps to make changes to your ATABLE:

...  
with MyIBDataSet do
begin
  if Active then 
   Close;  
  Open;
  Edit;
  FieldByName('AFIELD').asInteger := MyIntegerValue; // here instead of asInteger can be any of other type.
  Post;
  MyIBDataBase.Commit;
end;

If you want to use ExecSQL method then i would recommend you to use TIBSQL component rather then TIBDataSet, but you still can use TIBDataSet, in this case code would look like:

with MyIBDataSet do
begin
  if Active then
   Close;
  SelectSQL.Text := 'UPDATE ATABLE SET AFIELD = :AFIELD';
  ExecSQL;
  MyIBDataBase.Commit; // or simply call to ApplyUpdates.
end;

Hope this would help.
--- markus