Retrieving a field from a SQL procedure

[Matt12511]

Hello all,

I have been working out of an older ASP/Visual Basic/SQL Server Developers Guide. My code is exactly as written in the book but I still can't seem to get the asp to collect the information from the Stored Procedure.

My asp page calls for the Procedure:

'check to see if a basket has been created
		if session("idBasket") = "" then
			set dbBasket = server.CreateObject("adodb.connection")
			set rsBasket = server.CreateObject("adodb.recordset")
			dbBasket.open "Provider=sqloledb;Data Source=(local);Initial Catalog=EcommerceDB;User Id=sa;Password=12511;"
			
			sql = "execute sp_CreateBasket " & session("idShopper")
			
			set rsBasket = dbBasket.Execute(sql)

The Stored Procedure is this simple:

Create Procedure sp_CreateBasket
@idShopper int
AS
Insert into basket(idShopper) values(@idShopper)
Select idBasket = @@identity

Then I call the information from my asp like this:

idBasket = rsBasket("idBasket")

And that is where the error comes in.

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

I have tested the Procedure in SQL Analyzer and it returns idBasket with the correct number.

Any ideas?

Thanks,

Matt

[Drew]

Try this:

if session("idBasket") = "" then
'Open DB
set dbBasket = server.CreateObject("adodb.connection")
dbBasket.open "Provider=sqloledb;Data Source=(local);Initial Catalog=EcommerceDB;User Id=sa;Password=12511;"
set rsBasket = server.CreateObject("adodb.recordset")
 
sql = "execute sp_CreateBasket idShopper=" & session("idShopper")
 
rsBasket.Open sql, dbBasket
idBasket = rsBasket("idBasket")
 
'Always close connections or server will suffer
rsBasket.Close
Set rsBasket = Nothing
 
'Close main connection
dbBasket.Close
set dbBasket = Nothing
End If