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.

15 thoughts

  1. 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

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

  3. 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

  4. 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.

  5. 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

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

  7. 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.

  8. 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!

  9. 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…

Leave a Reply to Ben Nadel Cancel reply