vba 在与请求的名称或序号对应的集合中找不到项目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24620324/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Item cannot be found in the collection corresponding to the requested name or ordinal
提问by user2989981
The code below is not working on this stored procedure. It works on queries, and another stored procedure.
下面的代码不适用于此存储过程。它适用于查询和另一个存储过程。
It will not debug.print the values for this stored procedure, and is returning an error "Item cannot be found in the collection corresponding to the requested name or ordinal"
它不会调试.打印此存储过程的值，并返回错误“在与请求的名称或序号对应的集合中找不到项目”
Dim x As ADODB.Connection
Set x = New ADODB.Connection
x.ConnectionString = "Provider=a;Server=b;Database=c;Trusted_Connection=yes;"
x.Open
Dim y As ADODB.Recordset
Set y = New ADODB.Recordset
x.CommandTimeout = 0
Set y = x.Execute("exec SP_storedprocedure 0")
Debug.Print y(0), y(1), y(2), y(3)**--the problem is here**
y.Close
Set y = Nothing
x.Close
Set x = Nothing
回答by Nekud
Make sure that the stored procedure is included in securables for the user you are accessing the database with. For this:
确保存储过程包含在您访问数据库的用户的安全对象中。为了这：
On your SSMS under security --> under users --> right click the user you are accessing --> Click Properties --> click on Securables tab and look at the list and make sure that stored procedure is included there. If not click "search" button --> check the "specific objects" radio --> click OK --> click "Object type" button on new popup --> Check Stored procedures checkbox --> click OK --> Make sure the stored procedure is listed and click OK --> Now after it is listed under securables --> Under Explicit tab look for "Excute" in Permission column --> Find the one that has "dbo" under Grantor Column and check the Grant checkbox and click OK.
在安全性下的 SSMS --> 用户下 --> 右键单击您正在访问的用户 --> 单击属性 --> 单击安全选项卡并查看列表并确保其中包含存储过程。如果没有单击“搜索”按钮 --> 选中“特定对象”单选框 --> 单击确定 --> 在新弹出窗口中单击“对象类型”按钮 --> 检查存储过程复选框 --> 单击确定 --> 制作确保存储过程已列出并单击“确定”--> 现在将其列在安全对象下后--> 在“显式”选项卡下，在“权限”列中查找“执行”--> 在“授予者”列下找到具有“dbo”的那个并检查授予复选框并单击确定。
This should solve the problem.
这应该可以解决问题。
回答by Ago
make sure your dataset is not in filtered. set your dataset's filter to false then close it, then use it. Or better use a clean dataset.
确保您的数据集未过滤。将数据集的过滤器设置为 false 然后关闭它，然后使用它。或者更好地使用干净的数据集。
回答by greg
does your stored procedure possible have a debug select in it? Possibly you are selecting the value of some variable as a debug statement. This would have the effect of returning two tables. your dataset will at the first one unless told otherwise.
你的存储过程可能有调试选择吗？可能您正在选择某个变量的值作为调试语句。这将具有返回两个表的效果。除非另有说明，否则您的数据集将位于第一个。
Do this in SSMS and make sure it is returning what you think it is returning.
在 SSMS 中执行此操作并确保它返回您认为返回的内容。
exec SP_storedprocedure 0
Also, to break down the problem, make 4 debug.print statements and see which one(s) break.
另外，要解决问题，请创建 4 个 debug.print 语句，然后查看哪些语句中断了。
Considering your statment to @MarkBalhoff about column count being zero, the other possibility is that you simply forgot to return your results at the end of the query. Are you building a temp table?
考虑到您对@MarkBalhoff 的关于列数为零的声明，另一种可能性是您只是忘记在查询结束时返回结果。你在建临时表吗？
the last (or close to last) statement in your sp should be
sp 中的最后一个（或接近最后一个）语句应该是
select * from #temp
similar to calculating the correct value in a function , then forgetting to return it.
类似于在函数中计算正确的值，然后忘记返回它。
