Wrox Programmer Forums
|
BOOK: ASP.NET Website Programming Problem-Design-Solution
This is the forum to discuss the Wrox book ASP.NET Website Programming: Problem - Design - Solution, Visual Basic .NET Edition by Marco Bellinaso, Kevin Hoffman; ISBN: 9780764543869
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: ASP.NET Website Programming Problem-Design-Solution section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 17th, 2004, 05:56 PM
Registered User
 
Join Date: May 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Return codes

After constructing the BaseObj of the data tier I built a little test harness and made sure everything is working. It is. However, not having worked with @@ERROR in a SP before, I'm confused as to what value it is. Since we're in the .NET platform we get an exception and this seems to break the SP. Hence, the return value of @@ERROR is not reliable. It seems fine for indicating a lack of errors, but it seems utterly useless for errors.

I've tried putting a try block around the Execute.NonQuery call to catch it, but I still don't get anything returned this way. The error I introduce is usually something like commenting out one of my param values to the SP.

The question is how do you get a return in the middle of an exception that seems to break the SP? And what is its value since you already have an exception you can work with?

Thanks .... Paul


 
Old May 17th, 2004, 06:18 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hello,

Generally speaking, zero is the value when no error occurs. If an error occurs, it is set to something else than zero. You can return this value to the proc as a return statement or using an output variable, and declare this in your code and reference that variable procedure. You can also use transactions in your proc, so that if an error occurs, you can roll back the transaction.

Brian
 
Old May 17th, 2004, 11:20 PM
Registered User
 
Join Date: May 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by bmains
 Hello,
Generally speaking, zero is the value when no error occurs.

Quote:
quote:
Hi, Brian. Yes I get zero back legitimately with this line.

******
ReturnedSPCode = RunSP("sp_update_bugs", myParamArr, RowsAffected)
*******

Unfortunately, I get zero back when there's an error condition too. I'm nuts to figure out why it always returns zero. I do add the OUT param to the command object, and I even watched the count increase by one when I added it. It exists.

Here is how I am crudely trying to trap the error in the SP:

********
If @@ERROR <> 0
   BEGIN
      RAISERROR("Bad Error Dude", 16, 1)
      RETURN(99)
   END

ELSE

   BEGIN
    RETURN(0)
   END
*******

So what I get is the .NET exception, which I do trap and do nothing with. When the exception is thrown it seems like it disrupts what would be going on with the RAISERROR and @@ERROR because zero comes back as if no error.
Quote:
quote:
If an error occurs, it is set to something else than zero.
Quote:
quote:
As I said above, it is coming back as zero.
Quote:
quote:
You can return this value to the proc as a return statement or using an output variable, and declare this in your code and reference that variable procedure.
Quote:
quote: I do use RETURN as you can see from my SP code. I chose to return an arbitrary number 99 just for testing purposes, but it never returns. In my code behind I've tried to trap and ignore the .NET exception. I don't really want an exception; I just want the correct code to come back, but I have to live with the exception I guess. Is the exception causing an abort of the SP before it can return the correct code? I'm totally beaten like a dog on this one.

Thanks, Brian.
 
Old May 18th, 2004, 03:23 AM
Authorized User
 
Join Date: Nov 2003
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

A couple of points here:
1. Whan an error occurs in SQL Server you can't suppress it - it occurs whether or not your SP code detects @@ERROR. But in many cases, depending on the error, the SP will stop executing at the point of the error, so your detection code will not run.
2. SQL errors are raised as exceptions in ADO.Net. So you must catch them in order to determine how to proceed - justy testing your return code is not sufficient, as you have found.

So if you have error conditions that are likely to occur in the SP, then try and code around them - e.g. test for PK violations before INSERTs etc. Secondly, add logic to your catch block to treat the 'expected' errors you nevertheless can't avoid. This is common practice, for instance, when handling 1105 deadlocks to instigate a retry algorithm.



brian
 
Old May 18th, 2004, 10:05 PM
Registered User
 
Join Date: May 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by bschaldon
 A couple of points here:
1. Whan an error occurs in SQL Server you can't suppress it - it occurs whether or not your SP code detects @@ERROR. But in many cases, depending on the error, the SP will stop executing at the point of the error, so your detection code will not run.
2. SQL errors are raised as exceptions in ADO.Net. So you must catch them in order to determine how to proceed - justy testing your return code is not sufficient, as you have found.

So if you have error conditions that are likely to occur in the SP, then try and code around them - e.g. test for PK violations before INSERTs etc. Secondly, add logic to your catch block to treat the 'expected' errors you nevertheless can't avoid. This is common practice, for instance, when handling 1105 deadlocks to instigate a retry algorithm.



brian
 
Old May 19th, 2004, 12:38 PM
Registered User
 
Join Date: May 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by bschaldon
 A couple of points here:
1. Whan an error occurs in SQL Server you can't suppress it - it occurs whether or not your SP code detects @@ERROR. But in many cases, depending on the error, the SP will stop executing at the point of the error, so your detection code will not run.
2. SQL errors are raised as exceptions in ADO.Net. So you must catch them in order to determine how to proceed - justy testing your return code is not sufficient, as you have found.

So if you have error conditions that are likely to occur in the SP, then try and code around them - e.g. test for PK violations before INSERTs etc. Secondly, add logic to your catch block to treat the 'expected' errors you nevertheless can't avoid. This is common practice, for instance, when handling 1105 deadlocks to instigate a retry algorithm.



brian
Quote:
quote:

Sorry, I seemed to have botched my last reply, Brian. But thanks for the tips on how everything fires with regard to an ADO.NET exception and the SP return. I certainly would handle the exception, and I never would have bothered with the return at all if it hadn't been recommended by the authors. So now I'm wondering why bother at all. In the case of an exception you can't rely upon it, and you've caught the exception anyway, so what the hell is the point of a return code? I can see it you are working strictly in the SQL Server confines, but once you are talking to it from ADO.NET it seems like a rather useless piece of code and data.

What is the point of it? I could eliminate it and live with the results quite nicely, no? Or am I missing something due to some naive assumptions? ~Paul
 
Old May 20th, 2004, 03:15 AM
Authorized User
 
Join Date: Nov 2003
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I guess this is a matter of opinion, but I never write SP code that I expect to raise an error under normal operating conditions. Return codes are useful as just that - a way of returning a value to the client that indicates the degree of success of the procedure, or rather the reason it didn't work. If errors are occurring then either the data from the client is not well-enough validated, or the sproc needs to perform more checking itself.

brian

brian





Similar Threads
Thread Thread Starter Forum Replies Last Post
Automatically run some codes in SQL server 2005 Andraw SQL Server DTS 1 June 4th, 2008 09:40 PM
Return all or nothing SQL Statement help... brux2dc SQL Language 1 May 30th, 2007 03:44 AM
Sql Return question jezywrap ASP.NET 1.0 and 1.1 Professional 20 December 21st, 2006 05:58 PM
Codes stored in SQL database rylemer ASP.NET 1.0 and 1.1 Basics 1 December 1st, 2005 02:15 PM
Can I return error codes if I am not using rs? codehappy VB How-To 0 April 19th, 2005 03:19 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.