CODEWITHSUNDEEP
exceldatabasevbadatabase-connectionado
user2989981
user2989981

Reputation: 89

Item cannot be found in the collection corresponding to the requested name or ordinal

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

Upvotes: 1

Views: 14765

Answers (3)

greg
greg

Reputation: 1873

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. 

exec SP_storedprocedure 0

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

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?

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.

Upvotes: 0

Ago
Ago

Reputation: 765

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.

Upvotes: 0

Nekud
Nekud

Reputation: 11

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.

This should solve the problem.

Upvotes: 0

Related Questions