Retrieving a field from a SQL procedure

Expand Post »

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:

ASP Syntax (Toggle Plain Text)
 
		'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)
		'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:

ASP Syntax (Toggle Plain Text)
Create Procedure sp_CreateBasket
@idShopper int
AS
Insert into basket(idShopper) values(@idShopper)
Select idBasket = @@identity
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:

ASP Syntax (Toggle Plain Text)
idBasket = rsBasket("idBasket")
idBasket = rsBasket("idBasket")

And that is where the error comes in.

Quote ...

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

Similar Threads

Accessing a field in SQL in MS SQL
Update Binary Field in sql server 2000 in Visual Basic 4 / 5 / 6
Help-->SQL Statement in vb6 for getting total of Field in Visual Basic 4 / 5 / 6
How to send email attachments from PL/SQL procedure in Oracle
using sql procedure in Java

Matt12511

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Jul 2004

Permalink

Jul 31st, 2004

Re: Retrieving a field from a SQL procedure

Try this:

ASP Syntax (Toggle Plain Text)
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
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

Drew

Reputation Points: 25

Solved Threads: 7

Junior Poster

Offline

166 posts
since Apr 2004

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in ASP Forum Timeline: Search by Zip Radius

Next Thread in ASP Forum Timeline: getting email adress from logon_user

DaniWeb Message

Cancel Changes

User Name
Password