astudent
asked on
rollback not working.
hello experts, i am trying to run create table, insert records and delete records in a table made in Oracle.
but once i have delted few records thru this program, i am not able to get them back by running RollBack command at sql prompt of Oracle 8. i m using connection string to connect to oracle. other things are working fine. I am providing the code below. please help. i wish to get the records back by using Rollback.
the code follows :
Option Explicit
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim sql As String, i As Integer
Private Sub Form_Load()
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
con.ConnectionString = "Provider=MSDAORA.1;User ID=scott/tiger;Persist Security Info=False"
con.Open
cmd.ActiveConnection = con
i =1
End Sub
Private Sub cmdCreateTable_Click()
cmd.ActiveConnection = con
cmd.CommandText="Create table tempp(rno number")
cmd.Execute
Print "Table Created !"
End Sub
Private Sub cmdInsert_Click()
sql = "Insert into tempp values(1)
cmd.CommandText = sql
cmd.Execute
sql = "Insert into tempp values(2)
cmd.CommandText = sql
cmd.Execute
sql = "Insert into tempp values(3)
cmd.CommandText = sql
cmd.Execute
End Sub
Private Sub cmd_delete_Click()
' delete few records
cmd.CommandText = "Delete tempp where rno>1"
cmd.Execute
Print "Records Deleted !"
''''''''' Here is the Problem ''''''''''
End Sub
The records deleted thru Vb cant be recalled even after rollback is issued from SQL.
but once i have delted few records thru this program, i am not able to get them back by running RollBack command at sql prompt of Oracle 8. i m using connection string to connect to oracle. other things are working fine. I am providing the code below. please help. i wish to get the records back by using Rollback.
the code follows :
Option Explicit
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim sql As String, i As Integer
Private Sub Form_Load()
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
con.ConnectionString = "Provider=MSDAORA.1;User ID=scott/tiger;Persist Security Info=False"
con.Open
cmd.ActiveConnection = con
i =1
End Sub
Private Sub cmdCreateTable_Click()
cmd.ActiveConnection = con
cmd.CommandText="Create table tempp(rno number")
cmd.Execute
Print "Table Created !"
End Sub
Private Sub cmdInsert_Click()
sql = "Insert into tempp values(1)
cmd.CommandText = sql
cmd.Execute
sql = "Insert into tempp values(2)
cmd.CommandText = sql
cmd.Execute
sql = "Insert into tempp values(3)
cmd.CommandText = sql
cmd.Execute
End Sub
Private Sub cmd_delete_Click()
' delete few records
cmd.CommandText = "Delete tempp where rno>1"
cmd.Execute
Print "Records Deleted !"
''''''''' Here is the Problem ''''''''''
End Sub
The records deleted thru Vb cant be recalled even after rollback is issued from SQL.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
So IF you want to perform a ROLLBACK, you need to do this using the connection in the program (thus in the program itself), which is enabled only by starting a explicit TRANSACTION in the application itself.
The ROLLBACK will rollback all the insert/delete/update since the last BEGIN TRANSACTION, not only the last action.
CHeers