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.
Getting RECORDCOUNT For INSERT, UPDATE, DELETE Operations
16 Comments
B
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.
B
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
J
James Netherton
I'm also running 7,0,2,142559 and only ever get -1 returned as the record count. Using MSSQL 2005 express.
M
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
B
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
K
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.
M
Thanks Ben, I didn't know RESULT field of query, and I found it on <a href="http://livedocs.macromedia.com/coldfusion/7/htmldocs/00000316.htm#1102316">livedocs</a> (... is slow only for me that site?).
result.recordCount returns -1 me too.
Have a nice new week :)
C
Chris
Have you ever found a solution to this issue? I am running against MYSQL 5 and am seeing -1 as well.
B
Bob Jacoby
Ditto others against Oracle 9i. Only returns -1. Sadly, I'm not sure which version of CF 7 we're on.
Bob
J
Jason
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.
W
William from Lagos
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/Obtaining-Affected-rows-from-SQL-Query
W
William from Lagos
Hi Ben,
Just wondering, if this issue has been fixed in ColdFusion 8 beta.
Thanks
K
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.
N
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!
P
Peter Smith
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...
C
Chris
This works but you need to use the result variable:
cfquery datasource="myDB" name="qry_updRecord" result="updateResultVar"