[RESOLVED] How do I get returns from Stored Procedures in Classic ASP

[ianbhenderson73]

Good evening

I'm trying to execute a stored procedure which will take in a bunch of values as variables. Everything's working as expected so far, but I need to know how I would go about getting the value that the Stored Proc is returning when it's complete.

The procedure in question is creating a new record in a database table, and returning the identity value. However I can't get my head around how to capture that identity value so that it can be presented to the end user.

The code that I'm using is as follows:

Set SQLDB=Server.CreateObject("ADODB.Connection")
SQLDB.Open Session("ODBC")

If Request.QueryString("Action") = "New" THEN
NewSystemName = "'" & Request.Form("Field_SystemName") & "'"
NewControlURL = "'" & Request.Form("Field_ControlURL") & "'"

If Request.Form("Field_Visible") = "on" THEN
NewVisible = 1
ELSE
NewVisible = 0
END IF

cmd = "exec dbo.ihsp_Dashboard_Admin_AddSystems " & NewSystemName & ", " & NewControlURL & ", " & NewVisible & ", '" & Session("Username") & "'"
SQLDB.Execute(cmd)

END IF

After the SQLDB.Execute(cmd) line, I would expect to put something in there to interrogate the command and retrieve the return value. However everything I've tried has been unsuccessful. However, the Stored Procedure is working inasmuch as a new record is being created.

TIA

[techgnome]

create a command object, set the commandtext to the name of the stored procedure... then add a parameter, with the input/output type of RETURNVALUE ... then add the rest of your parameters.... once done, command.Execute .... at that point your parameter that you marked as a return value, will have the value RETURNed by the SProc.

-tg

[ianbhenderson73]

Ok, so I think I've managed to get the parameters configured properly. I'm not clear on how I code the front-end page so that it catches the value being returned by the Stored Procedure.

The full code for the stored procedure is as follows:

Use ESS_Dashboard
Go
Create Procedure dbo.ihsp_Dashboard_Admin_AddSystems

@SystemName varchar (50),
@ControlURL varchar (500),
@Visible bit = 0,
@CreatedBy varchar (50),
@SystemID INT OUTPUT 

As

Begin
	Set NoCount On

	Insert into dbo.Systems
		(
		SystemName,
		ControlURL,
		Visible,
		CreatedBy,
		CreatedOn
		)
	Values
		(
		@SystemName,
		@ControlURL,
		@Visible,
		@CreatedBy,
		GetDate()
		)

	Select	@SystemID = @@IDENTITY

	Set NoCount Off
End

The code that I've written in the ASP page is as follows:

    NewSystemName = Request.Form("Field_SystemName")
    NewControlURL = Request.Form("Field_ControlURL")
    If Request.Form("Field_Visible") = "on" THEN NewVisible = 1 ELSE NewVisible = 0

    set objCommand = Server.CreateObject("ADODB.command")
    conn = Session("ODBC")
    objCommand.ActiveConnection = conn
    objCommand.CommandText = "ihsp_Dashboard_Admin_AddSystems"
    objCommand.CommandType = adCmdStoredProc
    objCommand.Parameters.Append objCommand.CreateParameter ("@SystemName", adChar, adParamInput, 50, NewSystemName)
    objCommand.Parameters.Append objCommand.CreateParameter ("@ControlURL", adChar, adParamInput, 500, NewControlURL)
    objCommand.Parameters.Append objCommand.CreateParameter ("@Visible", adBoolean, adParamInput, , NewVisible)
    objCommand.Parameters.Append objCommand.CreateParameter ("@CreatedBy", adChar, adParamInput, 50, Session ("UserName"))
    objCommand.Parameters.Append objCommand.CreateParameter ("@SystemID", adInteger, adParamOutput)', , NewRecordID)

    NewRecordID = objCommand.Parameters("@SystemID")
    objCommand.Execute

I'm reasonably sure that I'm doing something silly, but I've been staring at this for about 3 days now, so I'm frankly stumped.

TIA

[MarkT]

Try swapping these two lines

NewRecordID = objCommand.Parameters("@SystemID")
objCommand.Execute

should be

objCommand.Execute
NewRecordID = objCommand.Parameters("@SystemID")

[ianbhenderson73]

And just like that, it works and makes a whole lot more sense to boot.

Many thanks