[Lazarus] SQLTransaction: Operation cannot be performed on an active transaction

[Eric Kom]

On 04/06/2013 17:14, Antonio Fortuny wrote:
> Le 04/06/2013 16:54, Eric Kom a écrit :
>> On 04/06/2013 10:19, Antonio Fortuny wrote:
>>>
>> 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;
> 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.
>> 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.
>>>
>>> Antonio.
>>>
>>>
>>> -- 
>>> _______________________________________________
>>> Lazarus mailing list
>>> Lazarus at lists.lazarus.freepascal.org
>>> http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
>>>
>>
>>
>
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?

>
>
> -- 
> _______________________________________________
> Lazarus mailing list
> Lazarus at lists.lazarus.freepascal.org
> http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
>


-- 
Kind Regards

Eric Kom

System Administrator & Programmer - Metropolitan College
  _________________________________________
/ You are scrupulously honest, frank, and \
| straightforward. Therefore you have few |
\ friends.                                /
  -----------------------------------------
    \
     \
         .--.
        |o_o |
        |:_/ |
       //   \ \
      (| Kom | )
     /'\_   _/`\
     \___)=(___/

2 Hennie Van Till, White River, 1240
Tel: 013 750 2255 | Fax: 013 750 0105 | Cell: 078 879 1334
erickom at kom.za.net | erickom at metropolitancollege.co.za
www.kom.za.net | www.kom.za.org | www.erickom.co.za

Key fingerprint: 513E E91A C243 3020 8735 09BB 2DBC 5AD7 A9DA 1EF5