Question about a stored procedure

[Magnus53]

Hi guys,

I have a confusing problem. I have a stored procedure that gets Consultant data from the db.The problem is that the procedure that doesn't work in all parts of the code. For example when printing, the procedure works fine. When retriving the data to view on screen(or doing any other function) the procedure will crash, givng me this error message

"Item can't be found in the collection corresponding
to the requested name or ordinal"

does anyone have any idea why it would work in one part of the program and not another?

the call to the procedure is like this

devdb.GetConsultant mvarConsID

I can post more code if needed.

Thanks for looking.

 

[Magnus53]

I forgot to mention that I'm using SQL Server 2000, personal(just on my dev computer) and that the procedure works fine using the Query Analyzer.

thanks again

 

[CCLINT]

It does not have to do with the stored proceedure or sql statement itself.
Sounds like a flaw in that GetConsultant proceedure, or the stored proceedure has changes in field names, or number of fields, and is then different to what some VB proceedure is looking for.
The error means that the field name or ordinal position used to retreive that field's data, or whatever, doesn't exist in the recordset, not necessarily that the field doesn't exist in the underlying table, or the query itself is wrong:

rs.ActiveConnection = Conn
rs.Source = "Select Field1, Field2 From Table1"
rs.Open

Debug.Print rs.Fields(10).Value
or
Debug.Print rs.Fields(Field10).Value

This would also be the case when using an alias field name:

rs.ActiveConnection = Conn
rs.Source = "Select Field1 As X, Field2 As Y From Table1"
rs.Open

Debug.Print rs.Fields(Field1).Value