Rows affected with SQL Server @@ROWCOUNT

Use this example to see the core idea, then work through the complete steps below. Code snippets are Classic ASP / VBScript or SQL Server T-SQL as labelled, not PHP.

Read @@ROWCOUNT immediately

SQL example

 CopyUPDATE dbo.member SET status = 'active' WHERE status = 'pending';
SELECT @@ROWCOUNT AS affected_rows;

These statements must run together in the same SQL Server session with no intervening statement changing the count. In Classic ASP, a separate ADO Execute call is not a reliable way to retrieve the earlier count.

SQL Server's @@ROWCOUNT returns the number of rows affected or read by the immediately preceding statement. Read it immediately because later statements can change the value.

Update and read @@ROWCOUNT

 CopySet cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandType = 1 ' adCmdText
cmd.CommandText = "SET NOCOUNT ON; UPDATE dbo.member SET status = ? WHERE status = ?; SELECT @@ROWCOUNT AS affected_rows;"
cmd.Parameters.Append cmd.CreateParameter("pNew", 202, 1, 20, "active")
cmd.Parameters.Append cmd.CreateParameter("pOld", 202, 1, 20, "pending")
Set rs = cmd.Execute
Response.Write "Rows affected: " & rs("affected_rows")
rs.Close
Set rs = Nothing
Set cmd = Nothing

The historical example updated rows by comparing plaintext passwords. This revision uses neutral status fields instead; password changes should follow the hashing guidance in the authentication tutorials.

For operations that can affect more than two billion rows, SQL Server provides ROWCOUNT_BIG().

Reliable ADO rows-affected alternative

ADO Command.Execute can return a records-affected value for a data-modification statement. This is useful when the application only needs the affected count and not a separate SQL result set.

 CopyDim affected
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandType = 1 ' adCmdText
cmd.CommandText = "UPDATE dbo.member SET status = ? WHERE status = ?"
cmd.Parameters.Append cmd.CreateParameter("pNew", 202, 1, 20, "active")
cmd.Parameters.Append cmd.CreateParameter("pOld", 202, 1, 20, "pending")
cmd.Execute affected, , 128 ' adExecuteNoRecords
Response.Write "Rows affected: " & CLng(affected)

Why the order of statements matters

SQL Server @@ROWCOUNT reflects the immediately previous statement in that session. Another SQL statement—even a harmless-looking assignment or SELECT—may overwrite it. Use a single batch or capture the count immediately.

Frequently asked questions

Are these queries for SQL Server or Access?

The SQL pages use SQL Server T-SQL. Access has different date functions, identity behavior, and SQL syntax.

Can I execute these statements directly on a live database?

Start in a test database. INSERT, UPDATE, DELETE, DROP and CREATE change data or schema.

How should Classic ASP send input to a query?

Use an ADODB.Command with typed, ordered parameters and encode database values when writing HTML.

Back to top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: