Delphi - TADOQuery.RowsAffected always -1 with UPDATE SQL statement

Question

I'm executing a SQL UPDATE statement using a TADOQuery component and want to inspect the number of rows that were updated. The UPDATE statement is executing fine and the rows are modified accordingly but the .RowsAffected property always returns -1. The documentation states -1 indicates an error with the statement, but as I say it executes fine. I've tried prefixing the statement with SET NOCOUNT OFF but this made no difference. My code is as follows :

var
  adoUpdateQuery: TADOQuery;
  blnUpdatedOK: Boolean;
begin
  adoUpdateQuery := TADOQuery.Create(nil);
  adoUpdateQuery.DisableControls;
  adoUpdateQuery.Connection := adcMiddleTierDB;
  adoUpdateQuery.ExecuteOptions := [eoExecuteNoRecords];
  adoUpdateQuery.SQL.Text := 'UPDATE MyTable SET Status = 1 WHERE Status = 0';
  try
    adoUpdateQuery.ExecSQL;
    blnUpdatedOK := (adoUpdateQuery.RowsAffected > 0);

I'm using Delphi XE2, connecting to MS SQL Server 2008R2.

It could be the `[eoExecuteNoRecords]` setting. ` the provider returns **Nothing** as long as the option adExecuteNoRecords is specified;` See [Execute Method (ADO Command)](http://msdn.microsoft.com/en-us/library/windows/desktop/ms681559(v=vs.85).aspx) — Pieter van Wyk, Oct 08 '12 at 10:31

score 3 · Accepted Answer · answered Oct 08 '12 at 11:16

3

Apologies guys, thanks for all your help but I've realised what the problem is. When typing my sample code snippet I failed to include in the SQL that I'm actually changing database as part of the query. The SQL should have shown :

USE MyDatabase; UPDATE MyTable SET Status = 1 WHERE Status = 0

It turns out that the USE command prevents the RowsAffected from working when in the same statement (stops it working in TADOQuery and TADOCommand). I've now fixed the problem by changing my code as follows :

try
  // Need to change database in separate SQL query in order for RowsAffected to work
  adoUpdateQuery.SQL.Text := 'USE MyDatabase;';
  adoUpdateQuery.ExecSQL;
  adoUpdateQuery.SQL.Text := 'UPDATE MyTable SET Status = 1 WHERE Status = 0';
  adoUpdateQuery.ExecSQL;
  blnUpdatedOK := (adoUpdateQuery.RowsAffected > 0);

Thanks

answered Oct 08 '12 at 11:16

Jonathan Wareham

3,357
7
46
82

+1, so the change of a database failed to return proper rows affected value. Strange, but good to know! – TLama Oct 08 '12 at 11:46
Maybe not the change, but the fact that multiple statements are executed in one go. – GolezTrol Oct 14 '12 at 13:34
I would choose to drop an ADOCommand on a datamodule for each statement I want to execute, and keep the SQL code of my Delphi code as much as possible. – GolezTrol Oct 14 '12 at 13:34
The use of two continuance executions looks kinda odd to me (without an isolating transaction). Are you 100% sure that the first statement is being used by the second update and in fact uses `MyDatabase` database? – kobik Oct 14 '12 at 14:50
Yes I'm 100% sure the USE MyDatabase executes and is used by the UPDATE statement. – Jonathan Wareham Oct 14 '12 at 22:04

score 1 · Answer 2 · answered Oct 08 '12 at 10:12

1

Use a TADOCommand and call it like this:

var
  AffectedRows: Integer;
begin
  adoUpdateCommand.Execute(AffectedRows, EmptyParam);

answered Oct 08 '12 at 10:12

GolezTrol

114,394
18
182
210

score 0 · Answer 3 · answered Oct 14 '12 at 11:52

One possibility is something like this:

adoUpdateQuery.SQL.Add( 'USE MyDatabase;' );
adoUpdateQuery.SQL.Add( 'UPDATE MyTable SET Status = 1 WHERE Status = 0;' );
adoUpdateQuery.SQL.Add( 'SELECT @@rowcount;' );

adoUpdateQuery.Open;
try
  LRowCount := adoQuery.Fields[0].AsInteger;
finally
  adoUpdateQuery.Close;
end;

If you have a lot of more statements you can store the RowCounts in a temptable and at the end publish the temptable with a select.

BTW TADOQuery.ExecSQL is a function and returns the number of affected rows. So your code can be more compact

blnUpdatedOK := ( adoUpdateQuery.ExecSQL > 0 );

Delphi - TADOQuery.RowsAffected always -1 with UPDATE SQL statement

3 Answers3