Download - VB - Oracle Database Connectivity
-
8/12/2019 VB - Oracle Database Connectivity
1/20
-
8/12/2019 VB - Oracle Database Connectivity
2/20
Steps to Connect Visual Basic & Oracle
1. Select SQL Plus from the Start Programs Oracle-OraClient10g_home1Application Development SQL Plus.
2. Enter the User Name, Password and Host String.
3. Create the table (eg. Employee).
-
8/12/2019 VB - Oracle Database Connectivity
3/20
-
8/12/2019 VB - Oracle Database Connectivity
4/20
6. Create the form with the following controls and assign the given for NamingConvention.
Control Name
Text Box txtEmpCodeText Box txtEname
Option Button optMale
Option Button optFemale
DTPicker dtpDob
Combo Box cboDesig
Combo Box cboDept
Text Box txtSalary
Command Button cmdAdd
Command Button cmdView
Command Button cmdEdit
Command Button cmdDeleteCommand Button cmdClear
Command Button cmdExit
7. For placing the DTPicker control, go to Project Components (or) press Ctrl+Tand select the Microsoft Windows Common Controls-2.6.0 (SP3) from the
Components and click OK to place in the Toolbox.
-
8/12/2019 VB - Oracle Database Connectivity
5/20
8. Now the form look like as below:
9. Change the form name as frmEmployee and project name as prjEmployee.10.Place the ADO Data Control by selection the Project Components (or) by
pressing Ctrl+T.
-
8/12/2019 VB - Oracle Database Connectivity
6/20
11.From the list of available components check the Microsoft ADO Data Control 6.0(SP3) (OLEDB).
12.From the Toolbox double click the Adodc control to place it in the form.
13.Select the Adodc and right click. Then select ADODC Properties.
-
8/12/2019 VB - Oracle Database Connectivity
7/20
14.In the Property Pages dialog box click the Use Connection String radio button andclick the Build button.
15.It opens the Data Link Properties dialog box. In that select the Microsoft OLE DBProvider for Oracle and click Next.
-
8/12/2019 VB - Oracle Database Connectivity
8/20
16.Enter the Server Name, User Name and Password correctly (based on yourinstitution configuration). Make the Allow Saving Password box as checked and
click Test Connection.
17.If everything is correct, it displays the Test Connection Succeeded message, thenclick OK.
18.Copy the text available in the Use Connection String text box.
-
8/12/2019 VB - Oracle Database Connectivity
9/20
19.Double click the form and place the cursor at the top of the code window and pressenter. Now we get General Declaration section. Declare the following variables.
Di m connEmp As ADODB. Connect i onDi m r sEmp As ADODB. Recor dset
20.Double click the form, it loads the Form Load event and enter the following code.Pr i vat e Sub For m_Load( )
Set connEmp = New ADODB. Connect i onconnEmp. Open "Pr ovi der=MSDAORA. 1; Password=baba; User I D=baba; Dat a
Sour ce=I BMREC; Per si st Secur i t y I nf o=True"connEmp. Cursor Locat i on = adUseCl i entMsgBox " Connect i on Est abl i shed. . . "cmdCl ear _Cl i ck
End Sub
21.Your code is look like as below:
22.Run the program by clicking the Start button (or) by pressing F5 button. If yourcode is correct, then the program will display the following message box.
23.Double click the Clear button and enter the following code.Pri vat e Sub cmdCl ear _Cl i ck( )
t xt EmpCode. Text = " "t xt Ename. Text = ""
opt Mal e. Val ue = Fal seopt Femal e. Val ue = Fal secboDesi g. Text = ""cboDept . Text = " "t xt Sal ar y. Text = ""
End Sub
-
8/12/2019 VB - Oracle Database Connectivity
10/20
24.Double click the Add button and enter the following code.Pr i vat e Sub cmdAdd_Cl i ck( )
Set r sEmp = New ADODB. Recor dsetr sEmp. Open "sel ect * f r om empl oyee wher e empcode = ' " &
t xt EmpCode. Text & " ' " , connEmp, adOpenKeyset ,
adLockReadOnl y, adCmdTextI f r sEmp. Recor dCount 0 Then
MsgBox "Empl oyee Code Al r eady Exi st s. . . "r sEmp. Cl oseSet r sEmp = Not hi ngExi t Sub
El seSet r sEmp = New ADODB. Recor dsetr sEmp. Open "sel ect * f r om empl oyee", connEmp, adOpenKeyset ,
adLockPessi mi st i c, adCmdTextr sEmp. AddNewr sEmp! Empcode = Tr i m( t xt EmpCode. Text )r sEmp! Ename = Tr i m( t xt Ename. Text )
I f opt Mal e. Val ue = True Thenr sEmp! Gender = "Mal e"
El seI f opt Femal e. Val ue = True Thenr sEmp! Gender = "Femal e"
End I fr sEmp! Dob = dt pDob. Val uer sEmp!Desi g = Tr i m( cboDesi g. Text )r sEmp!Dept = Tr i m( cboDept . Text )r sEmp! Sal ar y = Val ( Tr i m( t xtSal ar y. Text ) )r sEmp. Updat econnEmp. Execut e "commi t "r sEmp. Cl oseSet r sEmp = Not hi ng
MsgBox "Added Succesf ul l y. . . "cmdCl ear_ Cl i ck
End I fEnd Sub
25.Double click the View button and enter the following code.Pr i vat e Sub cmdVi ew_Cl i ck( )
Set r sEmp = New ADODB. Recor dsetr sEmp. Open "sel ect * f r om empl oyee wher e empcode = ' " &
t xt EmpCode. Text & " ' " , connEmp, adOpenKeyset , adLockReadOnl y,adCmdText
I f r sEmp. Recor dCount 0 Then
t xt Ename. Text = Tr i m( r sEmp! Ename)I f Tr i m( r sEmp!Gender ) = "Mal e" Then
opt Mal e. Val ue = TrueEl seI f Tr i m( r sEmp!Gender ) = "Femal e" Then
opt Femal e. Val ue = TrueEnd I fdt pDob. Val ue = r sEmp! DobcboDesi g. Text = Tr i m( r sEmp! Desi g)cboDept . Text = Tr i m( r sEmp!Dept )t xtSal ar y. Text = Val ( r sEmp! Sal ar y)MsgBox " Vi ewed Succesf ul l y. . . "
El seMsgBox "Empl oyee Code Not Exi st s. . . "
End I fr sEmp. Cl ose
-
8/12/2019 VB - Oracle Database Connectivity
11/20
Set r sEmp = Not hi ngEnd Sub
26.Double click the Delete button and enter the following code.Pri vat e Sub cmdDel et e_Cl i ck( )
I f ( MsgBox( "Ar e you sure t o del et e. . . " , vbYesNo) = vbYes) ThenSet r sEmp = New ADODB. Recor dsetr sEmp. Open "sel ect * f r om empl oyee wher e empcode = ' " &
t xt EmpCode. Text & " ' " , connEmp, adOpenKeyset ,adLockPessi mi st i c, adCmdText
r sEmp. Del eteconnEmp. Execut e "commi t "r sEmp. Cl oseSet r sEmp = Not hi ngMsgBox "Del et ed Succesf ul l y. . . "cmdCl ear_ Cl i ck
End I fEnd Sub
27.Double click the Edit button and enter the following code.Pri vat e Sub cmdEdi t _Cl i ck( )
I f ( MsgBox( "Ar e you sure t o edi t . . . " , vbYesNo) = vbYes) ThenSet r sEmp = New ADODB. Recor dsetr sEmp. Open "sel ect * f r om empl oyee wher e empcode = ' " &
t xt EmpCode. Text & " ' " , connEmp, adOpenKeyset ,adLockPessi mi st i c, adCmdText
r sEmp! Empcode = Tr i m( t xt EmpCode. Text )r sEmp! Ename = Tr i m( t xt Ename. Text )I f opt Mal e. Val ue = True Then
r sEmp! Gender = "Mal e"El seI f opt Femal e. Val ue = True Then
r sEmp! Gender = "Femal e"End I fr sEmp! Dob = dt pDob. Val uer sEmp!Desi g = Tr i m( cboDesi g. Text )r sEmp!Dept = Tr i m( cboDept . Text )r sEmp! Sal ar y = Val ( Tr i m( t xtSal ar y. Text ) )r sEmp. Updat econnEmp. Execut e "commi t "r sEmp. Cl oseSet r sEmp = Not hi ngMsgBox "Edi t ed Succesf ul l y. . . "cmdCl ear_ Cl i ck
End I fEnd Sub
-
8/12/2019 VB - Oracle Database Connectivity
12/20
28.Run the program and enter the values.
29.Click the Add button, the program displays the following Message box.
30.To see whether the values get stored in the database or not, enter into the SQL, andtype select * from employee ;, the SQL displays the inserted record values.
-
8/12/2019 VB - Oracle Database Connectivity
13/20
31.To edit any record values, clear the values by clicking the clear button and enter theEmployee Code in the text box and click View. If the record is available in the
database it will display the following message box.
32.If you want to modify any value, chagne it and click Edit button, then it will displaythe following confirmation Message box.
33.Click Yes to change or No to retain the previous values. If the selection is Yes, itdisplays the following Message box.
34.To delete any record, clear the values by clicking the clear button and enter theEmployee Code in the text box and click View. If the record is available in the
database it will display the following message box.
35.If you want to delete click Delete button, then it will display the followingconfirmation Message box.
-
8/12/2019 VB - Oracle Database Connectivity
14/20
36.Click Yes to delete or No to retain the record. If the selection is Yes, it displays thefollowing Message box.
37.Save the Form with the name frmEmployee.
38.Save the Project with the name prjEmployee.
-
8/12/2019 VB - Oracle Database Connectivity
15/20
39.Select No for adding project to SourceSafe.
40.To create an Executable file for this Project, select File Make prjEmployee.exe.
41.Select the path to store the Executable file and click OK.
-
8/12/2019 VB - Oracle Database Connectivity
16/20
Complete Code
Di m connEmp As ADODB. Connect i onDi m r sEmp As ADODB. Recor dset
Pr i vat e Sub cmdAdd_Cl i ck( )Set r sEmp = New ADODB. Recor dsetr sEmp. Open "sel ect * f r om empl oyee wher e empcode = ' " &
t xt EmpCode. Text & " ' " , connEmp, adOpenKeyset ,adLockReadOnl y, adCmdText
I f r sEmp. Recor dCount 0 ThenMsgBox "Empl oyee Code Al r eady Exi st s. . . "r sEmp. Cl oseSet r sEmp = Not hi ngExi t Sub
El seSet r sEmp = New ADODB. Recor dsetr sEmp. Open "sel ect * f r om empl oyee", connEmp, adOpenKeyset ,
adLockPessi mi st i c, adCmdTextr sEmp. AddNewr sEmp! Empcode = Tr i m( t xt EmpCode. Text )r sEmp! Ename = Tr i m( t xt Ename. Text )I f opt Mal e. Val ue = True Then
r sEmp! Gender = "Mal e"El seI f opt Femal e. Val ue = True Then
r sEmp! Gender = "Femal e"End I fr sEmp! Dob = dt pDob. Val uer sEmp!Desi g = Tr i m( cboDesi g. Text )r sEmp!Dept = Tr i m( cboDept . Text )r sEmp! Sal ar y = Val ( Tr i m( t xtSal ar y. Text ) )
r sEmp. Updat econnEmp. Execut e "commi t "r sEmp. Cl oseSet r sEmp = Not hi ngMsgBox "Added Succesf ul l y. . . "cmdCl ear_ Cl i ck
End I fEnd Sub
Pri vat e Sub cmdCl ear _Cl i ck()t xt EmpCode. Text = ""t xt Ename. Text = " "
opt Mal e. Val ue = Fal seopt Femal e. Val ue = Fal secboDesi g. Text = " "cboDept . Text = ""t xtSal ar y. Text = ""
End Sub
-
8/12/2019 VB - Oracle Database Connectivity
17/20
Pri vat e Sub cmdDel et e_Cl i ck( )I f ( MsgBox( "Ar e you sure t o del et e. . . " , vbYesNo) = vbYes) Then
Set r sEmp = New ADODB. Recor dsetr sEmp. Open "sel ect * f r om empl oyee wher e empcode = ' " &
t xt EmpCode. Text & " ' " , connEmp, adOpenKeyset ,adLockPessi mi st i c, adCmdText
r sEmp. Del eteconnEmp. Execut e "commi t "r sEmp. Cl oseSet r sEmp = Not hi ngMsgBox "Del et ed Succesf ul l y. . . "cmdCl ear_ Cl i ck
End I fEnd Sub
Pri vat e Sub cmdEdi t _Cl i ck( )I f ( MsgBox( "Ar e you sure t o edi t . . . " , vbYesNo) = vbYes) Then
Set r sEmp = New ADODB. Recor dsetr sEmp. Open "sel ect * f r om empl oyee wher e empcode = ' " &t xt EmpCode. Text & " ' " , connEmp, adOpenKeyset ,adLockPessi mi st i c, adCmdText
r sEmp! Empcode = Tr i m( t xt EmpCode. Text )r sEmp! Ename = Tr i m( t xt Ename. Text )I f opt Mal e. Val ue = True Then
r sEmp! Gender = "Mal e"El seI f opt Femal e. Val ue = True Then
r sEmp! Gender = "Femal e"End I fr sEmp! Dob = dt pDob. Val uer sEmp!Desi g = Tr i m( cboDesi g. Text )r sEmp!Dept = Tr i m( cboDept . Text )r sEmp! Sal ar y = Val ( Tr i m( t xtSal ar y. Text ) )r sEmp. Updat econnEmp. Execut e "commi t "r sEmp. Cl oseSet r sEmp = Not hi ngMsgBox "Edi t ed Succesf ul l y. . . "cmdCl ear_ Cl i ck
End I fEnd Sub
Pr i vat e Sub cmdExi t _Cl i ck( )End
End Sub
-
8/12/2019 VB - Oracle Database Connectivity
18/20
Pr i vat e Sub cmdVi ew_Cl i ck( )Set r sEmp = New ADODB. Recor dsetr sEmp. Open "sel ect * f r om empl oyee wher e empcode = ' " &
t xt EmpCode. Text & " ' " , connEmp, adOpenKeyset , adLockReadOnl y,adCmdText
I f r sEmp. Recor dCount 0 Then
t xt Ename. Text = Tr i m( r sEmp! Ename)I f Tr i m( r sEmp!Gender ) = "Mal e" Thenopt Mal e. Val ue = True
El seI f Tr i m( r sEmp!Gender ) = "Femal e" Thenopt Femal e. Val ue = True
End I fdt pDob. Val ue = r sEmp! DobcboDesi g. Text = Tr i m( r sEmp! Desi g)cboDept . Text = Tr i m( r sEmp!Dept )t xtSal ar y. Text = Val ( r sEmp! Sal ar y)MsgBox " Vi ewed Succesf ul l y. . . "
El seMsgBox "Empl oyee Code Not Exi st s. . . "
End I fr sEmp. Cl oseSet r sEmp = Not hi ng
End Sub
Pr i vat e Sub For m_Load( )Set connEmp = New ADODB. Connect i onconnEmp. Open "Pr ovi der=MSDAORA. 1; Passwor d=baba; User I D=baba; Dat a
Sour ce=I BMREC; Per si st Secur i t y I nf o=Tr ue"connEmp. Cursor Locat i on = adUseCl i entMsgBox " Connect i on Est abl i shed. . . "cmdCl ear _Cl i ck
End Sub
-
8/12/2019 VB - Oracle Database Connectivity
19/20
More about Cursor Types
CursorTypeEnum Values
Constant Value Description
adOpenUnspecified -1 Does not specify the type of cursor.
adOpenForwardOnly 0 Default. Uses a forward-only cursor. Identical to a
static cursor, except that you can only scroll forward
through records. This improves performance when you
need to make only one pass through a Recordset.
adOpenKeyset 1 Uses a keyset cursor. Like a dynamic cursor, except
that you can't see records that other users add, although
records that other users delete are inaccessible from
your Recordset. Data changes by other users are still
visible.
adOpenDynamic 2 Uses a dynamic cursor. Additions, changes, and
deletions by other users are visible, and all types of
movement through the Recordset are allowed, except
for bookmarks, if the provider doesn't support them.
adOpenStatic 3 Uses a static cursor. A static copy of a set of records
that you can use to find data or generate reports.
Additions, changes, or deletions by other users are not
visible.
LockTypeEnum Values
Constant Value Description
adLockUnspecified -1 Unspecified type of lock. Clones inherits
lock type from the original Recordset.
adLockReadOnly 1 Read-only records
adLockPessimistic 2 Pessimistic locking, record by record. The
provider lock records immediately after
editing
adLockOptimistic 3 Optimistic locking, record by record. The
provider lock records only when callingupdate
adLockBatchOptimistic 4 Optimistic batch updates. Required for
batch update mode
-
8/12/2019 VB - Oracle Database Connectivity
20/20