Problems with records returned from Stored Proc

[CJM]

I've created a Stored Procedure which adds a new record and updates some
more records and then returns the primary key for the added record.

The SP seems to work OK, but I'm having problems getting at the returned key
in my ASP code:

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

A common error, but in this case I can't see why... I output the SQL
instruction that is sent to the DB and have run it in Query Analyser and it
runs OK and returns a single row, with a single column - the new primary
key...

But when I try and access this in ASP, I get the error message.

Any ideas? It's bound to be something simple but I just cant see what it
it....

Thanks

Chris

Code snippets
---------------------
ASP:

sSQL = "Exec Orders_GenerateManifest"
response.Write sSQL

Set rsResult = oConn.Execute (sSQL)
response.Write rsResult("ManifestID")

iManifestID = rsResult("ManifestID")

Store Proc:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbrders_GenerateManifest Script
Date: 06/09/2005 12:10:12 ******/
ALTER Proc Orders_GenerateManifest
@CarrierID int,
@CollectionDay tinyint,
@NextDate smalldatetime
As

Set NoCount Off

Declare @ManifestID int

Set @ManifestID = 0

Begin Tran

/* Create Manifest record */
Insert into Manifests
(ManifestDate, CollectionDay, CarrierID)
Values
(@NextDate, @CollectionDay, @CarrierID)

Set @ManifestID = Scope_Identity()

If @ManifestID > 0
Begin
/* Update Order lines */
Update OrderDetail
Set ManifestID = @ManifestID
From OrderDetail D
inner join Orders O on O.OrderID = D.OrderID
inner join Customers C on C.CustomerID = O.CustomerID
Where D.CollectionDay = @CollectionDay
and C.CarrierID = @CarrierID
and (D.ManifestID is null or D.ManifestID = '')

If @@RowCount > 0
Commit tran
Else
Begin
Rollback Tran
Set @ManifestID = 0
End

End
Else
Begin
Rollback Tran
Set @ManifestID = 0
End

Select @ManifestID as ManifestID

Set NoCount Off



GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

[Andrew J. Kelly]

Do you really mean to have SET NOCOUNT OFF at the beginning or is it
supposed to be ON?

 

[Lars Broberg]

"SET NOCOUNT ON" in the beginning av the SP (instead of SET NOCOUNT
OFF). This gives you one returned recordset. Another (better?) way is to
use an output parameter to return the key.
Regards
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!

 

[Bob Barrows [MVP]]

Set NoCount Off

This statement is probably the problem. Remove it and see if it helps.

Bob Barrows

 

[CJM]

Do you really mean to have SET NOCOUNT OFF at the beginning or is it
supposed to be ON?

F&^k!
D£"n!
B&%(&r!

See, I told you it was something simple...

Thanks

Andrew

 

[Alejandro Mesa]

CJM,

Why are you setting "set nocount off" at the beginning of the sp?

ALTER Proc Orders_GenerateManifest
@CarrierID int,
@CollectionDay tinyint,
@NextDate smalldatetime
As

Set NoCount Off

it should be set "on", to avoid sql server returning the msg of rows
affected by the dml statement to the client app as if it were a recordset. If
you insist in doing this, then you have to use the method nextrecordset of
the recordset object to get traverse all recordsets sent by sql server.


AMB

 

[Bob Barrows [MVP]]

This statement is probably the problem. Remove it and see if it helps.

Oops, I missed the fact that you used "Off" at the beginning of the
procedure as well. Due to quickly skimming, I read it as "On".

I was talking about the final "nocount" statement in the procedure: I was
suspecting that it might cause an informational message to be sent ... even
if that's not the case, the statement is not needed. "SET NOCOUNT ON" only
applies while the procedure is in scope, and it only applies to the thread
in which the procedure is running.

Bob Barrows