1 / 23

Linking VB with Oracle Database

Linking VB with Oracle Database. Database Access Interfaces. DAO(Data Access Object) Accesses and controls local databases Can access and controls M/S ACCESS, dBase, FoxPro, Paradox RDO(Remote Data Object) Accesses remote databases

audra-velez
Download Presentation

Linking VB with Oracle Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Linking VB with Oracle Database

  2. Database Access Interfaces • DAO(Data Access Object) • Accesses and controls local databases • Can access and controls M/S ACCESS, dBase, FoxPro, Paradox • RDO(Remote Data Object) • Accesses remote databases • Uses standard ODBC(Open DataBase Connectivity) for access of relational databases • ADO(ActiveX Data Object) • Uses OLE DB which is based on M/S ActiveX for database access • OLE DB can access relational databases as well as non-relational databases like texts, audio, e-mails, etc.

  3. Registering Controls for ADO • To use ADO first we must register ADODC(ADO Data Control) at toolbox of Visual basic. • Choose menu in VB;[프로젝트]-[구성요소] • Then in [구성요소] dialog window, choose ‘Microsoft ADO Data Control 6.0’  new control ADODC is inserted in the bottom of TOOLBOX

  4. ADODC Programming • Connect DEPT table of scott • Make 3 textboxes with label ‘deptno’, ‘dname’, and ‘loc’ respectively and 1 ADODC control

  5. ADODC Programming(cont.1) • After pointing ‘Adodc1’ click right mouse button • Choose ‘ADODC 속성’, then you can see the following dialog window • Click the ‘작성’button

  6. ADODC Programming(cont.2) • Choose ‘Microsoft OLE DB Provider for Oracle’ • Click [다음]

  7. ADODC Programming(cont.3) • Enter server name(service name) • oracle • User name: • scott • Password: • tiger • Check ‘암호저장허용’ • Click [확인]

  8. ADODC Programming(cont.4) • Click ‘레코드 원본’tab • Choose ‘2 – adCmdTable’ for 명령형식 and ‘DEPT’ for ‘테이블 또는 저장 프로시저 이름’ • Click [확인]

  9. ADODC Programming(cont.5) • Choose the following values for each textbox

  10. ADODC Programming(cont.6) • The following shows executing window

  11. Data Project • Choose [새 프로젝트]-[데이터 프로젝트] at the starting menu of VB

  12. Data Project(cont.1) • Double click DataEnvironment1 for data connection setting

  13. Data Project (cont.2) • Click ‘Connection1’ with right button of mouse and choose ‘속성’menu • Then choose ‘Oracle Provider for OLE DB’ in the following pop-up window

  14. Data Project (cont.3) • Enter ‘oracle’ for ‘데이터원본’and ‘scott’ for ‘사용자이름’and ‘tiger’ for ‘암호’ • And also check ‘암호 저장 허용’

  15. Data Project (cont.4) • Click ‘폼’and double click ‘frmDataEnv’ to make a form

  16. Data Project (cont.5) • Make a table dept2 in scott account with the following fields deptno varchar2(2) dname varachar2(20) loc varchar2(20) • And enter 5 records • Insert into dept2 values (‘1’, ‘computer’, ‘U-IT building’) • . . . . .

  17. Data Project (cont.6) • Make a form looks like following

  18. Data Project (cont.7) • Click ‘Adodc1’ with right button of mouse to choose ‘Adodc 속성’menu • Click [작성] button to enter connection string

  19. Data Project (cont.8) • Click [레코드 원본] tab and enter ‘2-adCmdTable’ for ‘명령형식’and ‘DEPT2’ for ‘테이블 또는 저장 프로시저 이름’

  20. Data Project (cont.9) • Click DataGrid1 in the form • Choose ‘DataSource’ attribute value as Adodc1 • Click DataGrid1 with right button of mouse, • choose ‘필드 검색’change field name of the Grid • And if you choose also ‘속성’, you have the following dialog window Check these 2 only

  21. Data Project (cont.10) • Set the DataSource attribute values of Text1, Text2, and Text3 with Adodc1 • Set the DataField attribute values of Text1, Text2, and Text3 with deptno, dname, loc respectively

  22. Data Project (cont. 11) • Enter the following code for each command button Private Sub Command1_Click() ‘for [추가] button Dim dno, name, lname As String dno = InputBox("enter dept no", "new data insert") name = InputBox("enter dept name", "new data insert") lname = InputBox("enter dept loc", "new data insert") If Len(dno) > 0 Then Dim ConnectionString As String Dim query As String Dim DB As ADODB.Connection ConnectionString = "Provider=OraOLEDB.oracle.1;" & _ "Password=tiger;Persist Security Info=True;" & _ "User ID=scott;Data Source=oracle“ Set DB = New ADODB.Connection DB.Open ConnectionString query = "insert into DEPT2 values ('" & dno & "', '" & name & "', '" & lname & "')" DB.Execute query Adodc1.Refresh End If End Sub

  23. Data Project (cont.12) • Enter the following code for each command button Private Sub Command2_Click() ‘for [갱신] button Adodc1.Recordset.Update End Sub Private Sub Command3_Click() ‘for [삭제] button If MsgBox("really want to delete?", vbYesNo, "data delete") = vbYes Then Adodc1.Recordset.Delete End If End Sub Private Sub Command4_Click() ‘for [끝] button Unload Me End Sub

More Related