"Paddy" wrote:

> I have a stored procedure which has 2 output parameters, namely @RecCnt and
> @RetCode. In the stored procedure, I am using the SET statements to pass the
> data back. I am calling the stored procedure from my VBA code. I use
> objCmd.Execute options:=adExecuteNoRecords.
>
> I am able to retrieve only the @RetCode value and not the @RecCnt value.
> Could any of you tell me what is wrong?
>
> VBA Code:
> --------------
>  Set objCmd = New ADODB.Command
>   
>    With objCmd
>       .CommandText = "sp_addback_selectcount_CorpAcctCtr"
>       .NAME = "sp_addback_selectcount_CorpAcctCtr"
>       .CommandType = adCmdStoredProc
>      
>       'Create parameter list for objCmd
>       .Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
> 3, vstrCorp)
>       .Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
> 5, vstrAcct)
>       .Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput, 5,
> vstrCtr)
>       .Parameters.Append .CreateParameter("RecCnt", adInteger,
> adParamOutput, 4)
>       .Parameters.Append .CreateParameter("RetCode", adBoolean,
> adParamOutput, 1)
>   
>       .ActiveConnection = objCon
>      
>       .Execute options:=adExecuteNoRecords
>    End With
>   
>   
>    If objCmd.Parameters("RetCode").Value Then
>       rlngRecCnt = objCmd.Parameters("RecCnt").Value
>       fnGetAddbackCnt = True
>    End If
>
> --Store Procedure
>
> CREATE PROCEDURE sp_lotusdata_load_from_recon
>     @Corp nvarchar(3),
>     @Acct nvarchar(5),
>     @Ctr nvarchar(5),
>     @RecsAffected int OUTPUT,
>     @RetCode bit OUTPUT
> AS
>
> -- local variables
>     DECLARE @ErrorNum smallint
>     DECLARE @RecCnt smallint
>
> -- initialization
>     SET @RetCode = 0
>     SET @ErrorNum = 0
>
> -- logic
>
>     SELECT @RecCnt = COUNT(*)
>       FROM RECON
>     WHERE CORP = @Corp
>            AND ACCT = @Acct
>        AND CTR = @Ctr
>
>     IF @RecCnt > 0
>     BEGIN
>         IF EXISTS (SELECT name
>                FROM   sysobjects
>                WHERE  name = N'lotusdata'
>                AND       type = 'U')
>         BEGIN
>              DROP TABLE LOTUSDATA   
>        
>              SELECT RECON.*
>                INTO LotusData
>                FROM RECON
>                      WHERE CORP = @Corp
>                    AND ACCT = @Acct
>                 AND CTR = @Ctr
>         END
>     END
>
>     SELECT @ErrorNum = @@ERROR
>
>     IF @ErrorNum = 0
>     BEGIN
>        SET @RecsAffected = @RecCnt
>        SET @RetCode = 1
>     END
> GO

"Paddy" wrote:

> Soooorrryyy...Goofed up the code and stored procedure...
>
> Here is the correct one:
> VBA Code:
> .......
> ......
>    Set objCmd = New ADODB.Command
>   
>    With objCmd
>       .CommandText = "sp_lotusdata_load_from_recon"
>       .NAME = "sp_lotusdata_load_from_recon"
>       .CommandType = adCmdStoredProc
>      
>       'Create parameter list for oCmd
>       .Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
> Len(vstrAcct), vstrAcct)
>       .Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
> Len(vstrCorp), vstrCorp)
>       .Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput,
> Len(vstrCtr), vstrCtr)
>       .Parameters.Append .CreateParameter("RecsAffected", adInteger,
> adParamOutput, 4)
>       .Parameters.Append .CreateParameter("RetCode", adBoolean,
> adParamOutput, 1)
>   
>       .ActiveConnection = objCon
>   
>       '-- execute the proc
>       .Execute options:=adExecuteNoRecords
>      
>       '-- return success if stored proc is successful
>       If .Parameters("RetCode").Value = True Then
>          rintRecsAffected = .Parameters("RecsAffected")
>          fnLoadLotusData = True
>       End If
> .....
> ....
>
> Stored Procedure Code:
> -----------------------------
> CREATE PROCEDURE sp_lotusdata_load_from_recon
>     @Corp nvarchar(3),
>     @Acct nvarchar(5),
>     @Ctr nvarchar(5),
>     @RecsAffected int OUTPUT,
>     @RetCode bit OUTPUT
> AS
>
> -- local variables
>     DECLARE @ErrorNum smallint
>     DECLARE @RecCnt smallint
>
> -- initialization
>     SET @RetCode = 0
>     SET @ErrorNum = 0
>
> -- logic
>
>     SELECT @RecCnt = COUNT(*)
>       FROM RECON
>     WHERE CORP = @Corp
>            AND ACCT = @Acct
>        AND CTR = @Ctr
>
>     IF @RecCnt > 0
>     BEGIN
>         IF EXISTS (SELECT name
>                FROM   sysobjects
>                WHERE  name = N'lotusdata'
>                AND       type = 'U')
>         BEGIN
>              DROP TABLE LOTUSDATA   
>        
>              SELECT RECON.*
>                INTO LotusData
>                FROM RECON
>                      WHERE CORP = @Corp
>                    AND ACCT = @Acct
>                 AND CTR = @Ctr
>         END
>     END
>
>     SELECT @ErrorNum = @@ERROR
>
>     IF @ErrorNum = 0
>     BEGIN
>        SET @RecsAffected = @RecCnt
>        SET @RetCode = 1
>     END
> GO
>
> Sorry for the error.
>
> Regards,
> Paddy
>
> "Paddy" wrote:
>
> > I have a stored procedure which has 2 output parameters, namely @RecCnt and
> > @RetCode. In the stored procedure, I am using the SET statements to pass the
> > data back. I am calling the stored procedure from my VBA code. I use
> > objCmd.Execute options:=adExecuteNoRecords.
> >
> > I am able to retrieve only the @RetCode value and not the @RecCnt value.
> > Could any of you tell me what is wrong?
> >
> > VBA Code:
> > --------------
> >  Set objCmd = New ADODB.Command
> >   
> >    With objCmd
> >       .CommandText = "sp_addback_selectcount_CorpAcctCtr"
> >       .NAME = "sp_addback_selectcount_CorpAcctCtr"
> >       .CommandType = adCmdStoredProc
> >      
> >       'Create parameter list for objCmd
> >       .Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
> > 3, vstrCorp)
> >       .Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
> > 5, vstrAcct)
> >       .Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput, 5,
> > vstrCtr)
> >       .Parameters.Append .CreateParameter("RecCnt", adInteger,
> > adParamOutput, 4)
> >       .Parameters.Append .CreateParameter("RetCode", adBoolean,
> > adParamOutput, 1)
> >   
> >       .ActiveConnection = objCon
> >      
> >       .Execute options:=adExecuteNoRecords
> >    End With
> >   
> >   
> >    If objCmd.Parameters("RetCode").Value Then
> >       rlngRecCnt = objCmd.Parameters("RecCnt").Value
> >       fnGetAddbackCnt = True
> >    End If
> >
> > --Store Procedure
> >
> > CREATE PROCEDURE sp_lotusdata_load_from_recon
> >     @Corp nvarchar(3),
> >     @Acct nvarchar(5),
> >     @Ctr nvarchar(5),
> >     @RecsAffected int OUTPUT,
> >     @RetCode bit OUTPUT
> > AS
> >
> > -- local variables
> >     DECLARE @ErrorNum smallint
> >     DECLARE @RecCnt smallint
> >
> > -- initialization
> >     SET @RetCode = 0
> >     SET @ErrorNum = 0
> >
> > -- logic
> >
> >     SELECT @RecCnt = COUNT(*)
> >       FROM RECON
> >     WHERE CORP = @Corp
> >            AND ACCT = @Acct
> >        AND CTR = @Ctr
> >
> >     IF @RecCnt > 0
> >     BEGIN
> >         IF EXISTS (SELECT name
> >                FROM   sysobjects
> >                WHERE  name = N'lotusdata'
> >                AND       type = 'U')
> >         BEGIN
> >              DROP TABLE LOTUSDATA   
> >        
> >              SELECT RECON.*
> >                INTO LotusData
> >                FROM RECON
> >                      WHERE CORP = @Corp
> >                    AND ACCT = @Acct
> >                 AND CTR = @Ctr
> >         END
> >     END
> >
> >     SELECT @ErrorNum = @@ERROR
> >
> >     IF @ErrorNum = 0
> >     BEGIN
> >        SET @RecsAffected = @RecCnt
> >        SET @RetCode = 1
> >     END
> > GO

"Erland Sommarskog" wrote:

> Paddy (Pa***@discussions.microsoft.com) writes:
> > I have a stored procedure which has 2 output parameters, namely @RecCnt
> > and @RetCode. In the stored procedure, I am using the SET statements to
> > pass the data back. I am calling the stored procedure from my VBA code.
> > I use objCmd.Execute options:=adExecuteNoRecords.
> >
> > I am able to retrieve only the @RetCode value and not the @RecCnt value.
> > Could any of you tell me what is wrong?
>
> How do you conclude that you can not retriev the RecCnt value?
>
> I don't think it should really matter, but it is a good idea to align
> the names in the parameters collection with the actual parameters names.
> Thus, the names should be @Corp, @Acct, @Ctr, @RowsAffected and
> @RetCode. Furthermore there is one parameter missing. That is, each
> stored procedure has a return value, which in ADO you declare as a the
> first parameter and as adParamReturnValue. Then again, I think it's find
> to not include that paraemeter.
>
> >       .CommandText = "sp_addback_selectcount_CorpAcctCtr"
>
> The sp_ prefix is reserved for system objects, and SQL Server first
> looks in the master database for these. Don't use it, in your own code.
>
> >       .Parameters.Append .CreateParameter("RecCnt", adInteger,
> > adParamOutput, 4)
> >       .Parameters.Append .CreateParameter("RetCode", adBoolean,
> > adParamOutput, 1)
>
> I think adParamInputOutput are more appropriate as that is what they
> are.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>