12-10-2016, 04:10 PM
1458836979-VB2Oracle.doc (Size: 26.5 KB / Downloads: 7)
Before you can start pulling data from an Oracle database, you first need to set up a way for Visual Basic to communicate with Oracle. To do this, you need to set up an Oracle ODBC driver.
First, make sure you have the name of your database.
If you don’t have it, you can get it or create a new one by using the ‘Oracle Database Configuration Assistant’ in your Oracle – iSuites -> Database Administration folder.
Now, we must set up the ODBC Driver. To do this, go to Control Panel -> Administrative Tools and open ‘Data Sources (ODBC)’. Once open, the first tab titled ‘User DSN’ should contain any already set up ODBC drivers. Click the ‘Add…’ button on the right hand side of the form. When the ‘Create New Data Source’ window pops up, choose ‘Oracle ODBC Driver’ from the list and click finish. A new window entitled ‘Oracle 8 ODBC Driver Setup’ should appear. In the ‘Data Source Name’ field, enter the name you want to use as your ODBC Driver. In my case it was ‘CarParts’. The next field is to enter a description of the connection and it is optional. In the ‘Service Name’ field, enter the name of your database. Here I entered ‘CarParts’ because that was the name of my database. In UserID field, enter the user name that you use to access your data. In my case I entered ‘matt’. Leave all of the rest of the options untouched. Click ‘OK’ to finish. Close the ‘ODBC Data Source Administrator’.
To test the connection, I use the ‘Oracle ODBC Test’ located in the ‘Network Administration’ folder in the ‘Oracle – iSuites’ folder in my start menu. When you open the program, click the ‘Connect’ button to get started. A window titled ‘Select Data Source’ will open up. Click the ‘Machine Data Source’ tab on that window. Select the ODBC Driver you just created and click ‘OK’. It may take a minute, but a window will pop up titled ‘Oracle8 ODBC Driver Connect’. Leave the Service Name the same and enter your User Name and Password and click ‘OK’. If it doesn’t work, it will give you information why and you can even ask for further detail. If it does work, it will take you back to the main ‘Oracle ODBC Test’ window and the ‘Disconnect’ button will now be enabled. To see all of the tables you have created, press the ‘User Tables’ button. In the window, you can also enter an SQL statement and click ‘Execute’ and it will show you the results.
Now onto the Visual Basic part….
We are now going to connect to Oracle using the ODBC Driver. To make sure you have all the proper values, make sure that you create a ‘New Data Project’ in Visual Basic. You will need the following code in visual basic to make a connection. I made my connection variable global so that all forms in the database could use the same initial connection.
The following code is in a separate module in the application:
' Connection variable for the database
Public gConnection As ADODB.Connection
The following code is to open a connection. It is from my logon form that is loaded when the application starts:
' Variable to hold connection information
Dim strConn As String
' Create a new connection
Set gConnection = New ADODB.Connection
With gConnection ' Set the connection properties
.ConnectionTimeout = 3 ' Set the connection timeout(s)
.CursorLocation = adUseClient ' Use Client Based cursors
End With
' Create the first half of the connection string
strConn = "Data Source=CarParts;" ' Enter the name of your ODBC Driver
' This next code takes the User ID and Password that the user entered
' on my form and adds the values to the strConn string
strConn = strConn & "User Id=" & txtUID.Text & ";"
strConn = strConn & "Password=" & txtPWD.Text & ";"
' You can add a hard-coded username and password like this (just don’t
' do both)
strConn = strConn & "User Id=scott;Password=tiger;"
' Open the connection
gConnection.Open strConn
' Be sure to use gConnection.Close when exiting your application
In order to handle the data returned from executing an SQL statement, you need to create a recordset to handle it for you. Once you create and open a record set, you can get all of your data by looping through (if necessary) and setting the values of a textbox using the recordset like this:
' To get a value from the current record, use
' the syntax: recordset(“FIELD_NAME”)
txtGarageName.Text = rsGarages(“GARAGE_NAME”)
txtGarageAddress.Text = rsGarages(“ADDRESS”)
'etc…etc…
The following code illustrates how to create a new recordset, how to create an SQL statement, how to open the recordset and how to get the data from the recordset. The code populates a combo box with all of the garages in my database by creating a recordset using an SQL statement.
*cboGarages is a combo box that is on the form.
'Create the recordset variable
Dim rsGarages As ADODB.Recordset
'Create a string to hold the SQL statement
Dim strSQL As String
'Enable the combo box
cboGarages.Enabled = True
'Clear the combo box
cboGarages.Clear
' Add the first item
cboGarages.AddItem "Select a Garage.."
' Create a new recordset
Set rsGarage = New ADODB.Recordset
' Create the SQL statement
strSQL = "SELECT DISTINCT garages.garage_name " & _
"FROM garages, garage_purchases " & _
"WHERE garage_purchases.garage_id = garages.garage_id " & _
"ORDER BY garages.garage_name"
' Open the recordset using the SQL,the open connection,
' a Keyset-driven cursor and Optimistic Concurrency
rsGarage.Open strSQL, gConnection, adOpenKeyset, adLockOptimistic
' Loop through the recordset and populate the combo box
With rsGarage
While Not .EOF
' To get a value from the current record, use
' the syntax: recordset(“FIELD_NAME”)
cboGarages.AddItem rsGarage("GARAGE_NAME")
.MoveNext
Wend
End With
' Select the first item in the combo box as the default
cboGarages.ListIndex = 0
' Close the recordset
Set rsGarage = Nothing
You can use the same format to insert and update data as well. All you need to do is to change the strSQL variable to whatever you need to do, INSERT INTO, UPDATE, etc. Open the recordset the same way.