getting @@identity and @@rowcount

[Guest]

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 ?

[ilssac]

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

[-__cfSearching__-]

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.

[tooMuchTrouble · Accepted solution]

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