Problem with error reporting when executing multiple sql staments in asp

Discussion in 'ASP General' started by Dean g, Dec 4, 2004.

  1. Dean g

    Dean g Guest

    Hi, I have a problem with running multiple sql statements using asp.
    Basically if there is an error with any of the statements inside the
    query a rollback is done. the sql and rollback work fine, But on the
    actual asp page no error is detected unless it occurs in the first
    statement in the query. heres an example
    <%
    Sql= " BEGIN TRAN INSERT INTO Users VALUES ('BLAH', 'BLAH') INSERT INTO
    TESTING VALUES ('SOMETHING','SOMETHING') IF @@error <> 0 ROLLBACK TRAN
    ELSE COMMIT TRAN "
    If Err <> 0 Then

    Response.Write "error"

    Else
    Response.Write "<p>Data has been added!<p>"
    End If


    %>
    in that example if theres an error inserting into Testing the page will
    display 'Data has been added' even though it hasnt.

    Thanx any help is appreciated



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Dean g, Dec 4, 2004
    #1
    1. Advertising

  2. Dean g wrote:
    > Hi, I have a problem with running multiple sql statements using asp.
    > Basically if there is an error with any of the statements inside the
    > query a rollback is done. the sql and rollback work fine, But on the
    > actual asp page no error is detected unless it occurs in the first
    > statement in the query. heres an example
    > <%
    > Sql= " BEGIN TRAN INSERT INTO Users VALUES ('BLAH', 'BLAH') INSERT
    > INTO TESTING VALUES ('SOMETHING','SOMETHING') IF @@error <> 0
    > ROLLBACK TRAN ELSE COMMIT TRAN "
    > If Err <> 0 Then
    >
    > Response.Write "error"
    >
    > Else
    > Response.Write "<p>Data has been added!<p>"
    > End If
    >
    >
    > %>
    > in that example if theres an error inserting into Testing the page
    > will display 'Data has been added' even though it hasnt.
    >
    > Thanx any help is appreciated
    >
    >


    The way you have it, you are checking for an error after simply creating
    your sql string. Obviously, there's no error being raised at this point. You
    need to check for an error immediately after executing the sql string.

    sql = "..."
    conn.execute sql,,129
    if err <> 0 then
    ....

    It is crazy not to encapsulate this batch inside a stored procedure.

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Dec 4, 2004
    #2
    1. Advertising

  3. Dean g

    Dean g Guest

    my bad i forgot to add the execute after the sql string. Thats not the
    problem i just forgot to add it to the code

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Dean g, Dec 5, 2004
    #3
  4. Dean g wrote:
    > my bad i forgot to add the execute after the sql string. Thats not the
    > problem i just forgot to add it to the code
    >

    Please quote some of the previous conversation so the context will remain
    intact.

    You need to use RAISERROR to return an error to the client. You also need to
    check @@error a little more often. Which means using RETURN to facilitate
    the program folw, which means not doing it in a batch, but using a stored
    procedure.

    Open Query Analyzer and run this script:

    CREATE PROCEDURE InsUser (
    @val1 varchar(50),
    @val2 varchar(50),
    @val3 varchar(50),
    @val4 varchar(50)) AS

    SET NOCOUNT ON
    BEGIN TRAN
    INSERT INTO Users VALUES (@val1, @val2)
    IF @@error <> 0
    BEGIN
    ROLLBACK TRAN
    RAISERROR 200001 'Could not update Users'
    RETURN 1
    END

    INSERT INTO TESTING VALUES (@val3,@val4)
    IF @@error <> 0

    BEGIN
    ROLLBACK TRAN
    RAISERROR 200002 'Could not update TESTING'
    RETURN 1
    END
    COMMIT TRAN


    Then, in ASP, do this:

    conn.InsUser "blah1", "blah2","something1","something2"
    if err <> 0 then
    .....

    The benefit to this is that you will be debugging and optimizing your
    Transact-SQL code in an environment that has tools to help you optimize and
    debug that code. And once you are finished doing that, all that remains is
    to run that procedure from ASP (or whatever client application you are
    creating)


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Dec 5, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?aG90aWNlMzEwMA==?=

    ASP.NET, SQL Reporting Service, & Temp File problem

    =?Utf-8?B?aG90aWNlMzEwMA==?=, Oct 27, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    1,912
    Scott Allen
    Oct 27, 2004
  2. mahesh
    Replies:
    3
    Views:
    4,741
    cb_1987
    Apr 6, 2010
  3. RSH
    Replies:
    0
    Views:
    2,345
  4. naveenduttvyas
    Replies:
    0
    Views:
    961
    naveenduttvyas
    Nov 26, 2008
  5. Mark Sargent

    Response.Writing Rows based on If Staments

    Mark Sargent, Oct 23, 2003, in forum: ASP General
    Replies:
    6
    Views:
    148
    Mark Sargent
    Oct 25, 2003
Loading...

Share This Page