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.
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.
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
I’m also running 7,0,2,142559 and only ever get -1 returned as the record count. Using MSSQL 2005 express.
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
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 🙂
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.
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
Have you ever found a solution to this issue? I am running against MYSQL 5 and am seeing -1 as well.
Ditto others against Oracle 9i. Only returns -1. Sadly, I’m not sure which version of CF 7 we’re on.
Bob
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.
Hi Ben,
Just wondering, if this issue has been fixed in ColdFusion 8 beta.
Thanks
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
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.
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!
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…