AdobeStock_455007340

Getting RECORDCOUNT For INSERT, UPDATE, DELETE Operations

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.

16 responses to “Getting RECORDCOUNT For INSERT, UPDATE, DELETE Operations”

  1. Ben Nadel Avatar
    Ben Nadel

    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.

  2. Merlinox Avatar
    Merlinox

    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

  3. James Netherton Avatar
    James Netherton

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

  4. Ben Forta Avatar
    Ben Forta

    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

  5. Merlinox Avatar
    Merlinox

    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&gt; (… is slow only for me that site?).
    result.recordCount returns -1 me too.
    Have a nice new week 🙂

  6. Kevin Avatar
    Kevin

    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.

  7. Ben Forta Avatar
    Ben Forta

    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

  8. Chris Avatar
    Chris

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

  9. Bob Jacoby Avatar
    Bob Jacoby

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

  10. Jason Avatar
    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.

  11. William from Lagos Avatar
    William from Lagos

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

  12. William from Lagos Avatar
    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

  13. Kevin Kazmierczak Avatar
    Kevin Kazmierczak

    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.

  14. Nils Thingvall Avatar
    Nils Thingvall

    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!

  15. Peter Smith Avatar
    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…

  16. Chris Avatar
    Chris

    This works but you need to use the result variable:
    cfquery datasource=”myDB” name=”qry_updRecord” result=”updateResultVar”

Leave a Reply