通过ADO检索存储过程输出参数

I'm stuck with this for more than 48 Hrs now. I'm trying to retrieve a output parameter value using ADO objects. here is my vba code.

我现在已经超过48小时了。我正在尝试使用ADO对象检索输出参数值。这是我的vba代码。

Function ExecuteCommand(cSp As String, aParameters As Variant) As Integer

Dim lnReturn As Integer
Dim loCommand As ADODB.Command
Dim prm As ADODB.Parameter
Dim rds As ADODB.Recordset

Set loCommand = New ADODB.Command

loCommand.CommandText = cSp
loCommand.CommandType = adCmdStoredProc
Set loCommand.ActiveConnection = goConnection
loCommand.NamedParameters = True

Dim iCount As Integer

For iCount = LBound(aParameters) To UBound(aParameters)
    If aParameters(iCount, 0) <> "" Then
        If aParameters(iCount, 1) = adNumeric Then
            Set prm = loCommand.CreateParameter(aParameters(iCount, 0), aParameters(iCount, 1), adParamInput)
            prm.Precision = 18
            prm.NumericScale = 0
            prm.Value = aParameters(iCount, 3)
        Else
            Set prm = loCommand.CreateParameter(aParameters(iCount, 0), aParameters(iCount, 1), adParamInput, , aParameters(iCount, 3))
        End If
        loCommand.Parameters.Append prm
    End If
Next

Set prm = loCommand.CreateParameter("@returnval", adInteger, adParamOutput, , lnReturn)
loCommand.Parameters.Append prm

Set rds = loCommand.Execute
' Check the value of lnReturn and it's zero here.
lnReturn = loCommand.Parameters("@returnval").Value


ExecuteCommand = lnReturn

End Function

My stored procedure as follows...

我的存储过程如下......

USE [table_name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spname]
@para1 int,
@para2 int,
@para3 int,
@returnval int Output
AS
BEGIN

SET NOCOUNT ON;

SELECT * FROM type_table WHERE type1=@para1 and type2=@para2 and type3=@para3
SET @returnval = @@ROWCOUNT
RETURN @returnval
END

These are my findings

这是我的发现

lnReturn = 0 always
rds.EOF = False
rds.RecordCount = -1

My question is I'm not getting the correct affected row count which is 1. I'm not sure what is wrong with the code and can someone give me a hand?

我的问题是我没有得到正确的受影响的行数，即1.我不确定代码有什么问题，有人可以帮忙吗？

3 个解决方案

#1

This is probably because, as Technet says, In the case of output parameters and return values, the values are not returned until the data of the Recordset object has been fetched completely or the Recordset has been closed.

这可能是因为，正如Technet所说，在输出参数和返回值的情况下，在完全获取Recordset对象的数据或关闭Recordset之前，不会返回这些值。

Either close your RecordSet (rds.Close), if you're not interested in the data returned by the SELECT statement, or loop through it until you've read all rows.

如果您对SELECT语句返回的数据不感兴趣，请关闭RecordSet（rds.Close），或者在读取所有行之前循环访问它。

As an aside, if you are never interested in the data returned by the select statement, only the row count, then it's more efficient not to return it. So if you can modify your SP, you could change it to:

顺便说一句，如果您从不对select语句返回的数据感兴趣，只对行计数感兴趣，那么返回它的效率会更高。因此，如果您可以修改SP，则可以将其更改为：

SELECT @returnval = COUNT(*) FROM type_table WHERE type1=@para1 and type2=@para2 and type3=@para3

#2

Have a try after removing SET NOCOUNT ON; from your procedure

删除SET NOCOUNT ON后试一试;从你的程序

I think this will help you. The affected row count will not returned if you set SET NOCOUNT ON; in procedure.

我想这会对你有所帮助。如果将SET NOCOUNT设置为ON，则不会返回受影响的行计数;在程序中。

#3

If you are not expecting a recordset as part of your stored procedure call, use cmd1->Execute(NULL, NULL, ADO::adExecuteNoRecords);

如果您不希望将记录集作为存储过程调用的一部分，请使用cmd1-> Execute（NULL，NULL，ADO :: adExecuteNoRecords）;

#1