How to handle ADO Query with results vs. Query with no results?

ado, delphi, sql, sql-server

Solution

If your query returns a record set (`SELECT` statements) you should not use `ExecSQL` but simply `aADOQuery.Open` or `Active := True`.

For queries that do not return a record set e.g. `INSERT`/`UPDATE`/`DELETE`, use `ExecSQL`. in most cases you will get back `aADOQuery.RowsAffected` by your query.

Other SQL statements that you should use `ExecSQL`are `CREATE`/`ALTER`/`DROP`/`EXEC` etc... (no `RowsAffected` return in this case)

If the query does not return a cursor to data (such as `INSERT` statement), trying to `Open` or setting such `TDataSet` to `Active` will fail.

You could use `ADOConnection.Execute` instead of `TADOQuery` to execute your command-text, and then inspect if there is a valid `Recordset` returning from `ADOConnection`. In `ADOConnection.OnExecuteComplete` your could do something like this:

procedure TForm1.ADOConnection1ExecuteComplete(Connection: TADOConnection;
  RecordsAffected: Integer; const Error: Error;
  var EventStatus: TEventStatus; const Command: _Command;
  const Recordset: _Recordset);
begin
  // check for errors
  if Assigned(Error) then
  begin        
    Memo1.Lines.Add('Error: ' + Error.Description);
  end;
  // check for a valid recordset
  if Assigned(Recordset) then
  begin
    MyDataSet.Recordset := Recordset; // MyDataSet is TADODataSet
  end;
  // check for affected rows
  if RecordsAffected >= 0 then
    Memo1.Lines.Add('Records affected: ' + IntToStr(RecordsAffected))
  else
    Memo1.Lines.Add('Record count: ' + IntToStr(MyDataSet.RecordCount));
end;

Problem

I run various SQL statements using MSSQL and ADO. The code sequence looks like this: ``` aADOQuery.Active := False; aADOQuery.SQL.Text := ' MY SQL STATEMENT '; aADOQuery.ExecSQL; aADOQuery.Active := True; ``` The last statement fails if the SQL return result is empty. How to check for this case to avoid run time errors? Note: The SQL statement comes from a memo where the user is typing the SQL.

Original source