ExecuteNonQuery always 0 :(

[cbellew]

Hi guys,

I have a weird problem - I wonder if someone could spare a second for me. I have posted this exact question in the SQL Server forum but was advised to post it here instead.

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 groupPromotionRequests
 
WHERE groupPromotionRequestId = @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

[RobyV]

Why don't you try to run the procedure directly in the code of the form? Doing this you can check step by step what's wrong while deploying.
You can state a simple function like below (of course you need a valid SQL connection-> in my example is named cnSQL):

private void DeleteFunction()
        {
            string YOURVARIABLE = ""; //WHAT YOU WANT TO DELETE
            string sQuery = "DELETE FROM groupPromotionRequests WHERE groupPromotionRequestId = '" + YOURVARIABLE + "';
            try
            {
                if (cnSQL.State == ConnectionState.Closed) { cnSQL.Open(); }
                System.Data.SqlClient.SqlCommand command = cnSQL.CreateCommand();
                command.CommandText = sQuery;
                command.ExecuteNonQuery();
                if (cnSQL.State == ConnectionState.Open) { cnSQL.Close(); }
                MessageBox.Show("DELETED", "OK", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message, "attention!!", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

Hope this will help!
RobyV

[cbellew]

Thanks for that RobyV. However, I prefer to abstract DB functions from the UI. One reason is for reusability, another is for the performance of stored procedures. So this implementation would not work for me.

Thanks again for the time spent answering though!

[tlhintoq]

TIP: When you are writing your question, there is a button on the tool bar that wraps the [code] tags around your copy/pasted code. It helps a bunch. Its the button with a '#' on it. More on tags. They're cool. Check'em out.

[Curtis Rutland]

You could modify your SP to have a return value, and use this line to get it:

return @@ROWCOUNT

See if that works for you.