Everytime I try to communicate with my database with stored procedures I get this "Cannot perform this operation on a closed dataset"

Question

I'm working on a Delphi project with a MS SQL Server database, I connected the database with ADOConnection, DataSource and ADOProc components from Borland Delphi 7 and I added this code in behind:

procedure TForm1.Button2Click(Sender: TObject);
begin
  ADOStoredProc1.ProcedureName := 'sp_Delete_Clen';
  ADOStoredProc1.Refresh;
  ADOStoredProc1.Parameters.ParamByName('@clenID').Value := Edit6.Text;
  ADOStoredProc1.Active := True;
  ADOStoredProc1.ExecProc;
end;

The component Edit6 is an editbox that takes the ID of the tuple that should be deleted from the database and ADOStoredProc1 is the stored procedure in the database that takes 1 parametar (the ID you want to delete). The project runs with no problems, I even got a TADOTable and a DBGrid that load the information from the database, but when I try to delete a tuple from the database using its ID written in the EditBox I get this Error: "Cannot perform this operation on a closed dataset" and the breakpoint of the project is when the application tries to add the value for the 'clenID' parameter. Where is my mistake and how to fix it?

Answer 1

I think the ADOStoredProc1.Refresh method is not appropriate here. In this case the stored procedure does not return a result set. Could you leave it out? And also the line ADOStoredProc1.Active := True. The connection to the database is open I presume? Could you also check the values of the Parameters collection in the Object Inspector?

Answer 2

I think you want to call ADOStoredProc1.Parameters.Refresh, not ADOStoredProc1.Refresh.

Also, you should only set Active to True if the SQL Server Stored procedure returns a dataset - i.e. the result of a SELECT statement. Setting Active to True is the same as calling Open.

If the stored procedure only returns a result code (RETURN n), then use ExecProc.

In no case should you use both ADOStoredProc1.Active := True; and ADOStoredProc1.ExecProc;

In summary, you probably want something like

procedure TForm1.btnDeleteClick(Sender: TObject);
begin
  ADOStoredProc1.ProcedureName := 'sp_Delete_Clen';
  ADOStoredProc1.Parameters.Refresh;  // gets the parameter list from SQL Server
  ADOStoredProc1.Parameters.ParamByName('@clenID').Value := edtID.Text;
  ADOStoredProc1.ExecProc;
end;