Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Problem with error reporting when executing multiple sql staments in asp

Reply
Thread Tools

Problem with error reporting when executing multiple sql staments in asp

 
 
Dean g
Guest
Posts: n/a
 
      12-04-2004
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!
 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      12-04-2004
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"


 
Reply With Quote
 
 
 
 
Dean g
Guest
Posts: n/a
 
      12-05-2004
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!
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      12-05-2004
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"


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL ERROR while executing the code java.sql.SQLException: ORA-01008: not all variables bound mahesh Java 3 04-06-2010 10:59 AM
getting error while executing .sql file from sql task of ant naveenduttvyas Java 0 11-26-2008 09:23 AM
ASP .net SQL Server Reporting Services 401 Error RSH ASP .Net 0 06-29-2007 05:19 PM
ASP.NET, SQL Reporting Service, & Temp File problem =?Utf-8?B?aG90aWNlMzEwMA==?= ASP .Net 1 10-27-2004 02:07 PM
Response.Writing Rows based on If Staments Mark Sargent ASP General 6 10-25-2003 08:08 AM



Advertisments