DaniWeb Forum Index > Web Development > ASP

Retrieving a field from a SQL procedure

Please support our ASP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jul 2004

Posts: 1

Reputation: Matt12511 is an unknown quantity at this point

Solved Threads: 0

Matt12511

Offline

Newbie Poster

Retrieving a field from a SQL procedure

Jul 29th, 2004

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:

 Help with Code Tags 
 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:

 Help with Code Tags 
 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:

 Help with Code Tags 
 ASP Syntax (Toggle Plain Text) 
idBasket = rsBasket("idBasket")
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

•

Join Date: Apr 2004

Posts: 167

Reputation: Drew is an unknown quantity at this point

Solved Threads: 7

Drew

Offline

Junior Poster

Re: Retrieving a field from a SQL procedure

Jul 31st, 2004

Try this:

 Help with Code Tags 
 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