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.

Expand|Select|Wrap|Line Numbers

 DELETE
 
FROM groupPromotionRequests
 
WHERE groupPromotionRequestId = @requestId;

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

Expand|Select|Wrap|Line Numbers

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):

Expand|Select|Wrap|Line Numbers

 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.

insertAlias

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

Expand|Select|Wrap|Line Numbers

return @@ROWCOUNT

See if that works for you.

ExecuteNonQuery always 0 :(

re: ExecuteNonQuery always 0 :(

re: ExecuteNonQuery always 0 :(

re: ExecuteNonQuery always 0 :(

re: ExecuteNonQuery always 0 :(

Similar topics

More channels