[thelist] capturing returned value from MS-SQL stored procedure

[Scott Dexter]

using output parameters with ASP is kinda a chore; you have to use the
ADODB.Command object and enumerate the parameters or refresh the query
to get everything populated. Not fun.

Can you change it around to return the PonID as a recordset? Then you
can access it like a recordset.

But, here's some idea of what working with OUTPUT parameters is like in
ASP:

		set DBCON = SetupSQL(Application("DBCON"))
 		Set cmd = Server.CreateObject("ADODB.command")
 		Set cmd.ActiveConnection = DBCON
 		cmd.CommandText = "procIOnlineAuthPwd"
 		cmd.CommandType = 4 'adCmdStoredProc
 		cmd.Parameters.Refresh 
 		' Instead of "cmd.Parameters.Refresh" we can use the
followings:
 		''cmd.Parameters.Append cmd.CreateParameter("RetVal",
adInteger, adParamReturnValue)    
 		'cmd.Parameters.Append cmd.CreateParameter("email",
adVarChar, adParamInput, 50, "e at ti3.com") 
 		'cmd.Parameters.Append cmd.CreateParameter("backoffice",
adInteger, adParamInput)
 		'cmd.Parameters.Append cmd.CreateParameter("tablename",
adVarChar, adParamInput, 20, "tbOnlineAuth_2090")  
 		'cmd.Parameters.Append cmd.CreateParameter("password",
adInteger, adParamOutput)   
 		cmd.Parameters("@email") = Email
 		cmd.Parameters("@backoffice")=1
 		cmd.Parameters("@branchID")=branchID
 		cmd.Parameters("@tablename")="tbOnlineAuth"
 		cmd.Parameters("@AuthStartDate")=startdate
 		cmd.Parameters("@AuthStopDate")=enddate
 		cmd.Parameters("@passwordIn")=0
 		cmd.Parameters("@passwordOut")=0
 		'Response.Write vbnewline + "Inserting via stored proc."
 		cmd.Execute
		CheckErr appid, Err, CStr(cmd.CommandText)
 		pwd=cmd.Parameters("@passwordOut")
		Set cmd = Nothing


--ASP has no idea what a @ is. It only has meaning in SQL Server....

Hope that helps
sgd
--
work: http://ti3.com/
non: http://thinksafely.org/ 

> -----Original Message-----
> From: spinhead [mailto:evolt at spinhead.com] 
> 
> CREATE PROCEDURE sp_pon_Add
> (
>   @po_ID int,
>   @RetVal int output
> )
> 
> =======================
>   strQuery = "DECLARE @PonID int EXECUTE sp_pon_Add " & 
> strPoID & ", @RetVal
> = @PonID OUTPUT"
>   Set AddPON = objConn.Execute(strQuery)
> 
> So now, how do I get the value of @PonID into a VBScript 
> variable? I tried
> this
>