Error Code from SQL Server2000

[amitavabardhan]

How can I return an error code from a sql server stored procedure and use it
in my asp page to generate an alert indicating that some error has ocurred?

 

[Bob Barrows [MVP]]

How can I return an error code from a sql server stored procedure and
use it in my asp page to generate an alert indicating that some error
has ocurred?

An error in a procedure should raise a vbscript error. Also, it should show
up in your connection object's Errors collection.

Check out the two articles about error-handling on this site:
http://www.sommarskog.se/index.html

Bob Barrows

 

[amitavabardhan]

what I actually meant was that when I am calling a Sql Server stored
procedure from an asp page, then if an error occurs during execution of the
stored procedure, under such circumstances how can I capture that error in
the asp page so as to generate an error message and stop program execution.

 

[Bob Barrows [MVP]]

what I actually meant was that when I am calling a Sql Server stored
procedure from an asp page, then if an error occurs during execution
of the stored procedure, under such circumstances how can I capture
that error in the asp page so as to generate an error message and
stop program execution.

Same answer. Explain why the answer is not sufficient. Do you kinow how to
use On Error Resume Next to trap errors?

Bob Barrows

 

[amitavabardhan]

Yes, but how do I return an error message/ code from a sql server stored
procedure to an asp page?

 

[Bob Barrows [MVP]]

Are you replying to me? Please quote some of the message to which you are
replying.

Yes, but how do I return an error message/ code from a sql server
stored procedure to an asp page?

Again, errors are returned automatically to the ADO Connection, which
automatically bubbles them up to the vbscript runtime, which creates an Err
object containing the information, which you can trap using "On Error Resume
Next".


On Error Resume Next
connection.StoredProcedureName parm1,..,parmN
If err <> 0 then
'handle the error
errmsg= err.description
'optionally, check the connection's Errors collection:
if connection.Errors.Count > 0 then
for i = 0 to connection.Errors.Count - 1
errmsg=connection.Errors(i).Description
'etc.
next
end if
end if


The reason for checking the Errors collection is that in some cases,
multiple errors may be returned from the procedure. Only the first error
gets bubbled up to the vbscript runtime. The rest will be found in the
connection's errors collection.

Here is a link to the download location for the vbscript documentation:
http://tinyurl.com/7rk6

You can read the ADO documentation online at:
http://msdn.microsoft.com/library/en-us/ado270/htm/dasdkadooverview.asp

Here is some more information about executing stored procedures from ASP:
http://tinyurl.com/jyy0

HTH,
Bob Barrows

 

[amitavabardhan]

Yes Bob, I was replying to you. My sincere apologies for leaving you in
cold....
Thanks for the piece of code on error trapping from stored procedures. I
definitely appreciate you help on this.