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 dbo.Orders_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
--
cj*******@REMOVEMEyahoo.co.uk
[remove the obvious bits]
