Forta.com - Blog

<cfquery> returns the number of rows retrieved by a SELECT operation in the recordcount variable, available in the CFQUERY structure or the specified RESULT structure.

But what if you are performing INSERT, UPDATE, or DELETE operations, how could you determine the number of rows affected?

Well, apparently this one snuck in under the radar, I don't see it in the docs (which say that result_name.recordcount contains "Number of records (rows) returned from the query.") or in the release notes. But, as of CFMX 7.0.2, result_name.recordcount also returns the number of rows affected by INSERT, UPDATE, and DELETE operations.

Comments

[Add Comment] [Subscribe to Comments]

If you try to reference this pr 7.0.2 will it crash or just return zero? I can't test as I have an up-to-date dev box.

# Posted By Ben Nadel | 12/8/06 3:52 PM

I still tried it on Crystaltech hosting, but system replied me that recordCount is undefined in query result. Also I tried to CFDUMP query, but system replied me that query variable is undefined.

PRODUCTVERSION 7,0,2,142559

# Posted By Merlinox | 12/9/06 8:33 AM

I'm also running 7,0,2,142559 and only ever get -1 returned as the record count. Using MSSQL 2005 express.

# Posted By James Netherton | 12/9/06 8:59 AM

Merlinox, it's in the RESULT structure, not the query structure.

Ben, the RESULT structure is returned pre 7.0.2, but RECORDCOUNT will be empty or not present.

James, I'll report that. The number should be whatever the DBMS reports.

--- Ben

# Posted By Ben Forta | 12/9/06 10:23 PM

Thanks Ben, I didn't know RESULT field of query, and I found it on <a href="http://livedocs.macromedia.com/coldfusion/7/htmldo...; (... is slow only for me that site?).

result.recordCount returns -1 me too.
Have a nice new week :)

# Posted By Merlinox | 12/11/06 3:30 AM

This would be a great feature to have, but it doesn't seem to work for me using SQL 2000, CF Version:7,0,2,142559. I get -1 all the time in the result struct.

# Posted By Kevin | 12/11/06 11:27 AM

I tried it in Access and it works, but SQL Server ... I am going to test a few more databases. And I am also checking with the engineers and QA to see if they have any details as to what works and what does not.

--- Ben

# Posted By Ben Forta | 12/11/06 4:32 PM

Have you ever found a solution to this issue? I am running against MYSQL 5 and am seeing -1 as well.

# Posted By Chris | 5/7/07 3:20 PM

Ditto others against Oracle 9i. Only returns -1. Sadly, I'm not sure which version of CF 7 we're on.

Bob

# Posted By Bob Jacoby | 6/11/07 11:56 AM

I'm using CF 7.0.2.... and MySQL 5.0.27 and am having the same problem as many others. My result is -1.

# Posted By Jason | 7/12/07 11:22 AM

Hi Ben,

Just wondering, if this issue has been fixed in ColdFusion 8 beta.

Thanks

# Posted By William from Lagos | 7/21/07 7:57 PM

For anyone using CF 7 and looking for solution to this issue, check it out here http://www.techfeed.net/blog/index.cfm/2006/4/20/O...

# Posted By William from Lagos | 7/21/07 8:04 PM

Another thing you could do with SQL Server is to include SELECT changedRows = @@ ROWCOUNT right after you Insert/Update/Delete inside your cfquery and then reference it by queryname.changedRows.

# Posted By Kevin Kazmierczak | 7/23/07 8:40 AM

Just thought I would put one testimonial in here for this working. I set the result attribute for my query tag to queryResult and was able to get the recordCount through queryResult.recordCount for a DELETE query. Thanks Ben!

# Posted By Nils Thingvall | 5/14/09 3:01 PM

CF MX 7 + SQL 2000

Found I had to wrap the query with
SET NOCOUNT ON;
<cfquery....
SELECT changedRows = @@ROWCOUNT
SET NOCOUNT OFF;

*Note no space between @@ and Row as perkevin comment...

# Posted By Peter Smith | 10/1/09 1:31 PM

[Add Comment]

Getting RECORDCOUNT For INSERT, UPDATE, DELETE Operations