Random ADODB.Recordset error "800a0cc1" ?

In one old ASP application we have suddenly started to get the following
error message:

ADODB.Recordset error '800a0cc1'

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


The mysterious thing about this is that the code works well most of the time.
Only about every 15-20 page hit results in the error message above! So in
about 95% of the times the same code is executed it works perfectly! And
this is with the same set of parameters!

The application is running on a W2K server connecting to an SQL2K named instance
on another machine. The ADODB call that gives the error message is made to
a stored procedure returning 2 recordsets (it is a paged result set). I have
tried debugging the application to see what kind of contents the recordset
has when the error occurs but without success. I managed to produce the error
when debugging with VS.NET 2003 but the recordset returned didn't give away
any clues. Neither EOF or BOF was true for the recordset even if it didn't
contain any rows...

When testing from Query Analyzer the stored procedure always returns the
correct result without any error.

Best regards,
Per Salmi

Re: Random ADODB.Recordset error "800a0cc1" ?

Per Salmi wrote:
> In one old ASP application we have suddenly started to get the
> following error message:
>
> ADODB.Recordset error '800a0cc1'
>
> Item cannot be found in the collection corresponding to the requested
> name or ordinal.
>
>
> any clues. Neither EOF or BOF was true for the recordset even if it
> didn't contain any rows...
>
> When testing from Query Analyzer the stored procedure always returns
> the correct result without any error.
>
Without seeing the code, I can do nothing but guess.

My guess is that the stored procedure is lacking a "SET NOCOUNT ON"
statement. Without that statement, informational messages can be generated
by the code in your procedure. These messages are sent to the client as
resultsets.

If that's not the case, you need to trap the error and log the parameter
values that produced the error, so you can consistently reproduce the
error.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Random ADODB.Recordset error "800a0cc1" ?

Ok, here are some more details...

> Without seeing the code, I can do nothing but guess.

Somewhat shortened down version of the SP, pretty basic temp-table solution
for paging:

CREATE PROCEDURE [dbo].[ProduktSok]
@fritext varchar(255) = '',
@sida int = 1,
@sidstorlek int = 10
AS
DECLARE @Start int, @End int
DECLARE @temptbl TABLE(
Row int IDENTITY(1,1) PRIMARY KEY,
[id] [int]
-- more colums...
)
SET @Start = (((@sida - 1) * @sidstorlek) + 1)
SET @End = (@Start + @sidstorlek - 1)
SET NOCOUNT ON
INSERT INTO @temptbl ( id )
SELECT Produkt.id as 'ID'
-- more columns selected...
FROM Produkt
JOIN ...
WHERE (titel like '%'+@fritext+'%')
ORDER BY Produkt.id DESC
SET NOCOUNT OFF

SELECT
CONVERT(int,CEILING( (COUNT(Row)/CONVERT(float,@sidstorlek)))) as Pages,
COUNT(Row) as TotalRows,
@Start as StartRow,
CASE
WHEN @End>COUNT(Row) THEN COUNT(Row)
ELSE @End
END as EndRow
FROM @temptbl

SELECT id as 'ID'
-- lots of columns
FROM @temptbl
WHERE (Row >= @Start) AND (Row <= @End)


The ASP code makes a call to the SP and then gets the Pages, TotalRows, StartRow
and EndRow values from the first resultset followed by a loop writing the
contents from the second resultset to an html table.


> My guess is that the stored procedure is lacking a "SET NOCOUNT ON"
> statement. Without that statement, informational messages can be
> generated by the code in your procedure. These messages are sent to
> the client as resultsets.

The NOCOUNT stuff is in there... however as I need result counters in the
first result set it is turned off again before that.

> If that's not the case, you need to trap the error and log the
> parameter values that produced the error, so you can consistently
> reproduce the error.

The parameters are the same for every call to the SP, but it still generates
the error message about 5% of the times it is called...

/Per

Re: Random ADODB.Recordset error "800a0cc1" ?

Per Salmi wrote:
> Ok, here are some more details...
>
>> Without seeing the code, I can do nothing but guess.
>
> Somewhat shortened down version of the SP, pretty basic temp-table
> solution for paging:
>
> CREATE PROCEDURE [dbo].[ProduktSok]
> @fritext varchar(255) = '',
> @sida int = 1,
> @sidstorlek int = 10
> AS
> DECLARE @Start int, @End int
> DECLARE @temptbl TABLE(
> Row int IDENTITY(1,1) PRIMARY KEY,
> [id] [int]
> -- more colums...
> )
> SET @Start = (((@sida - 1) * @sidstorlek) + 1)
> SET @End = (@Start + @sidstorlek - 1)
> SET NOCOUNT ON
> INSERT INTO @temptbl ( id )
> SELECT Produkt.id as 'ID'
> -- more columns selected...
> FROM Produkt
> JOIN ...
> WHERE (titel like '%'+@fritext+'%')
> ORDER BY Produkt.id DESC
> SET NOCOUNT OFF

Don't do this!!!

>
> The NOCOUNT stuff is in there... however as I need result counters in
> the first result set it is turned off again before that.

This will not prevent your counters from working. All SET NOCOUNT ON does is
prevent the informational messages from being generated. It has absolutely
no effect on aggregate functions in your queries and @@ROWCOUNT. Try it and
see.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Random ADODB.Recordset error "800a0cc1" ?

Ok, my homework on the NOCOUNT settings was obviously not properly done.

The problem seems to be gone, did a couple of hundred reloads/searches and
the error only occured the first time after changing the SP and no more after
that.

Thanks for the the quick response!

/Per

> Don't do this!!!
>
>> The NOCOUNT stuff is in there... however as I need result counters in
>> the first result set it is turned off again before that.
>>
> This will not prevent your counters from working. All SET NOCOUNT ON
> does is prevent the informational messages from being generated. It
> has absolutely no effect on aggregate functions in your queries and
> @@ROWCOUNT. Try it and see.