Make your own free website on Tripod.com
Home Page

Back to Papers


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

    Next

    Technical Papers Utilities and Scripts Book Reviews Links
    My Resume Fun & Games Email Home