存储过程不返回记录集

问题描述：

我被困在下面。我有一个存储过程，这需要时间和经理ID作为参数，以产生一个结果存储过程不返回记录集

CREATE PROCEDURE[ACT].[sp_getAllocations] 
    @dtmReportDate DATE, 
    @ManagerID BIGINT, 
    @type nvarchar(MAX) OUTPUT 
AS 
BEGIN 
    SELECT 
     ARCVTADMIN.tblEmployee.strFirstName + ' ' + ARCVTADMIN.tblEmployee.strSurname AS Name, 
     COUNT(ACT.tblActivity.employeeID) AS Workstate, 
     SUM(CASE WHEN ACT.tblactivity.statusTypeID = 4 AND ACT.tblActivity.dtmCompletedDate = @dtmReportDate THEN 1 ELSE 0 END) AS Completed, 
     SUM(CASE WHEN ACT.tblActivity.statusReasonID = 1 THEN 1 ELSE 0 END) AS NewLeads, 
     SUM(CASE WHEN ACT.tblContact.dtmLoggedDate = @dtmReportDate THEN 1 ELSE 0 END) AS Worked 
    FROM    
     ACT.tblContact 
    RIGHT OUTER JOIN 
     ACT.tblActivity ON ACT.tblContact.activityID = ACT.tblActivity.activityID 
    LEFT OUTER JOIN 
     ARCVTADMIN.tblEmployee ON ACT.tblActivity.employeeID = ARCVTADMIN.tblEmployee.employeeID 
           AND ACT.tblActivity.employeeID = ARCVTADMIN.tblEmployee.employeeID 
           AND ACT.tblActivity.employeeID = ARCVTADMIN.tblEmployee.employeeID 
           AND ACT.tblActivity.employeeID = ARCVTADMIN.tblEmployee.employeeID 
    GROUP BY 
     ARCVTADMIN.tblEmployee.strFirstName + ' ' + ARCVTADMIN.tblEmployee.strSurname, 
     ARCVTADMIN.tblEmployee.managerID 
    HAVING   
     (ARCVTADMIN.tblEmployee.managerID = @ManagerID) 
END

NB：ARCVTADMIN，ACT是架构名称时，我的SQL Server内等运行

此存储过程工作正常

Declare @D nVarchar(max); 
EXECUTE ACT.sp_getAllocations @dtmReportDate = '20161220', @ManagerID = 91, @[email protected] out 
Print @D

的下面是由存储过程产生时，我SQL Server中执行它的结果

Name   Workstate Completed NewLeads Worked 
=========================================================== 
Nikki Furnell  4999   0  3434   0 
Harriet Johnson  10   0   0   0 
Claire Rowe  138   0   17   0 
Carina Hughes  4   0   0   0

我有一个类模块的功能如下

Public Function getSPRecordset(dtmReportDate As String, managerID As Long) As ADODB.Recordset 
Dim strType As String 
Dim rst As ADODB.Recordset 
If Not (isConnectionOpen()) Then 
OpenConnection 
End If 

Set ADOCom = New ADODB.Command 
ADOCom.ActiveConnection = ADOConn 
ADOCom.CommandType = adCmdStoredProc 
ADOCom.CommandText = "[ACT].[sp_getAllocations]" 
ADOCom.Parameters.Refresh 

ADOCom.Parameters.Append ADOCom.CreateParameter("@dtmReportDate", adDate, adParamInput, 10, dtmReportDate) 
ADOCom.Parameters.Append ADOCom.CreateParameter("@ManagerID", adBigInt, adParamInput, 10, managerID) 
ADOCom.Parameters.Append ADOCom.CreateParameter("@type", adVarChar, adParamOutput, 10000, strType) 
Set rst = ADOCom.Execute 
Set getSPRecordset = rst 
End Function

我试图检索结果集，并将其分配给我的形式，我想下面。

Public Sub loadAllocation() 
Dim objSS As clsSQLServer 
Dim rst As ADODB.Recordset 
Set objSS = New clsSQLServer 
Set rst = objSS.getSPRecordset(Me.dtmReportDate, Me.managerID) 
Set Me.frmTeamDashboardWorkstate.Form.Recordset = rst ***Error Line **** 
set rst=nothing 
End Sub

任何帮助将不胜感激

从哪里调用loadAllocation？在这种情况下，“我”是什么？ frmTeamDashboardWork是一个子表单吗？您是否打算将这个记录集一直公开在整个网络上？如果没有，那么你应该使用'断开'的记录集。在设置之前使用'rst.CursorLocation = adUseClient'（可能需要将其作为'New ADODB.Recordset'来调暗）。 – Skippy

备注：您应该**不要**为存储过程使用'sp_'前缀。微软已经保留了这个前缀以供自己使用（参见*命名存储过程*）]（http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx），以及你将来有可能冒着名字冲突的风险。 [这对你的存储过程性能也是不利的]（http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix）。最好只是简单地避免使用'sp_'并将其他内容用作前缀 - 或者根本没有前缀！ –

@Skippy谢谢你的回应。下面是澄清... – Shallo

答

OK，我在想什么，你需要做的是...

首先，在一个单独的模块，您Public Sub loadAllocation()或者是它的父窗体的模块中？我建议在父窗体模块中将它作为Private Sub。

其次，您需要在父窗体的模块中声明第一个模块级变量。它需要在loadAllocation()运行后保持在范围内。因此，在模块的右上角，在任何Sub或函数的定义，并不仅仅是Option Compare Database和Option Explicit后，你的程序中加入

Private rst As ADODB.Recordset

然后我会重新代码

Private Sub loadAllocation() 
On Error GoTo PROC_ERR 

    Dim objSS As New clsSQLServer 

    Set rst = New ADODB.Recordset 
    rst.CursorLocation = adUseClient 
    Set rst = objSS.getSPRecordset(Me.dtmReportDate, Me.managerID) 
    'You could put some Debug.Print statements in here just to check that rst has retrieved some data' 
    If rst.State = adStateOpen Then 
     Set Me.frmTeamDashboardWorkstate.Form.Recordset = rst 
     frmTeamDashboardWorkstate.Form.Requery 
     rst.ActiveConnection = Nothing 
    End If 

PROC_EXIT: 
    'Put any clean-up code in here' 
    Exit Sub 

PROC_ERR: 
    'Put your preferred error handling code in here' 
    Resume PROC_EXIT 

End Sub

制作当您关闭父窗体（在Form_Unload事件中）时，确保清理并且Set rst = Nothing。

答

您使用的是32位还是64位Access？如果你使用32位，那么bigint是64位，32位访问不能处理它。

存储过程不返回记录集

相关推荐