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

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

SQL Rollback Transactional Error Code

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

SQL Rollback Transactional Error Code

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us