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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -