Upon migrating an ASP.NET app from Borland Delphi 2006 to CodeGear RAD Studo 2007, from ASP.NET 1.x to 2.x, and from CoreLab BDP.NET MySQL Provider to MySQL Connector/NET, I started getting this error on attempts to commit a certain transaction:
There is already an open DataReader associated with this Connection which must be closed first.
The transaction was coded roughly like this:
try
DataGrid(target).datasource := mysqlcommand.Create
('select...',connection,transaction).ExecuteReader;
DataGrid(target).DataBind;
if a_condition then begin
mysqlcommand.Create('delete...',connection,transaction).ExecuteNonquery;
end;
transaction.Commit;
except
transaction.Rollback;
end;
DataGrid(target).datasource := mysqlcommand.Create
('select...',connection,transaction).ExecuteReader;
DataGrid(target).DataBind;
if a_condition then begin
mysqlcommand.Create('delete...',connection,transaction).ExecuteNonquery;
end;
transaction.Commit;
except
transaction.Rollback;
end;
The exception would be thrown on the Rollback after the Commit had failed.
Apparently DataBind does not close the mysqldatareader that is implcitly used as the datasource for the DataGrid. This should not have surprised me, but prior to migrating my app, this transaction worked, so I was temporarily at a loss when this problem arose.
The solution was to explicitly close the mysqldatareader by inserting this line immediately after the DataBind call:
mysqldatareader(DataGrid(target).datasource).Close;