Skip to main content
A
Anonymous
April 23, 2009
Answered

getting @@identity and @@rowcount

  • April 23, 2009
  • 3 replies
  • 665 views

When I do this

<cfquery name="test" datasource="#ds#">

   insert into tbltest (name,weight) values ('#thename#','#thewwight#')

   select @@ as idadded
   select @@rowcount as rowsadded

</cfquery>

<cfoutput>

   #itest.idadded#,#test.rowsadded#

</cfoutput>

testtest.idadded shows but test.rowsadded is not defined-I get an error.

1) How do I get both test.idadded and test.rowsadded to show ?

2) If done with cfstoredproc and a stored procedure (sqlserver), both of the values show up. Why is this ?

    This topic has been closed for replies.
    Correct answer tooMuchTrouble

    well for that kind of insert the row count will always be 1 & as the other folks

    have suggested there are other ways to skin this particular cat but if you

    really need to do it in your cfquery maybe something like:

    SELECT @@IDENTITY as idadded, @@ROWCOUNT as rowsadded

    3 replies

    T
    tooMuchTroubleCorrect answer
    Inspiring
    April 23, 2009

    well for that kind of insert the row count will always be 1 & as the other folks

    have suggested there are other ways to skin this particular cat but if you

    really need to do it in your cfquery maybe something like:

    SELECT @@IDENTITY as idadded, @@ROWCOUNT as rowsadded

    -
    -__cfSearching__-
    Inspiring
    April 23, 2009

    As mentioned, cfquery returns a single resultset.

    lwfg wrote:

    When I do this

    <cfquery name="test" datasource="#ds#">

       insert into tbltest (name,weight) values ('#thename#','#thewwight#')

       select @@ as idadded
       select @@rowcount as rowsadded

    </cfquery>


    Is there a reason you need the rowcount for a single record insert?

    Also, you should look into cfquery's "result" attribute.  It can return the rows affected, and the identity value from a _single record_ insert statement.

    ilssac
    ilssac
    Inspiring
    April 23, 2009

    <cfquery...> can only return one result set.  You can't have two selects like that in it.  For something like that you would need to go to stored procedures and the <cfstoredproc...>, <cfprocparam...> and <cfprocresult...> tags.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices