Professional Web Applications Themes

returning error code form Stored Procedure - ASP Database

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 ...

  1. #1

    Default returning error code form Stored Procedure

    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 Bakhtiari Guest

  2. #2

    Default Re: returning error code form Stored Procedure

    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.


    Bob Barrows Guest

  3. #3

    Default Re: returning error code form Stored Procedure

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

    Ray at work

    "Bob Bakhtiari" <b_a_hram> wrote in message
    news:O5jzqsNxDHA.2148TK2MSFTNGP12.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!

    Ray at Guest

  4. #4

    Default Re: returning error code form Stored Procedure

    Chris Hohmann wrote:
    > "Bob Bakhtiari" <b_a_hram> wrote in message
    > news:O5jzqsNxDHA.2148TK2MSFTNGP12.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.


    Bob Barrows Guest

  5. #5

    Default Re: returning error code form Stored Procedure

    "Bob Bakhtiari" <b_a_hram> wrote in message
    news:O5jzqsNxDHA.2148TK2MSFTNGP12.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


    Chris Hohmann Guest

  6. #6

    Default Re: returning error code form Stored Procedure

    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.


    Bob Barrows Guest

  7. #7

    Default Re: returning error code form Stored Procedure

    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" <reb01501NOyahoo.SPAMcom> wrote in message
    news:OuP1KHOxDHA.1484TK2MSFTNGP09.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 Guest

  8. #8

    Default Re: returning error code form Stored Procedure


    "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
    news:OR2QhKOxDHA.1596TK2MSFTNGP10.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


    Ray at Guest

  9. #9

    Default Re: returning error code form Stored Procedure

    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 Guest

  10. #10

    Default Re: returning error code form Stored Procedure

    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.


    Bob Barrows Guest

  11. #11

    Default Re: returning error code form Stored Procedure

    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> wrote in message
    news:uXjJYZOxDHA.1596TK2MSFTNGP10.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!

    Ray at Guest

  12. #12

    Default Re: returning error code form Stored Procedure

    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!
    Bob Bakhtiari Guest

Similar Threads

  1. Help - stored procedure not returning a recordset
    By Bill S. in forum ASP Database
    Replies: 5
    Last Post: February 13th, 10:06 AM
  2. Stored Procedure not returning values.
    By Bob Barrows in forum ASP
    Replies: 2
    Last Post: August 14th, 12:31 PM
  3. Stored Procedure Not Returning
    By Amanda Osment in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 9th, 02:07 PM
  4. Stored Procedure not returning the wanted resultset
    By Henning Kongsgaard in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 4th, 08:01 AM
  5. Help with Stored Procedure returning recordset
    By Ivar in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 2nd, 04:31 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139