June 26, 2013 at 1:04 pm
Hello all.
I have a SP which inserts data and all works fine. I have also written an ASP.NET (VB) page which passes the data to the SP and the SP inserts the data. All is good.
I don't want to assume anything and especially that the SP worked fine without error. I suppose if something goes wrong, I will see some kind of system generate error message on the page but I was hoping to learn how to handle an error more gracefully.
How do you more experienced developers do this? Can I get the SP to return a True or "success" kind of value if all goes well and a False or "Error" if something fails? That way, I can detect for it in my ASP.NEt page(s)....
June 26, 2013 at 1:14 pm
You can do a try...catch block in your stored procedure. In the catch you can log the errors in a table, use raiserror to return the error to the calling page..etc. You might also want to look into starting a transaction and rolling that transaction back if you catch an error. BOL has plenty of information on how to do all this.
http://msdn.microsoft.com/en-us/library/ms175976%28v=sql.105%29.aspx
http://msdn.microsoft.com/en-us/library/ms188929%28v=sql.105%29.aspx
June 26, 2013 at 1:17 pm
I agree with Brendan to a point. If you want to catch something on the sql side you would use try-catch. Everything he said is accurate. The big difference here is that you want to know on the .net side when something goes wrong. If sql raises an error it will throw a sqlexception in .NET. So all you really need to do here is add a try/catch block in your .net code. Make sure you have a finally block so you can properly dispose of your sql connection so you don't end up hosing your connection pool. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2013 at 1:27 pm
What happens when someone executes that proc outside of his web page? Does he still want to capture errors?
June 26, 2013 at 1:30 pm
brendan woulfe (6/26/2013)
What happens when someone executes that proc outside of his web page? Does he still want to capture errors?
Dunno for sure. That is exactly why I said that having the try/catch in sql is good, but if you want to capture it in .net you have to have a try/catch there at the least. Depending on the process I have coded a number of things with try/catch in sql so it can be audited, and a try/catch in .net so I can capture the error and inform the user.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2013 at 1:44 pm
I'm with you...the last sentence ("all you really need to do...") threw me off. I thought you were implying just add a try/catch in .Net and be done with it.
I've usually done the same...with the .Net developers bubbling up a more friendly user message to the UI.
June 26, 2013 at 1:54 pm
brendan woulfe (6/26/2013)
I'm with you...the last sentence ("all you really need to do...") threw me off. I thought you were implying just add a try/catch in .Net and be done with it.I've usually done the same...with the .Net developers bubbling up a more friendly user message to the UI.
Poor wording on my part. Sounds like we are in 100% agreement.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2013 at 2:03 pm
😀
June 26, 2013 at 2:18 pm
Thanks Sean (and everyone else). I am catching on the .NET side by using Try / Catch. I've simply developed a habit to write the system errors to a asl:label hiding in the page in the "catch" clause.
I was hoping to bail on other sub routines if something does go wrong. So far, all has worked fine. But it's only a matter of time before something breaks. For example, my web form does a few things when the user clicks the Submit button. To water it down, it looks like this:
Sub btnSubmit_OnClick()
InsertData()
SendEmail()
End Sub
The InsertData Function will insert the data into the tbl by using a Stored Procedure. All works fine. But what it something goes wrong? I want to bail and get out of the Sub. How do I know InsertData() actually did it's job? Does a SP by nature return a Response like a Function?
June 26, 2013 at 2:25 pm
.to add...I suppose I could query the Max value of the PK. Say record 101 was the last one to be inserted. Then after the Insert takes place, I could do it again and if the second value is greater than the first, I could **assume** it worked. Just seems kind of crude and I'm sure there's a better way.
June 26, 2013 at 2:31 pm
RedBirdOBX (6/26/2013)
Thanks Sean (and everyone else). I am catching on the .NET side by using Try / Catch. I've simply developed a habit to write the system errors to a asl:label hiding in the page in the "catch" clause.I was hoping to bail on other sub routines if something does go wrong. So far, all has worked fine. But it's only a matter of time before something breaks. For example, my web form does a few things when the user clicks the Submit button. To water it down, it looks like this:
Sub btnSubmit_OnClick()
InsertData()
SendEmail()
End Sub
The InsertData Function will insert the data into the tbl by using a Stored Procedure. All works fine. But what it something goes wrong? I want to bail and get out of the Sub. How do I know InsertData() actually did it's job? Does a SP by nature return a Response like a Function?
A proc returns an integer. If the proc raises an error it will throw an exception in your .net code, as I said earlier. Here is an example of a proc that will crash.
create procedure [dbo].[CrashSproc] as
select 1/0
If you run this in SSMS it will raise and error. If you execute this in .net it will also raise an exception. Try it out. Add a call to this in your .net code and you see what happens.
Generally speaking in .net I like to have multiple catch blocks to catch different types of exceptions.
try
{
//do something
}
catch (System.Data.SqlClient.SqlException e)
{
//Do something with a sql exception here. Might be good to send an email to a dba or something...
}
catch (Exception e)
{
//This is some other kind of exception
}
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2013 at 2:33 pm
RedBirdOBX (6/26/2013)
.to add...I suppose I could query the Max value of the PK. Say record 101 was the last one to be inserted. Then after the Insert takes place, I could do it again and if the second value is greater than the first, I could **assume** it worked. Just seems kind of crude and I'm sure there's a better way.
Never code anything and just assume it worked. Also, using a value as the MAX(value) for inserted will work if there is only EVER 1 user in the system. As soon as there are 2 users you have to worry about concurrency (this means you always have to worry about concurrency).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2013 at 2:40 pm
I'm starting to think the way I call my routines and functions could be refined. Maybe something like?....
Sub btnSubmit_OnClick()
Try
InsertRecord()
SendEmail()
Catch
...Oopps. Show me error
Finally
End
In theory, if InsertRecord() fails for whatever reason, SendEmail() will be aborted.
Thoughts?
June 26, 2013 at 2:42 pm
RedBirdOBX (6/26/2013)
I'm starting to think the way I call my routines and functions could be refined. Maybe something like?....Sub btnSubmit_OnClick()
Try
InsertRecord()
SendEmail()
Catch
...Oopps. Show me error
Finally
End
In theory, if InsertRecord() fails for whatever reason, SendEmail() will be aborted.
Thoughts?
That looks like a solid format.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2013 at 2:42 pm
(I like the idea of multiple Catches. Nice!)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply