ASP recordset annoyance

OK I have been running a piece of ASP quite satisfactorily on a server, and now I have moved it to a new .net enabled server it has broken. Basically I am running a stored procedure that performs an update and then Selects the ID of the updated record.

However on the .net server the record set doesn't appear to have the select statements result in it.

As I said before this code DOES work on another server. Please find the code below:

--------------------------------
MSSQL: Stored Procedure
--------------------------------

CREATE PROCEDURE addHBHouse
@NAME varchar(255),
@EFF int,
@COST int
AS
INSERT INTO HBHouses (name, efficiency, cost, uploaded, uploader)
VALUES (@NAME, @EFF, @COST, GetDate(), 'Anonymous')
SELECT @@IDENTITY AS newHouse
GO

--------------------------
ASP Code: "VBScript"
--------------------------

set connection=server.createObject("adodb.connection")
connection.open ****Connection String*****
set answer = connection.execute("EXEC addHBHouse '"& name & "', " & eff & ", " & cost
ID = answer("newHouse")

--------------------------

And it claims on the "ID =" line:

'ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.'



If I remove the SELECT @@IDENTITY from the stored procedure and add it as another query then it works fine, but I need a solution that doesn't require me putting ALL my stored procedures into single queries

A little help please someone?

Thanks

Its possibly worth mentioning that when I use the Query Analyser it DOES return a field called newHouse with one record in it.

Horray!

After a whole morning of searching I have come back from Lunch to find the solution.

URL

Horray for Ken!

BTW, I am now voting yes in my poll