Oracle and VB 6.0 Connectivity (Part 4)

This part describes the way to implement procedure and cursor as well. I have together described cursor and procedure in a single example. What i have done is when you delete or update a record that record gets deleted from voter table and get inserted in to votertemp table.
In procedure what i have tried to do is the deleted record that is currently in votertemp gets deleted from votertemp table and gets inserted back to voter table as it was in its first instance

Oracle
1) type ed -> in the sql> command line -> it opens a notepad
2) type the procedure there!

create or replace procedure voterundo
is
cursor votercur is
select * from votertemp;
id number(5);
name varchar2(25);
address varchar2(50);
age varchar2(3);
gender varchar2 (6);
dob varchar2(30);
begin
open votercur;
loop
fetch votercur into id,name,address,age,gender,dob;
exit when votercur%notfound;
delete from voter;
insert into voter values(id,name,address,age,gender,dob) ;
delete from votertemp;
end loop;
end;
/

3) at prompt press “/”
4) it will say procedure created successfully!

Visual Basic

declare variable!
‘declare variables for connection recordset and command which we use for procedure
Dim conv As ADODB.Connection
Dim rsv As ADODB.Recordset
Dim cmdv As ADODB.Command

‘Form load
Private Sub Form_Load()
‘set connections
Set conv = New ADODB.Connection
Set rsv = New ADODB.Recordset
Set cmdv = New ADODB.Command
‘open connection
conv.Open “Provider=MSDASQL.1;Password=tiger;Persist Security Info=True;User ID=scott;Data Source=voter;Initial Catalog=voter”
End Sub

‘Undo Button double click then type this code
Private Sub Command9_Click()
‘when you click on undo button
‘this is the code for procedure you have to call it
‘these four lines of code call the procedure

cmdv.ActiveConnection = conv
cmdv.CommandType = adCmdStoredProc
cmdv.CommandText = “voterundo” ‘voterundo is the name of procedure that you created
‘finally
cmdv.Execute
MsgBox “undo”
End Sub

OutPut will make you clear to understant!
before delete
SQL> select * from voter;
ID NAME
————— ————————————-
ADDRESS AGE GENDER
————————————————————————— ————— ———
DOB
———————————————
77 nitesh
pune 22 male
10/23/1990

SQL> select * from votertemp;

no rows selected

after delete

SQL> select * from voter;

no rows selected

SQL> select * from votertemp;

ID NAME
————— ————————————-
ADDRESS AGE GENDER
————————————————————————— ————— ———
DOB
———————————————
77 nitesh
pune 22 male
10/23/1990

after undo procedure
SQL> select * from voter;

ID NAME
————— ————————————-
ADDRESS AGE GENDER
————————————————————————— ————— ———
DOB
———————————————
77 nitesh
pune 22 male
10/23/1990

SQL> select * from votertemp;
no rows selected

Advertisements

One thought on “Oracle and VB 6.0 Connectivity (Part 4)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s