When working with a resultset in ASP and you do this:
<%
...
Response.Write(rs("columnName"))
%>
... you may get the following error:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
... even though columnName is clearly being returned in the resultset when you run the procedure from Query
Analyzer.
Sometimes this is because you've misspelled the column name, referred to an aggregate without using an alias
(see Article #2159), referenced a column name that appears more than once in the SELECT list (e.g. a JOIN
between two tables that have a common column), or even referenced the wrong resultset in the case of multiple
resultsets. If you are having one of these problems, you can correct them quite easily by referencing the column
correctly, or the correct recordset. If you have a JOIN that has two columns with the same name, you probably have
a design issue because (a) if the columns contain the same value, you don't need both in the resultset; and (b) if
the columns don't contain the same value, then they have different meanings, and therefore should have distinct
names.
But most often it is because you're using a stored procedure, and ADO is incorrectly interpreting messages in QA's
messages tab, for example:
1 row(s) affected
... as a resultset, however there are no columns in the data pane, so it is useless except for debugging within Query
Analyzer.
You can correct this behavior by adding the following to the top of your procedure:
SET NOCOUNT ON
This prevents the messages from factoring into your ASP code.
If your database code is not changeable, you might experiment with adding set rs = rs.nextRecordset() lines until
you hit the first 'real' resultset.
The use of SET NOCOUNT ON is demonstrated in Article #2201 ...
   _{Your Comment}