Problem with error reporting when executing multiple sql staments in asp

D

Dean g

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
 
B

Bob Barrows [MVP]

Dean said:
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
 
D

Dean g

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
 
B

Bob Barrows [MVP]

Dean said:
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)
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top