i now get the message:

'Operation is not allowed on an object referencing a closed or invalid 
connection'

Any ideas?

G

> Something like the following  works for us (loads the rs returned from 
the stored proc in to rs)
> and we can accept the very slight performance hit for using 
> .CommandType = adCmdText
> 
>    ' set up the command obj
>     Set cmdStoredProc = New ADODB.Command
>      
>     With cmdStoredProc
>         Set .ActiveConnection = mConn
>         .CommandText = "{call railtrack.locomotive_pkg.Get_All_Records
({resultset 0, rtnCursor})}"
>         .CommandType = adCmdText
>     End With
>     
>     Set rs = New ADODB.Recordset
>     With rs
>         .CursorLocation = adUseClient
>         .LockType = adLockReadOnly  ' can't update these from procs 
anyway
>         Set .Source = cmdStoredProc
>         .Open
>     End With
> 
> 
> where rtnCursor is defined as an OUT parm in the stored proc:
>    PROCEDURE  Get_All_Records (rtnCursor OUT refCursor);
> IF you are defining the returned rs as OUT perhaps your CreateParm 
statement should not be adParamInput
> 
> 
> > -----Original Message-----
> > From:	Gurdip Sambhi [SMTP:gurdip.sambhi@l...]
> > Sent:	Thursday, January 31, 2002 7:05 AM
> > To:	professional vb
> > Subject:	[pro_vb] Parameter error trying to call Oracle procedure 
using ADO
> > 
> > Dear all hope you can help.
> > 
> > I am currently working on an existing system which connects to Oracle 
> > stored procedures using rdo. I am changing this to ado, i can connect 
to 
> > the database, but when trying to call the procedure i get an error -
> > 2147217904, with description 'No value given for one or more required 
> > parameters. I am unsure whether this is due to incorrect syntax or any 
> > other reason.
> > 
> > The vb code is as follows:
> > 
> > Public Function RetrieveDataUsingADO(strStoredProc As String) As 
recordset
> > Dim cmmQuery As ADODB.Command
> > Dim rstReturned As ADODB.Recordset
> > Dim prmADO As ADODB.Parameter
> > 
> > Set cmmQuery = New ADODB.Command
> > 
> > ' *** strStoredProc = "{call PkgMaintenance.RetrieveAccountTypes _
> >                      & 
({resultset100,"sAccountType,sTransfersAvailable})}"
> > 
> > With Query
> >        Set prmADO = .CreateParameter 
_                                     
> >                     & (,adBSTR,adParamInput,22, "sAccountType")
> >        .Parameters.Append prmADO
> >        Set prmADO = .CreateParameter 
_                                     
> >                     & (,adBSTR,adParamInput,22, "sTransfersAvailable")
> >        .Parameters.Append prmADO
> > 
> >        .ActiveConnection = cnnConn
> >        .CommandTimeout = 30
> >        .CommandText = strStoredProc
> >        .CommandType = adCmdStoredProc
> >        Set RetrieveDataUsingADO = .Execute
> > End With
> > 
> > Exit Function
> > 
> > 
> > Any help would be greatly appreciated
> > 
> > Cheers
> > Gurdip