December 4, 2011 at 3:14 am
Hello,
I am struggling with a rollback problem....
For just a test purpose I have written the 3rd insert statement wrong.
when I debug the program at run, the debug says that the connection cannot rollback because is closed.
It seems that it close the connection automatically.
here below you can see what I am trying to do.
Thank you in advance for helping me.
on error go to rollback_lbl:
open connection
begintran
insert no.1
insert no.2
select no.1
select no.2
insert no.3
insert no.4
insert no.5
update no. 1
commit
rollaback_lbl:
msgbox " db error" err.description
rollback
December 4, 2011 at 3:58 am
Post the actual code please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2011 at 9:42 pm
You should put Exit Sub before error label statement...OR put your original code so we can find your problem and give proper solution...
December 5, 2011 at 4:10 pm
Dear friends,
I modified the code and now I have no errors in debugging. It executes the rollback.
The result is that it does not actually rollback. I can see records inserted in tab1 and 2.
I would like to rollback all the operations I did before... (from the insert in tab no.1....)
Here the code:
Sub INSERT_OPERATIONS
Dim pos,rtab1,rtab2,rtab3,rtab4,rtab5,Q1,Q2 as Integer
Dim vtext, vdescr,vquality as String
Dim vf1,vf2,vf3,vf4 as Integer
On Error GoTo error_exit
Set dbconn01 = New ADODB.Connection
Cnnxstr="Provider=SQLOLEDB.1............................."
dbconn01.connectiontimeout=30
dbconn01.open cnnxstr
Set cmdobj = New ADODB.Command
cmdobj.activeconnection=cnnxstr
cmdobj.commandtype= adcmdtext
dbconn01.BeginTrans
vtext="Insert into dbo.tab1....."
cmdobj.commandtext=vtext
cmdobj.execute rtab1
vtext="Insert into dbo.tab2....."
cmdobj.commandtext=vtext
cmdobj.execute rtab2
FIND_Q1ANDQ2 -------------------------------------> here I have another connection to retrieve data(open/close dbconn connection)
IF ERR.NUMBER<>0 THEN
GoTo error_exit
END IF
IF Q1 > 0 AND Q2 > 0 THEN
vtext="Insert into dbo.tab3....."
cmdobj.commandtext=vtext
cmdobj.execute rtab3
vtext="Insert into dbo.tab3....."
cmdobj.commandtext=vtext
cmdobj.execute rtab3
vf1=1111
vtext="Insert into dbo.tab3....."
cmdobj.commandtext=vtext
cmdobj.execute rtab3
vf1=2222
vtext="Insert into dbo.tab3....."
cmdobj.commandtext=vtext
cmdobj.execute rtab3
vf1=3333
vtext="Insert into dbo.tab3....."
cmdobj.commandtext=vtext
cmdobj.execute rtab3
vf2=1
vtext="Insert into dbo.tab4....."
cmdobj.commandtext=vtext
cmdobj.execute rtab4
vf2=2
vtext="Insert into dbo.tab4....."
cmdobj.commandtext=vtext
cmdobj.execute rtab4
vtext="Update dbo.tab2 set qual=1 where ....."
cmdobj.commandtext=vtext
cmdobj.execute rtab2
FIND_QUALITY ---------------------------> here I have another connection to retrieve data(open/close dbconn connection)
Select case Mid(vquality,6,2)
case "AA"
vdescr="descr.1"
case "AB"
vdescr="descr.2"
case "AC"
vdescr="descr.3"
case else
MsgBox ("Quality not valid!")
goto error_exit
end select
vtext="Insert into dbo.tab5....."
cmdobj.commandtext=vtext
cmdobj.execute rtab5
dbconn01.committrans
MsgBox ("Successfully executed!")
end if
exit sub
error_exit:
msgbox "error" & vbcrlf & err.number & "-" & err.description & vbcrlf & vtext, vbokonly
dbconn01.rollbacktrans
set cmdobj=nothing
end sub
Thank you very much for helping me.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply