473,324 Members | 2,535 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Problem with error reporting when executing multiple sql staments in asp

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!
Jul 22 '05 #1
3 1507
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"
Jul 22 '05 #2
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!
Jul 22 '05 #3
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"
Jul 22 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Neil Strong | last post by:
I'm getting an error message "Unidentified Index" when reading posted variables from a form $x = $_POST I know I can use 'isset()' to check them first, but I'm curious as to why this works on...
2
by: Alex Hopson | last post by:
I'm using the code below to loop through some images and resize each image twice, once to create a thumbnail and once to create a small image. The page stops loading around the 38th image out of...
3
by: DarthMacgyver | last post by:
Hello, I recently wrote a survey application. Each question is very similar. The first questions gives me a problem when there are multiple people taking the survey (The Database connection...
11
by: Alf P. Steinbach | last post by:
// As usual the error message directs one to the report the bug. // // And as usual there is absolutely no way to do so without paying for // the privilege... // // Or using three or four hours...
6
by: Tony Johansson | last post by:
Hello! I have a class called Outdoors which is a singleton see below for definition and a main that call this instance method. Now to my problem this piece of code doesn't compile I get the...
2
by: Randy Harris | last post by:
I thought that I had a grasp of how VBA error handling functioned, but have just become painfully aware that I don't. I thought that the "On Error GoTo 0" in the second sub below would turn off...
2
by: Joseph Geretz | last post by:
I'm having a credentialing problem in my web application. Actually, I don't think this is an IIS security issue, since I'm able to access the page I'm requesting. However, the executing page itself...
1
by: hotice3100 | last post by:
I have created an interface for a SQL Reporting Service report. SQL Reporting Service makes a virutal directly in IIS called ReportServer. There are various buttons on the form I created and one...
1
by: abh1508 | last post by:
Following a release of code the following problem occurs on certain asp ..net pages. This is not a problem on other testing/demo environments. IIS seems to be creating certain files twice in the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.