Skip to main content

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

Comments

  1. Nitesh PatareOctober 27, 2010 at 11:17 PM

    it really works try it...

    //procedure for deletion of record
    1 create or replace procedure studpro(n in number)
    2 is
    ...... 3 begin
    4 delete from stud where rno=n;
    5* end;
    6 /

    Procedure created.

    ReplyDelete
  2. rat2April 16, 2011 at 4:24 AM

    what about if oracle data base is loacated on any other computer on the network
    i meant to say how to coonect to any database on a network located database.

    ReplyDelete
  3. KobbySeptember 19, 2012 at 5:02 AM

    i want to build a school database management system that will allow for new students registration, class allocations and report generator. I want the students assessment to generate a report for printing. i wish to link the student assessment table to MS Excel so that i can access data from that table to generate a student report. please how do i go by this?

    ReplyDelete
  4. UnknownOctober 12, 2012 at 1:29 PM

    please help me in writing the coding for update command plyz

    ReplyDelete

Post a Comment

Popular posts from this blog

Oracle and VB 6.0 Connectivity (Part 1)

Here is a basic VB application called voter information system demonstrated which explains the concepts quite clearly. The first step required to establish connectivity is preparing the back end and the front end of the project. These steps are divided into two parts first the back end that is the oracle and front end that is the Visual Basic 6.0. Oracle Firstly we need to set up the database. Create the tables, triggers and procedures and database in the oracle. Create tables: Example: create table voter(id number(5) primary key, name varchar2(25),address varchar2(50),age number(3),gender varchar2(6),dob varchar2(30)); create table votertemp(id number(5) primary key, name varchar2(25),address varchar2(50),age number(3),gender varchar2(6),dob varchar2(30)); (we will be using the next table in the triggers that we will be implementing) Visual Basic 6.0 Develop the VB application as in this example given below. Design the necessary forms for the application. The next part is ...
13 comments
Read more

Validations in Visual Basic 6.0

This topic will demonstrate how to perform client side validations in Visual Basic 6.0. It will guide you with a step by step procedure. 1) Firstly in the below image suppose you want to accept only integer numbers as id in the textbox next to ID label in the form from the user you can perform client side validations in the following way: These are the ascii codes that i am using for acepting only integer values from user. Back space - 8 0 to 9 Integers - 48 to 57 Delete - 127 This is how the function will look. Private Sub Text2_KeyPress(KeyAscii As Integer) If Not ((KeyAscii >= 48 And KeyAscii <= 57) Or KeyAscii = 127 Or KeyAscii = 8) Then MsgBox "enter proper value" KeyAscii = 0 End If End Sub Double click on the text box which you want to validate. Next select the event as keypress from top right list box. In this case the name of text box is text1. This is the function for accepting input as integer value only from the user. ...
11 comments
Read more

Oracle and VB 6.0 Connectivity (Part 3)

Now we will see how to insert values into table a table and to view them. Oracle Initially we had created table in part1. Visual Basic 6.0 Now we wil insert data into the table through visual basic. The code for inserting will be as follows When you double click on add button it will open your code window it will have this code! Private Sub Command1_Click() Dim gen As String If (Option1.Value = True) Then gen = "male" ElseIf (Option2.Value = True) Then gen = "female" End If rsv.Open "insert into voter values(" & Text5.Text & ",'" & Text2.Text & "','" & Text3.Text & "','" & Combo1.Text & "','" & gen & "','" & DTPicker1.Value & "')", conv, adOpenDynamic, adLockOptimistic If (rsv.State = 1) Then rsv.Close End If MsgBox ("Data Entered") End Sub This will add the data entered into...
7 comments
Read more