Re: ADORecordset

From: DriftWood <drift_wood_at_my-deja.com>
Date: Wed, 10 Jan 2001 14:23:42 GMT
Message-ID: <93hr93$ool$1@nnrp1.deja.com>

here are a couple:

Name: Illustrates use of the ado recordset recordCount property Category: Oracle OLEDB Provider
Port: WIN32
Description: This simple sample demonstrates how to get the record count from an ADO recordset object using the Oracle OLE DB Provider.

---

---

Set Db1 = New ADODB.Connection
Db1.ConnectionString = "Provider=ORAOLEDB.ORACLE;Data Source=V815;User ID=scott;password=tiger;"
Db1.Open
Set recset = New ADODB.Recordset
recset.CursorLocation = adUseClient
recset.Open "select * from bunchorows", Db1, adOpenKeyset, adLockOptimistic
MsgBox recset(0)
MsgBox (recset.RecordCount)
recset.Close
Db1.Close
Set recset = Nothing
Set Db1 = Nothing
MsgBox "Connection closed"

Name: VB6: Returning Resultset from a Stored Procedure using ADO Category: ODBC
Port: WIN32
Description: Example of Calling a stored procedure and returning a resultset from a Ref Cursor using the Oracle 8.0.5.6 Driver and ADO Version 2.1

---

---

Private Sub STProc_Click()
' This example code demonstrates calling packaged procedures
' From ADO and also returning result sets from them
'
' This code was tested using the following software:
' Visual Basic Version 6 Service Pack 3 with
' Microsoft Activex Data Objects Version 2.1
' Microsoft ODBC Driver Manager Version 3.510.3711.0
' Oracle ODBC DRiver for Oracle Version 8.0.5.6
' (NOTE: that this does not work with Version 8.0.5.0 to 8.0.5.4
' Error Message:
' Run-Time Error -2147467259(80004005)':
' ORA-6550 - PLS-00306 wrong number or types of argument in call
' to 'GETEMPS' )
'
' Oracle Database Version 8.0.5.1

' The Command to create the packaged procedure (under the SCOTT
schema) is
'
'create or replace package adotst as
'type empcur is ref cursor;

'procedure getemps(vdeptno in number,vcount out number,ecur out empcur);
'end adotst;
'/

'create or replace package body adotst as
'procedure getemps(vdeptno in number,vcount out number,ecur out empcur)
is
'begin
' select count(*) into vcount from emp where
' deptno = vdeptno;

' open ecur for select ename from emp
' where deptno = vdeptno;
'end getemps;
'end adotst;
'/

   Dim cnn1 As ADODB.Connection
   Dim cmdExeproc As ADODB.Command
   Dim prmDeptno As ADODB.Parameter
   Dim prmECount As ADODB.Parameter
   Dim rstDepts As ADODB.Recordset
   Dim intDeptno As Integer
   Dim strEname As String
   Dim strCnn As String
' Open connection.

   Set cnn1 = New ADODB.Connection
' Modify the following line to reflect a DSN within your environment

   strCnn = "DSN=W805; UID=scott; PWD=tiger;"    cnn1.Open strCnn
   cnn1.CursorLocation = adUseClient
' Open command object with one parameter.

   Set cmdExeproc = New ADODB.Command
' Note that this has been tested using
' cmdExeproc.CommandText = "scott.adotst.GetEmps"
' which also works

   cmdExeproc.CommandText = "adotst.GetEmps"    cmdExeproc.CommandType = adCmdStoredProc
' Get parameter value and append parameter.

   intDeptno = Trim(InputBox("Enter Department:"))    Set prmDeptno = cmdExeproc.CreateParameter("vdeptno", _

      adInteger, adParamInput)
   Set prmECount = cmdExeproc.CreateParameter("vcount", _

      adInteger, adParamOutput)
   cmdExeproc.Parameters.Append prmDeptno    prmDeptno.Value = intDeptno
   cmdExeproc.Parameters.Append prmECount
' Create recordset by executing the command.
' NOTE: if no resultset is being returned execute the stroed procedure
' using cmdExeproc.Execute on it's own

   Set cmdExeproc.ActiveConnection = cnn1    Set rstEmps = cmdExeproc.Execute
' Build stirng to be displayed with information returned

   strEname = "The " & prmECount.Value & _      " Employees in Department " & intDeptno & " are :"    Do While Not rstEmps.EOF

      strEname = strEname & " " & rstEmps!ename & ","
      rstEmps.MoveNext

   Loop
'

   MsgBox (strEname)
' Close resultsets and log off

   rstEmps.Close
   cnn1.Close
End Sub

---

---

-adding to a resultset

Dim cn As ADODB.Connection

   Dim rsEMP As ADODB.Recordset
   Dim strCn As String

   Set cn = New ADODB.Connection
   Set rsEMP = New ADODB.Recordset
   strCn = "Data Source = V815; User id = scott;password = tiger"    cn.Open strCn

   rsEMP.CursorType = adServer
   rsEMP.LockType = adLockOptimistic
   rsEMP.Open "EMP", cn, , , adCmdTable
   rsEMP.AddNew
   rsEMP!EMPNO = 6357
   rsEMP!ENAME = "DUFUS"
   rsEMP!JOB = "DBA"
   rsEMP!MGR = 1111
   rsEMP!HIREDATE = "03-Jan-01"
   rsEMP!SAL = 5000

   rsEMP!COMM = 5
   rsEMP!DEPTNO = 20
   rsEMP.Update

   rsEMP.Close
   cn.Close
   MsgBox "Done"

--

-cheers
  DW
--------------------------------------------------------------------
"It is a kind of good deed to say well; and yet words are not deeds.
  -William Shakespeare"


Sent via Deja.com
http://www.deja.com/

Received on Wed Jan 10 2001 - 08:23:42 CST