none
stored procedure always returning 1 RRS feed

  • Question

  • I am using sql server and C# calling a stored procedure that performs an insert and even though the insert fails it still returns 1.  called stored procedure from c# and using a DataSet queriestableadapter.  .Net 4.0
    Friday, August 12, 2011 2:51 PM

Answers

All replies

  • A stored procedure can return several resultsets. How are you setting the Return value in the SP--it's really up to you. Are you testing for a good INSERT in the SP? How?

    __________________________________________________________________
    William Vaughn
    Author, Mentor, Trainer, MVP
    Beta V Corporation
    William Vaughn's blog

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    “The Owl Wrangler” a fantasy fiction novel

    Please click the Mark as Answer button if a post solves your problem!

    Friday, August 12, 2011 3:34 PM
    Moderator
  • Thanks for the reply.  I have a try catch block and the catch block is executed during the insert.  When i run the SP by itself I get a rows affected of 1 all the time.  Hence it appears the C# code that calls the stored procedure appears to return a 1 as well.  I thought both would be 0.  Do I have to change the C# properties for the queriestableadapter that is set up to run the SP.  I can try and get an example of the code on here to illustrate what I am doing.  but currently I am not doing any return in the SP although the C# properties is setup to return a value and I believe it is a query value not a scalar.  In visual studio I just drop the SP on the DataSet design screen to have access to it.  I have tried fiddling with the properties but same result.  I have tried to return a 0 or -1 in the SP in the catch block but the return value still seems to be tied to the rows affected which is always 1.
    Friday, August 12, 2011 8:22 PM
  • As I discuss in the 7th Edition, there are a number of approaches to take. I like the use of an Output Parameter to manage the @@ROWCOUNT value (which reflects the number of rows changed by an DML query). Another approach is to use a SET RETURN = @@ROWCOUNT statement. Make sure "SET NOCOUNT ON" is NOT in the SP.

    hth


    __________________________________________________________________
    William Vaughn
    Author, Mentor, Trainer, MVP
    Beta V Corporation
    William Vaughn's blog

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    “The Owl Wrangler” a fantasy fiction novel

    Please click the Mark as Answer button if a post solves your problem!

    Friday, August 12, 2011 11:29 PM
    Moderator