SQL Rollback Transactional Error Code

Question

0.00/5 (No votes)

See more:

This is the second amendment on this problem.

SQL

DECLARE @currentBalance int

BEGIN TRAN

SELECT @currentBalance = [Money] 
FROM CustomerBankInfo 
WHERE CustomerBankInfo.CustomerID = (SELECT CustomerID
				     FROM Customer
				     WHERE FirstName = 'Trevor'
				     AND LastName = 'Sandoval')
 
IF ((@currentBalance - 1000) < 0)
BEGIN
ROLLBACK TRAN 
END

UPDATE CustomerBankInfo
SET [Money] = ([Money] - 1000)
WHERE CustomerBankInfo.CustomerID = (SELECT CustomerID
				     FROM Customer
				     WHERE FirstName = 'Trevor'
				     AND LastName ='Sandoval')

UPDATE CompanyBankInfo
SET [Money] = ([Money] + 1000)
							 
									 
COMMIT TRAN

I don't get why it wont work. It comes up with:

(1 rows(s) affected)
(1 rows(s) affected)

"Msg 3902, Level 16, State 1, Line 25
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION."

I don't want it to do the UPDATES if the @current balance is less than 1000 yet it still effects it.

Any suggestions would be good.

Posted 15-Dec-11 7:09am

WurmInfinity

Updated 15-Dec-11 8:08am

v5

Add a Solution

Comments

Uday P.Singh 15-Dec-11 13:21pm

what's the error?

WurmInfinity 15-Dec-11 13:24pm

Well I have a front end which I've done in c# and used ADO.net to run the stored procedure. It works fine if I remove the top bit up to the rollback and just use the bottom update. So I guess its within the rollback itself. I feel like maybe because its not returning the RAISERROR anywhere its causing an issue, but it doesn't process the procedure.

2 solutions

Solution 2

It appears like your transaction failed, got rolled back and there is nothing to commit.

check this[^] out

hope it helps :)

Posted 15-Dec-11 8:25am

Uday P.Singh

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2011-12-15T08:05:00

Solution 1

Since the problem is data related (for example @orderValue being always zero) it's actually hard to say what's causing the problem. Try sending some more info from the procedure. For example:

SQL

BEGIN TRAN

SELECT @currentBalance = [Money]
FROM CustomerBankInfo
WHERE CustomerBankInfo.CustomerID = (SELECT CustomerID
                     FROM Customer
                     WHERE FirstName = @custFName
                     AND LastName =@custLName)
PRINT N'currentbalance:' + CAST(@currentBalance AS nvarchar(10));
PRINT N'orderValue:' + CAST(@orderValue AS nvarchar(10));
IF ((@currentBalance - @orderValue) < 0)
...

Do the values make sense?

Posted 15-Dec-11 8:05am

Wendelius

Comments

WurmInfinity 15-Dec-11 14:07pm

I've just updated my question. I think it makes more sense now. I'm not sure what you are trying to achieve there doesn't look like what I'm after. But thanks for trying! Possibly take a look at the reviewed version for me please?

Wendelius 15-Dec-11 14:18pm

Ok, now the problem looks very different when you updated the question.

The problem looks like if the the statement

IF ((@currentBalance - 1000) < 0)

is true then you rollback the transaction, but you still continue the execution. So after that you do few updates and try to commit but then again if rollback was executed you don't have a transaction anymore.

If you want to stop the execution after the rollback, try adding return[^] after rollback

WurmInfinity 15-Dec-11 14:21pm

So basically it was going through it rather than doing it. RETURN has fixed it. Thank you so much, you have no idea how angry I was getting haha! You've made my day :)

Wendelius 15-Dec-11 14:24pm

No problem at all, glad it helped :)