Returning @@rowcount to asp.net.....
-
I have a sproc that does an insert. All i want to do is return the # of rows affected so after the the insert i "select @@rowcount", returning 1, for the one inserted row. Works fine in QA. Run the sproc from the web, using my asp.net app and the return value is 4! Am I completely off on something i'm doing or has anyone else run into this before?
Sproc Code:
(
@userid varchar(25)
)
AS
declare @result int
update logins
set Reg = 'Y'
where (userid = @userid)
select @@rowcount
-
The answer is you are probably updating 4 rows in production. Run the query below on both production and QA.
Select Count(*)
From logins
Where userid = 'the_userid_passsed_when_sproc_was_called'
-
Checked that. Not the case. Thanks though!
-
Are you sure you have the right proc as the one above is an update and the first message talks abount an insert!
-
Ha! Excellent catch! I mis-typed my first message. The code is calling the sproc for the update. Sorry about the confusion.
-
So all is OK?
-
no, still no resolution
-
Both qa and production return 1 when you run count(*)? What do you get when you run the update outside the proc? Is there more to the proc than posted?
-
that's the whole proc. Userid is unique via a constraint set on the column. The update works ok in either place, it's just that from the web it returns 4 instead of 1. Thanks
-
Last question! If you execute the procedure from query analyzer on qa and production, what is returned from the procedure?
-
Are you sure that is the entire proc? Why are you declaring a variable called @result?
-
Result from Query Analyzer is 1.
Sorry, @result was leftover from when i was going to use an output parameter. Haven't gotten around to cleaning the code up yet since it isn't working
-
Think i found it. I was doing an ExecuteNonQuery when i should have been doing ExecuteScalar in my aspx page. Thanks for the help guys!
-
If you have not set "nocount", "on", within the SP, then you are getting 2 record sets returned when you are expecting 1.
-
I consider it a best practice thing to ALWAYS name your result columns and use column names to refer to the columns in your client-side code.
EG. You could have
select @@rowcount as cnt
Then in your client code, rather then getting fields(1).value you should get fields('cnt').value - this would have found your problem for you straight away as the field 'cnt' would not have existed...
I bet you feel better - it's nice to find that you are not crazy!!
Hunting for bugs like that is not fun
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply