Item cannot be found in the collection/ordinal

[rvancleef]

I guess one should never go on vacation! A week ago I wrote the following stored procedure and it successfully returned the new/updated record:

CREATE PROCEDURE proc_SetProjAssignment
@ProjID int,
@ITStaff varchar(50),
@EstManHours smallint
AS
/* Search for existing records with both Staff ID and Project ID if non then insert */
IF (SELECT COUNT(ProjID) FROM Assignment WHERE StaffID=@ITStaff AND Assignment.ProjID=@ProjID)<1
BEGIN
INSERT INTO Assignment (ProjID,StaffID,EstHours)
VALUES (@ProjID,@ITStaff,@EstManHours)
END
/* Else Do an update */
ELSE
BEGIN
UPDATE Assignment
SET EstHours=@EstManHours
WHERE ProjID=@ProjID AND StaffID=@ITStaff
END
SELECT Assignment.ProjID,Assignment.StaffID,Assignment.EstHours as fHours
FROM Assignment
WHERE StaffID=@ITStaff AND Assignment.ProjID=@ProjID
GO

As you can see, it determines if an INSERT or UPDATE is necessary and then should return the new/modified record. This works fine in the Query Analyzer (including the return of the new/updated record).

However, when calling the SP from an ASP page, the Add/Update function works (I have checked and confirmed that the record is added/updated in the actual DB table) but I receive an error that the Requested Ordinal is not found. I have triple checked that the ASP refers to the correct Column names:
<%= rsAssignment(&quot;ProjID&quot %>
rsAssignment(&quot;StaffID&quot
<%= rsAssignment(&quot;fHours&quot %>

Any thoughts about why the ASP is failing to recognize the record returned in the final SELECT? Could something have changed within the environment?

Thanks!

 

[JamesLean]

Try adding

SET NOCOUNT ON

to the top of the SP.

--James

 

[rvancleef]

Yep. That was the trick I got from the SQL Server forum. Thanks @!!!