无法在与请求的名称或序号对应的集合中找到项目 [英] Item cannot be found in the collection corresponding to the requested name or ordinal

问题描述

由于某种原因，下面的代码不工作在这个存储过程。我已经测试它的查询和另一个存储过程，它的工作原理。由于某种原因，它不会debug.print这个存储过程的值，并返回一个错误无法找到对应于请求的名称或序数的集合

For some reason the below code is not working on this stored procedure. I have tested it on queries, and another stored procedure, and it works. For some reason 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

推荐答案

你的存储过程可能有一个调试选择吗？可能你选择一些变量的值作为调试语句。这将有返回两个表的效果。您的数据集将在第一个，除非另有说明。

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.

在SSMS中执行此操作，并确保它返回您认为返回的内容。

Do this in SSMS and make sure it is returning what you think it is returning.

exec SP_storedprocedure 0

b $ b

此外，为了解决问题，make 4 debug.print语句，看看哪个断点。

Also, to break down the problem, make 4 debug.print statements and see which one(s) break.

考虑到您对@MarkBalhoff的列计数为零的判断，另一种可能性是，您只是在查询结束时忘记返回结果。你正在构建临时表吗？

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?

您的sp中的最后一个（或接近尾声）语句应为

the last (or close to last) statement in your sp should be

select * from #temp

类似于计算函数中的正确值，然后忘记返回。

similar to calculating the correct value in a function , then forgetting to return it.

这篇关于无法在与请求的名称或序号对应的集合中找到项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！