Introduction and Set-up

PL/SQL Package Header

PL/SQL Package Body

Visual Basic Code I

Visual Basic Code II

Conclusion and Additional Resources

Please note this page has moved to http://oracledoug.com/ado4.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-)

Visual Basic Code I

We will now look at how we can use Visual Basic to call our procedures and read the records stored in the REF CURSOR.

Start up VB and create a new project.
Add in "Microsoft ActiveX Data Objects 2.1 Library" from the References dialog within the Project menu
Add a text field to the form (name it "txtQuery")
Add a command button to the form (name it "CmdExecute")
Add a text box to the form (name it "txtResults")

We are going to use AL_PROCEDURE1 first. Type the following code into your project under the click event for the button.

Private Sub CmdExecute_Click()
Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim Cmd As New ADODB.Command
TxtResults.Text = ""
' It's important to use the correct connection string.  
' Especially the PLSQLRSet switch!
' Important:  I am using Oracle OLE DB Provider
' Remember to replace xxxxx with your database username/password, etc.
Conn.Open "PROVIDER=OraOLEDB.Oracle;DATA SOURCE=xxxxx;" & _
"USER ID=xxxxxx;PASSWORD=xxxxxx;PLSQLRSet=1"
    
Cmd.ActiveConnection = Conn
Cmd.CommandType = adCmdStoredProc
Cmd.CommandText = "AL_PACKAGE.AL_PROCEDURE1"
' Setup parameters.  Notice how we don't need one for the REF CURSOR.
' Also, notice how we pass the parameter in from the text field.
Cmd.Parameters.Append Cmd.CreateParameter("SiteID", 
					adVarChar, 
					adParamInput, 
					10, 
					TxtQuery.Text)
Cmd.Parameters.Append Cmd.CreateParameter("ErrCode", 
					adVarChar, 
					adParamOutput, 
					10)
' Execute the Procedure and populate our recordset
Set RS = Cmd.Execute
' A generic do while loop which will display
' all records contained in our recordset
Do While Not RS.EOF
    For i = 0 To RS.Fields.Count - 1
        
        TxtResults.Text = TxtResults.Text & RS.Fields(i).Value & Chr(9)
            
    Next
    TxtResults.Text = TxtResults.Text & vbCrLf
    RS.MoveNext
    
Loop
' Print error message if any
TxtResults.Text = TxtResults.Text & vbCrLf & 
		"Error Code: " & 
		Cmd.Parameters("ErrCode").Value & vbCrLf
RS.Close
Set Cmd = Nothing
Conn.Close
Set Conn = Nothing
End Sub

Save and run the project. If you type 8 into the query text box and click the button, you should get returned "TORONTO". Try a few different site_id's to see what happens (refer to the site table).

Previous