Item cannot be found in the collection - Error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Item cannot be found in the collection - Error

Author

Topic

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-08 : 14:19:51

I am getting this error:

ADODB.Recordset error '800a0cc1'

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

/busmgmt/bma_dev/ScidRequestUpdate.asp, line 71

line 71 is "response.write rs("errorCount")"

when i run this following code:

-------------------------------------
stored procedure
------------------------------------
create spupdaterecords
@input1 int
as
Declare @errorCount
begin tran

Set @errorCount = 0

if @input1 = 1
update table set field = 0
else
update table set field = -1

if @@error <> 0
Begin
rollback tran
Set @errorCount = @@error
End

select @errorCount

Commit tran
Go

------------------------------------------------
asp
------------------------------------------------

set rs = createobject("adodb.recordset")
sql = "spupdaterecords 1"
rs.open sql, conn

response.write rs("errorCount")

Can someone please tell me where iam wrong.

Thanks in advance,
Nitu

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-08 : 14:23:17

didn't we already went through this??

Go with the flow & have fun! Else fight the flow

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-08 : 14:31:31

I am getting the error even after i changed the code.

Thats why iam posting again. Can u please tell me what wrong now in the code.

Thanks a lot for your help,
Nitu

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-08 : 14:33:26

select @errorCount as errorCount

Go with the flow & have fun! Else fight the flow

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-08 : 14:45:09

No, its still giving me the same error. any other suggestions.

How do you access an output parameter from the stored procedure in asp.

Thanks a lot in advance.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-08 : 14:48:03

put
set nocount on
at the beggining fo your sproc

Go with the flow & have fun! Else fight the flow

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-08 : 15:04:48

Thank u Thank u Thank u Thank u Thank u Thank u Thank u ,.........

Thanks a lot, it worked.

I have a question, this procedure returns me error number if any error occured during transaction. Is that right!

Thanks,
Nitu

VIG
Yak Posting Veteran

86 Posts

Posted - 2006-03-08 : 15:10:20

create spupdaterecords
@input1 int
as
Declare @errorCount
begin tran

Set @errorCount = 0

if @input1 = 1
update table set field = 0
else
update table set field = -1

if @@error <> 0
Begin
rollback tran
Set @errorCount = @@error
End

select @errorCount as errorCount

Commit tran
Go

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-08 : 15:19:06

When does the stored procedure return an error value other than 0.

in the give example, say input was a bit.

thanks in advance,
nitu

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-08 : 15:21:39

I am asking because, i wanted to test it for errors. Since if i get an errorCount>0 i should not allow other procedures to run.

Thanks in advance,
Nitu

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-08 : 15:45:14

Can i do this!!!

create spupdaterecords
@input1 int
as
Declare @errorCount
begin tran

Set @errorCount = 0

if @input1 = 1
update table set field = 0
else
update table set field = -1

if @@error <> 0
Begin
rollback tran
Set @errorCount = @@error
End

insert into tab2 (field1, field2, field3) select field1, field2, field3 where field4 = @input

if @@error <> 0
Begin
rollback tran
Set @errorCount = @@error
End

select @errorCount as errorCount

Commit tran
Go

Can i Rollback the transaction for every query or, i have to do it only at the end of the procedure.

thanks a lot,
Nitu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources