我真的不明白这里发生了什么。我正在使用 Excel VBA 连接到 SQL Server Express 数据库并返回 ADO 记录集。我一开始让它工作，但我的代码有点乱，所以我创建了一个新模块并复制了代码，随着我的进行整理。
现在，当我尝试运行 Sub 以返回记录计数时，出现错误“对象关闭时不允许操作”。代码在 MsgBox 行中断。
这是简化的代码:
Dim Server As String
Server = "ServerName"
Dim Database As String
Database = "DatabaseName"
Dim UserID As String
UserID = "UserID"
Dim Pwd As String
Pwd = "Password"
Dim StoredProcedure As String
StoredProcedure = "StoredProcedureName"
Dim conn As New ADODB.Connection
conn.ConnectionString = "Driver={SQL Server};Server=" & Server & "; Database=" & Database & "; UID = " & UserID & "; PWD=" & Pwd & ""
conn.Open
Dim Cmd As New ADODB.Command
Cmd.ActiveConnection = conn
Cmd.CommandText = StoredProcedure
Cmd.CommandType = adCmdStoredProc
Dim params() As String
ReDim Preserve params(4, 2)
params(0, 0) = "Param1"
params(1, 0) = CStr(adInteger)
params(2, 0) = CStr(adParamInput)
params(4, 0) = CStr(6)
params(0, 1) = "Param2"
params(1, 1) = CStr(adInteger)
params(2, 1) = CStr(adParamInput)
params(4, 1) = CStr(6)
params(0, 2) = "Param3"
params(1, 2) = CStr(adInteger)
params(2, 2) = CStr(adParamInput)
params(4, 2) = CStr(15)
Dim sParam4 as String
If Not sParam4 = "" Then
ReDim Preserve params(4, UBound(params, 2) + 1)
params(0, UBound(params, 2)) = "Param4"
params(1, UBound(params, 2)) = CStr(adChar)
params(2, UBound(params, 2)) = CStr(adParamInput)
params(3, UBound(params, 2)) = "1"
params(4, UBound(params, 2)) = sParam4
End If
Dim sParam5 as String
If Not sParam5 = "" Then
ReDim Preserve params(4, UBound(params, 2) + 1)
params(0, UBound(params, 2)) = "Param5"
params(1, UBound(params, 2)) = CStr(adChar)
params(2, UBound(params, 2)) = CStr(adParamInput)
params(3, UBound(params, 2)) = Len(sParam5)
params(4, UBound(params, 2)) = sParam5
End If
Dim Prm As ADODB.Parameter
Set Prm = New ADODB.Parameter
Dim i As Integer
For i = 0 To UBound(params, 2)
If params(1, i) = CStr(adChar) Then
Set Prm = Cmd.CreateParameter(params(0, i), CInt(params(1, i)), CInt(params(2, i)), CInt(params(3, i)))
Cmd.Parameters.Append Prm
Cmd.Parameters(params(0, i)).Value = params(4, i)
Else
Set Prm = Cmd.CreateParameter(params(0, i), CInt(params(1, i)), CInt(params(2, i)))
Cmd.Parameters.Append Prm
Cmd.Parameters(params(0, i)).Value = CInt(params(4, i))
End If
Next i
Dim rs As New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open Cmd, , adOpenStatic, adLockOptimistic
MsgBox ("Success! " & rs.RecordCount & " Records Returned!")
当我在 Locals 窗口中查看 rs 变量时，所有属性都被列为显示相同的错误消息。对我来说，似乎记录集正确打开，但随后立即关闭。
奇怪的是，原来的(凌乱的)子现在也不起作用，抛出同样的错误。我不认为我在那里改变了任何东西，只是将它复制到新的 sub 中。
我已经注释掉了旧 sub 所在的整个模块，以防万一发生任何类型的冲突变量情况。这没有任何区别。
我只是看不出它有什么问题!我做了很多研究和阅读，在我未经训练但热情的眼睛看来，一切都很好。
任何帮助将非常感激。
编辑:这是存储过程:
USE [MyDatabase]
GO
/****** Object: StoredProcedure [dbo].[MyProcedure] Script Date: 14/09/2015 11:39:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[MyProcedure]
-- Add the parameters for the stored procedure here
@Param1 int, @Param2 int, @Param3 int, @Param4 char(1), @Param5 varchar(20) = NULL, @Param6 varchar(20) = NULL, @Param7 varchar(20) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;
-- Insert statements for procedure here
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
SET @cols = '';
SELECT @cols = @cols + ',' + QUOTENAME([Field3]) FROM View1
WHERE Field2 = @Param2 AND Field1 = @Param1
SET @cols = STUFF(@cols,1,1,'');
IF @Param5 IS NOT NULL
SET @query = 'SELECT Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10, Field11, Field12, Field13, Field14,' + @cols + ' FROM
(
SELECT
Table1.Field1,
Table1.Field2,
Table1.Field3,
Table1.Field4,
Table1.Field5,
Table1.Field6,
Table1.Field7,
Table1.Field8,
Table1.Field9,
Table1.Field10,
Table1.Field11,
Table1.Field12,
Table1.Field13,
Table1.Field14,
Table2.Field2,
Table3.Field1
FROM Table3
LEFT OUTER JOIN Table2 ON Table3.Field1 = Table2.Field1
AND Table3.Field2 = Table2.Field2
LEFT OUTER JOIN Table4 ON Table3.Field4 = Table4.Field1
AND Table3.Field2 = Table4.Field3
RIGHT OUTER JOIN Table1 ON Table2.Field1 = Table1.Field1
AND Table2.Field2 = Table1.Field15
WHERE Table1.Field2 = ' + CAST(@Param3 AS char(1)) + '
AND Table1.Field12 = ''' + @Param5 + '''
AND Table1.Field15 = ' + CAST(@Param1 AS char(2)) + '
)
AS UP
PIVOT(MAX(Field2) FOR UP.ID IN (' + @cols + ')) AS PVT '
ELSE IF @Param6 IS NOT NULL
SET @query = 'SELECT Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10, Field11, Field12, Field13, Field14,' + @cols + ' FROM
(
SELECT
Table1.Field1,
Table1.Field2,
Table1.Field3,
Table1.Field4,
Table1.Field5,
Table1.Field6,
Table1.Field7,
Table1.Field8,
Table1.Field9,
Table1.Field10,
Table1.Field11,
Table1.Field12,
Table1.Field13,
Table1.Field14,
Table2.Field2,
Table3.Field1
FROM Table3
LEFT OUTER JOIN Table2 ON Table3.Field1 = Table2.Field1
AND Table3.Field2 = Table2.Field2
LEFT OUTER JOIN Table4 ON Table3.Field4 = Table4.Field1
AND Table3.Field2 = Table4.Field3
RIGHT OUTER JOIN Table1 ON Table2.Field1 = Table1.Field1
AND Table2.Field2 = Table1.Field15
WHERE Table1.Field2 = ' + CAST(@Param3 AS char(1)) + '
AND Table1.Field13 = ''' + @Param6 + '''
AND Table1.Field15 = ' + CAST(@Param1 AS char(2)) + '
)
AS UP
PIVOT(MAX(Field2) FOR UP.ID IN (' + @cols + ')) AS PVT '
ELSE IF @Param7 IS NOT NULL
SET @query = 'SELECT Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10, Field11, Field12, Field13, Field14,' + @cols + ' FROM
(
SELECT
Table1.Field1,
Table1.Field2,
Table1.Field3,
Table1.Field4,
Table1.Field5,
Table1.Field6,
Table1.Field7,
Table1.Field8,
Table1.Field9,
Table1.Field10,
Table1.Field11,
Table1.Field12,
Table1.Field13,
Table1.Field14,
Table2.Field2,
Table3.Field1
FROM Table3
LEFT OUTER JOIN Table2 ON Table3.Field1 = Table2.Field1
AND Table3.Field2 = Table2.Field2
LEFT OUTER JOIN Table4 ON Table3.Field4 = Table4.Field1
AND Table3.Field2 = Table4.Field3
RIGHT OUTER JOIN Table1 ON Table2.Field1 = Table1.Field1
AND Table2.Field2 = Table1.Field15
WHERE Table1.Field2 = ' + CAST(@Param3 AS char(1)) + '
AND Table1.Field14 = ''' + @Param7 + '''
AND Table1.Field15 = ' + CAST(@Param1 AS char(2)) + '
) AS UP
PIVOT(MAX(Field2) FOR UP.ID IN (' + @cols + ')) AS PVT '
ELSE
SET @query = 'SELECT Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10, Field11, Field12, Field13, Field14,' + @cols + ' FROM
(
SELECT
Table1.Field1,
Table1.Field2,
Table1.Field3,
Table1.Field4,
Table1.Field5,
Table1.Field6,
Table1.Field7,
Table1.Field8,
Table1.Field9,
Table1.Field10,
Table1.Field11,
Table1.Field12,
Table1.Field13,
Table1.Field14,
Table2.Field2,
Table3.Field1
FROM Table3
LEFT OUTER JOIN Table2 ON Table3.Field1 = Table2.Field1
AND Table3.Field2 = Table2.Field2
LEFT OUTER JOIN Table4 ON Table3.Field4 = Table4.Field1
AND Table3.Field2 = Table4.Field3
RIGHT OUTER JOIN Table1 ON Table2.Field1 = Table1.Field1
AND Table2.Field2 = Table1.Field15
WHERE Table1.Field2 = ' + CAST(@Param3 AS char(1)) + '
AND Table1.Field3 = ''' + @Param4 + '''
AND Table1.Field15 = ' + CAST(@Param1 AS char(2)) + '
) AS UP
PIVOT(MAX(Field2) FOR UP.ID IN (' + @cols + ')) AS PVT '
EXECUTE (@query)
Set NOCOUNT OFF;
END
我知道参数背后的逻辑不是很......合乎逻辑......但它的工作方式我现在需要它。这是我在工作时会改变的东西。
我还应该说，当我第一次发布问题时，我省略了处理 SP 参数的 VBA 代码部分。我现在刚下类，但回家后我会加进去。我不认为问题出在那里。我基本上处理参数的字符串数组并将它们附加到
Cmd 对象。编辑:我现在添加了用于创建参数并将它们传递给
Cmd 对象的代码。同样，这有点令人费解，但这是我当时脑子里想出来的，而且确实有效。一旦记录集被填充，我将重新审视逻辑。 最佳答案
请尝试以下代码:
Public Sub AdoTestConnection()
Dim conServer As ADODB.Connection
Dim rstResult As ADODB.Recordset
Dim strDatabase As String
Dim strServer As String
Dim strSQL As String
strServer = "YourServerName"
strDatabase = "YourDatabaseName"
Set conServer = New ADODB.Connection
conServer.ConnectionString = "PROVIDER=SQLOLEDB; " _
& "DATA SOURCE=" & strServer & "; " _
& "INITIAL CATALOG=" & strDatabase & "; " _
& "User ID=" & strLogin & ";" _
& "Password=" & strPassword
On Error GoTo SQL_ConnectionError
conServer.Open
On Error GoTo 0
Set rstResult = New ADODB.Recordset
strSQL = "set nocount on; "
strSQL = strSQL & "select 1 "
rstResult.ActiveConnection = conServer
On Error GoTo SQL_StatementError
rstResult.Open strSQL
On Error GoTo 0
If Not rstResult.EOF And Not rstResult.BOF Then
MsgBox "Connection worked. Server returned " & rstResult.Fields(0).Value
Else
MsgBox "Connection worked. The server did not return any value."
End If
Exit Sub
SQL_ConnectionError:
MsgBox "Problems connecting to the server." & Chr(10) & "Aborting..."
Exit Sub
SQL_StatementError:
MsgBox "Connection established. Yet, there is a problem with the SQL syntax." & Chr(10) & "Aborting..."
Exit Sub
End Sub
如果上述代码有效，那么您可以使用您的过程更改 SQL 命令，如下所示:
strSQL = "set nocount on; "
strSQL = strSQL & "exec StoredProcedureName @Parm1 = " & intValue1 & ", "
strSQL = strSQL & " @Parm2 = " & intValue2 & ", "
strSQL = strSQL & " @Parm3 = " & intValue3 & ", "
strSQL = strSQL & " @Parm4 = N'" & strValue1 & "', "
strSQL = strSQL & " @Parm5 = N'" & strValue2 & "', "
strSQL = strSQL & " @Parm6 = N'" & strValue3 & "', "
strSQL = strSQL & " @Parm7 = N'" & strValue4 & "' "
我强烈支持这种方法而不是您当前的方法，因为它更容易调试。如果您遇到 SQL 语法问题，您可以简单地请求
strSQL 的内容，如下所示:Debug.Print strSQL
然后您可以将结果复制到 SQL Server Management Studio (SSMS) 并在那里验证结果。
您甚至可能得出结论，您不想使用存储过程并将 SP 的全部内容复制到您的 VBA 代码中。
关于vba - ADO 记录集未在 VBA 中保持打开状态 - "Operation is not allowed when the object is closed"，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/32560747/