@@ROWCOUNT always 0 :(

[cbellew]

Hi guys,

I have a weird problem - I wonder if someone could spare a second for me.

I am running a DELETE statement inside a T-SQL Stored Procedure (for my C# .NET application). It's as simple as they get.

DELETE

FROM groupPromotionR equests

WHERE groupPromotionR equestId = @requestId;

However, when I call ExecuteNonQuery () I always get back 0. I have tried...

SET NOCOUNT OFF;

...in all sorts of places inside the procedure, yet it still always returns 0. I am certain that one row will be deleted as I have ran the query inside Management Studio and it said that one row was affected, plus I have checked the table itself.

I am very familiar with this setup and have used it all over my application, but for some reason this time it just won't work. I've tried renaming the procedure, debugging the return value from ExecuteNonQuery () and rewriting the procedure but nothing appears to work.

Does anyone have any clues? Thanks for your time.

Chris

[ck9663]

Are you asking why @@rowcount returns zero or ExecuteNonQuery () returns zero?

~~ CK

[cbellew]

Sorry for not being so clear in the question and its title.

I am really asking for why ExecuteNonQuery () returns 0. I have run the procedure in Management Studio and have got the message '1 row affected' (this indicates that @@ROWCOUNT is indeed working). Then when I replace the single row that was deleted and try with ExecuteNonQuery (), the return is 0.

[ck9663]

It looks like it's a C# .NET function. So you should post it to C# forum. It could be because it means no error. The 0 is a return/error code not the number of records affected.

Happy Coding!!!

~~ CK

[cbellew]

OK, thanks for your help - i'll post it over there.

I'm not totally convinced you are correct in saying it is an error code. Please see these two pages.

http://technet.microsoft.com/en-us/library/ms187316.aspx

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

[ck9663]

I'm sorry, am not much of a C# programmer. Check the T-SQL that you're executing. If there are two or more statement, the @@rowcount will only return the result for the last statement. Even if the last statement is just RETURN or SET statement.

Happy Coding!!!

~~ CK