returning error code form Stored Procedure

[Bob Bakhtiari]

Hi,

I'm trying to catch the error with following code and my code will stop
executing before getting to on error resume next. What is wrong with it?

'I'm executing a stored procedure
Set objRS = objCon.Execute(strQuery)
'I will not get to this line if there is an error like
'unique constraint in my SP
on error resume next
if Err.Number <> 0 then
response.Write(Err.Description)
else
Response.Redirect "mypage.asp"
end if

I'm checking for @@Error <> 0 after every SQL statement in my SP and
rolling back if there is an error.

Thanks,


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

Bob Bakhtiari wrote:

> Hi,
>
> I'm trying to catch the error with following code and my code will
> stop executing before getting to on error resume next. What is wrong
> with it?
>
> 'I'm executing a stored procedure
> Set objRS = objCon.Execute(strQuery)
> 'I will not get to this line if there is an error like
> 'unique constraint in my SP
> on error resume next
> if Err.Number <> 0 then
> response.Write(Err.Description)
> else
> Response.Redirect "mypage.asp"
> end if
>
> I'm checking for @@Error <> 0 after every SQL statement in my SP and
> rolling back if there is an error.
>

Can you provide us with the means to recreate this problem? A sample CREATE
TABLE script? A script containing INSERT...VALUES statements to insert
sample data into the table? The CREATE PROCEDURE script for the stored
procedure that is being run by the asp page? The string contained in
strQuery that results in the non-execution of the "if Err.Number <> 0 then"
line?

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Ray at]

[url]http://www.aspfaq.com/5003[/url]

Ray at work

"Bob Bakhtiari" <b_a_hram@yahoo.com> wrote in message
news:O5jzqsNxDHA.2148@TK2MSFTNGP12.phx.gbl...

> Hi,
>
> I'm trying to catch the error with following code and my code will stop
> executing before getting to on error resume next. What is wrong with it?
>
> 'I'm executing a stored procedure
> Set objRS = objCon.Execute(strQuery)
> 'I will not get to this line if there is an error like
> 'unique constraint in my SP
> on error resume next
> if Err.Number <> 0 then
> response.Write(Err.Description)
> else
> Response.Redirect "mypage.asp"
> end if
>
> I'm checking for @@Error <> 0 after every SQL statement in my SP and
> rolling back if there is an error.
>
> Thanks,
>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

Chris Hohmann wrote:

> "Bob Bakhtiari" <b_a_hram@yahoo.com> wrote in message
> news:O5jzqsNxDHA.2148@TK2MSFTNGP12.phx.gbl...

>> Hi,
>>
>> I'm trying to catch the error with following code and my code will
>> stop executing before getting to on error resume next. What is wrong
>> with it?
>>
>> 'I'm executing a stored procedure
>> Set objRS = objCon.Execute(strQuery)
>> 'I will not get to this line if there is an error like
>> 'unique constraint in my SP
>> on error resume next
>> if Err.Number <> 0 then
>> response.Write(Err.Description)
>> else
>> Response.Redirect "mypage.asp"
>> end if
>>
>> I'm checking for @@Error <> 0 after every SQL statement in my SP and
>> rolling back if there is an error.
>>
>> Thanks,
>>
>>
>> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
>> Don't just participate in USENET...get rewarded for it!

>
> Put the "On Error Resume Next" line BEFORE your objCon.Execute
> statement.

Oh duh!
I saw that but did not believe that his code actually looked like that ...

Bob
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Chris Hohmann]

"Bob Bakhtiari" <b_a_hram@yahoo.com> wrote in message
news:O5jzqsNxDHA.2148@TK2MSFTNGP12.phx.gbl...

> Hi,
>
> I'm trying to catch the error with following code and my code will

stop

> executing before getting to on error resume next. What is wrong with

it?

>
> 'I'm executing a stored procedure
> Set objRS = objCon.Execute(strQuery)
> 'I will not get to this line if there is an error like
> 'unique constraint in my SP
> on error resume next
> if Err.Number <> 0 then
> response.Write(Err.Description)
> else
> Response.Redirect "mypage.asp"
> end if
>
> I'm checking for @@Error <> 0 after every SQL statement in my SP and
> rolling back if there is an error.
>
> Thanks,
>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

Put the "On Error Resume Next" line BEFORE your objCon.Execute
statement.

HTH
-Chris Hohmann

[Bob Barrows]

Ray at <%=sLocation%> wrote:

> [url]http://www.aspfaq.com/5003[/url]
>

Umm - don't you think he got the message from your post in the vbscript
group?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Ray at]

Yes, but what about Joe Participator who sees it was multiposted and checks
the other groups to see if it was answered before he replies? Multiposting
the multiposting message to all the groups is preventative of the
"annoyed"ness described on that page, although I guess it would make more
sense for me to /cross/post the multipost message for this idea to be truly
effective. That is what I'll try next time.

Ray at work

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OuP1KHOxDHA.1484@TK2MSFTNGP09.phx.gbl...

> Ray at <%=sLocation%> wrote:

> > [url]http://www.aspfaq.com/5003[/url]
> >

> Umm - don't you think he got the message from your post in the vbscript
> group?
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

[Ray at]

"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:OR2QhKOxDHA.1596@TK2MSFTNGP10.phx.gbl...

> I guess it would make more
> sense for me to /cross/post the multipost message for this idea to be

truly

> effective.

I don't know that newsreaders would group the message properly though, now
that I think about it. I'll see.

Ray at work

[Bob Bakhtiari]

Hi
I did that, but still my script does not continue to work after Execute
if there is an error generated in SP.

Thanks,



*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

Bob Bakhtiari wrote:

> Hi
> I did that, but still my script does not continue to work after
> Execute if there is an error generated in SP.
>
> Thanks,
>

OK, back to my original response then:

Can you provide us with the means to recreate this problem? A sample CREATE
TABLE script? A script containing INSERT...VALUES statements to insert
sample data into the table? The CREATE PROCEDURE script for the stored
procedure that is being run by the asp page? The string contained in
strQuery that results in the non-execution of the "if Err.Number <> 0 then"
line?

Bob Barrows


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Ray at]

I'm no expert at this, but the ADODB.Connection object has an errors
collection.

For Each q in objCon.Errors
Response.Write q.number & " - " & q.description & "<br>"
Next

Ray at work



"Bob Bakhtiari" <b_a_hram@yahoo.com> wrote in message
news:uXjJYZOxDHA.1596@TK2MSFTNGP10.phx.gbl...

> Hi
> I did that, but still my script does not continue to work after Execute
> if there is an error generated in SP.
>
> Thanks,
>
>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

[Bob Bakhtiari]

Here is the SP code:

CREATE PROCEDURE dbo.UpdateUser

/*Input parameter for SP*/

@UserId AS INT,
@userName AS VARCHAR(150),
@UserAddress AS VARCHAR(150),
@UserPhone AS VARCHAR(50)
AS
BEGIN TRAN
SET NOCOUNT ON
BEGIN
IF ( (@UserId <> "" ) AND (@UserId IS NOT NULL))
BEGIN
UPDATE dbo.Users
SET
UserName = @UserName,
UserAddress = @UserAddress,
UserPhone = @UserPhone
WHERE UserId = @UserId
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
GOTO EOP
END
END
COMMIT TRAN
EOP:
GO

and in my ASP code I have:

strQuery = "EXEC dbo.UpdateUser "
strQuery = strQuery & " @UserId = " & strUserId & ", "
strQuery = strQuery & " @UserName = '" & strUserName & "'"
strQuery = strQuery & ", @UserAddress ='" & strUserAddress & "'"
strQuery = strQuery & ", @UserPhone ='" & strUserPhone & "' "

Set objRS = objCon.Execute(strQuery)
' Ihave a unique constraint on User name and if I try to have a duplicat
name, my code will not continue and I get the error mesage on my screen,
rather printing the error description. I also put on error before
execute and the result was the same.
on error resume next
if Err.Number <> 0 then
response.Write(Err.Description)
else
Response.Redirect "User.asp"
end if

Thanks Guys,

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!