Good day all;

I am trying to set the Active property of TSQLTransaction to False so that I can avoid the above Read Error message:
SQLTransaction: Operation cannot be performed on an active transaction The True value is currently set to True.

Le 04/06/2013 09:56, Eric Kom a ?crit :

Try SQLTransaction.Commit or SQLTransaction.RollBack instead, depending on the case. Afterwards you can do whatever you need with the transaction. Do not use the Retaining methods as they do not desactivate the transaction. If the underlying database is Firebird, use Commit preferably to RollBack unless the former job has to be really rollbacked.

Antonio.

Thanks, I did changed to Commit and then caNone before it solved the error, another error message pop up saying Access Violation. Anyway, the problem was solved.

The above code its giving me a problem; the SQLQuery do not changed according to the condition for exemple if the first condition matched, the second condition is still going to use the matching from the first and vice versa.

          else if  (cbbList.Items[cbbList.ItemIndex] = lisList1) then begin
               DataModuleConn.SQLTransaction1.Active:=True;
               DataModuleConn.SQLQuery1.SQL.Text:='SELECT year, country,
gender FROM reiser WHERE country = ''Dutch Republic''';
               DBGrid1.DataSource.DataSet:=DataModuleConn.SQLQuery1;
               DBGrid1.DataSource:=DataModuleConn.Datasource;
               DBGrid1.DataSource.DataSet.Active:=True;
               //DataModuleConn.SQLTransaction1.Commit;
               DataModuleConn.SQLTransaction1.CommitRetaining;
          end
          else if  (cbbList.Items[cbbList.ItemIndex] = lisList1) then begin
               DataModuleConn.SQLTransaction1.Active:=True;
               DataModuleConn.SQLQuery1.SQL.Text:='SELECT year, country,
gender FROM reiser WHERE country = ''Holland''';
               DBGrid1.DataSource.DataSet:=DataModuleConn.SQLQuery1;
               DBGrid1.DataSource:=DataModuleConn.Datasource;
               DBGrid1.DataSource.DataSet.Active:=True;
               //DataModuleConn.SQLTransaction1.Commit;
               DataModuleConn.SQLTransaction1.CommitRetaining;
          end;

I have tried DataModuleConn.SQLTransaction1.Rollback also, still the same.

Le 04/06/2013 16:54, Eric Kom a ?crit :

Use DataModuleConn.SQLTransaction1.StartTransaction instead, it is more readable ans standard
Also, if you commit or rollback the transaction most of the DataSet based components close their datasets as well. This means that the original transaction must be InTransaction for the grid to display the collected data. The transaction can be shared by all DataSet descendants though.

Hi all,

I tried reviewed the code and commented the try...except statement for more debug:

procedure TfrmMainWindow.bitBtn1Click(Sender: TObject);
begin
//try
      DataModuleConn.openConnection;
      DataModuleConn.MySQL55Conn.Transaction   :=
DataModuleConn.SQLTransaction;
      //DataModuleConn.MySQL55Conn.Transaction.Active:=False;
DataModuleConn.SQLTransaction.DataBase:=DataModuleConn.MySQL55Conn;
      DataModuleConn.SQLQuery.DataBase:=DataModuleConn.MySQL55Conn;
DataModuleConn.SQLQuery.Transaction:=DataModuleConn.SQLTransaction;
      DataModuleConn.SQLQuery.Active:=False;
      DataModuleConn.SQLQuery.SQL.Text:='';
      DataModuleConn.Datasource.DataSet:=DataModuleConn.SQLQuery;
      DataModuleConn.Datasource.DataSet.Active:=False;
      //DBGrid1.DataSource.DataSet.Active:=False;
      DBGrid1.DataSource:=DataModuleConn.Datasource;
      DBNavigator.DataSource:=DataModuleConn.Datasource;
      if  DataModuleConn.MySQL55Conn.Connected=True then begin
          frmMainWindow.memReports.Append(msgOpeningDB);
          frmMainWindow.memReports.Append(msgDBReady);
      end;
      if  (cbbList1.Items[cbbList1.ItemIndex] = 'ALL') then begin
           DataModuleConn.SQLQuery.SQL.Text:='SELECT gender, year,
country FROM reiser';
      end
      else if  (cbbList1.Items[cbbList1.ItemIndex] = lisCountry1) then begin
           DataModuleConn.SQLQuery.SQL.Text:='SELECT gender, year,
country FROM reiser WHERE country = ''Dutch Republic''';
      end
      else //(cbbList1.Items[cbbList1.ItemIndex] = lisCountry3)
      begin
         DataModuleConn.SQLQuery.SQL.Text:='SELECT gender, year, country
FROM reiser WHERE country = ''Holland''';
      end;
      //DataModuleConn.SQLTransaction.StartTransaction;
      DataModuleConn.SQLQuery.Close;
      DataModuleConn.Datasource.DataSet.Active:=True;
      DataModuleConn.SQLQuery.Open;
   //except
   //   frmMainWindow.memReports.Append(ErrConnectionFailed);
   //end;
end;

The first trigged for the procedure bitBtn1Click1 works fine, the second trigged give up, with the above error:

Operation cannot be performed on an active dataset.

Press OK to ignore and risk data corruption.
Press Cancel to kill the program.

I tried to close and open the Dataset on my procedure bitBtn1Click1.

May you please assist me?

Hi all,

Could you point out which line generates the error ? As a matter of fact, there are too much DB related operations. For instance the first 9 lines after the one // Try should be moved somewhere in an independent method and called only once in some intialization routine.
Secondly, the two next lines do exactle the same thing, only one of them should be removed

      DataModuleConn.Datasource.DataSet.Active:=True;     <==== keep

this one as more datasource independent
DataModuleConn.SQLQuery.Open; <---------- this one can be removed

This rearrangement should work provided the 9 preceeding lines have been moved somewhere else and executed at least once

      if DataModuleConn.SQLQuery.Active then
          DataModuleConn.SQLQuery.Close;
     if DataModuleConn.MySQL55Conn.Transaction.InTransaction then
         DataModuleConn.MySQL55Conn.Transaction.Commit;
      if  DataModuleConn.MySQL55Conn.Connected=True then begin
          frmMainWindow.memReports.Append(msgOpeningDB);
          frmMainWindow.memReports.Append(msgDBReady);
      end;
      if  (cbbList1.Items[cbbList1.ItemIndex] = 'ALL') then begin
           DataModuleConn.SQLQuery.SQL.Text:='SELECT gender, year,
country FROM reiser';
      end
      else if  (cbbList1.Items[cbbList1.ItemIndex] = lisCountry1) then
begin
           DataModuleConn.SQLQuery.SQL.Text:='SELECT gender, year,
country FROM reiser WHERE country = ''Dutch Republic''';
      end
      else //(cbbList1.Items[cbbList1.ItemIndex] = lisCountry3)
      begin
         DataModuleConn.SQLQuery.SQL.Text:='SELECT gender, year, country
FROM reiser WHERE country = ''Holland''';
      end;
      DataModuleConn.SQLTransaction.StartTransaction;
      DataModuleConn.SQLQuery.Open;

Antonio.

Thank you for your response
No specific line has been reported.
According to the error message, for another query to be executed, the Dataset must be turn to false so that previous query must be cancelled.

on the DataModuleConn.MySQL55Conn.Transaction.InTransaction, the InTransaction property/var its not reorganized by lazarus.

The following code works:
//try
      DataModuleConn.openConnection;
      if DataModuleConn.SQLQuery.Active then begin
          DataModuleConn.SQLQuery.Close;
      end;
      if DataModuleConn.SQLTransaction.Active then begin
         //DataModuleConn.MySQL55Conn.Transaction.Commit;
          DataModuleConn.SQLTransaction.Active:=True;
          DataModuleConn.SQLTransaction.Rollback;
          DataModuleConn.SQLTransaction.Active:=False;
      end;
      if  DataModuleConn.MySQL55Conn.Connected=True then begin
          frmMainWindow.memReports.Append(msgOpeningDB);
          frmMainWindow.memReports.Append(msgDBReady);
DataModuleConn.SQLTransaction.DataBase:=DataModuleConn.MySQL55Conn;
DataModuleConn.SQLQuery.DataBase:=DataModuleConn.MySQL55Conn;
DataModuleConn.SQLQuery.Transaction:=DataModuleConn.SQLTransaction;
          DataModuleConn.Datasource.DataSet:=DataModuleConn.SQLQuery;
      end;
     if  (cbbList1.Items[cbbList1.ItemIndex] = 'ALL') then begin
           DataModuleConn.SQLQuery.SQL.Text:='SELECT gender, year,
country FROM reiser';
      end
      else if  (cbbList1.Items[cbbList1.ItemIndex] = lisCountry1) then
begin
           DataModuleConn.SQLQuery.SQL.Text:='SELECT gender, year,
country FROM reiser WHERE country = ''Dutch Republic''';
      end
      else //(cbbList1.Items[cbbList1.ItemIndex] = lisCountry3)
      begin
         DataModuleConn.SQLQuery.SQL.Text:='SELECT gender, year, country
FROM reiser WHERE country = ''Holland''';
      end;
      DataModuleConn.SQLTransaction.StartTransaction;
      DataModuleConn.SQLQuery.Open;
DBGridStudentRegistration.DataSource:=DataModuleConn.Datasource;
DBNavigatorStudentRegistration.DataSource:=DataModuleConn.Datasource;