Execute Stored Procedure from Classic ASP
- by Jaco Pretorius
For some fantastic reason I find myself debugging a problem in a Classic ASP page (at least 10 years of my life lost in the last 2 days).
I'm trying to execute a stored procedure which contains some OUT parameters. The problem is that one of the OUT parameters is not being populated when the stored procedure returns. I can execute the stored proc from SQL management studio (this is 2008) and all the values are being set and returned exactly as expected.
declare @inVar1 varchar(255)
declare @inVar2 varchar(255)
declare @outVar1 varchar(255)
declare @outVar2 varchar(255)
SET @inVar2 = 'someValue'
exec theStoredProc @inVar1 , @inVar2 , @outVar1 OUT, @outVar2 OUT
print '@outVar1=' + @outVar1
print '@outVar2=' + @outVar2
Works great. Fantastic. Perfect. The exact values that I'm expecting are being returned and printed out.
Right, since I'm trying to debug a Classic ASP page I copied the code into a VBScript file to try and narrow down the problem.
Here is what I came up with:
Set Conn = CreateObject("ADODB.Connection")
Conn.Open "xxx"
Set objCommandSec = CreateObject("ADODB.Command")
objCommandSec.ActiveConnection = Conn
objCommandSec.CommandType = 4
objCommandSec.CommandText = "theStoredProc "
objCommandSec.Parameters.Refresh
objCommandSec.Parameters(2) = "someValue"
objCommandSec.Execute
MsgBox(objCommandSec.Parameters(3))
Doesn't work. Not even a little bit. (Another ten years of my life down the drain) The third parameter is simply NULL - which is what I'm experiencing in the Classic ASP page as well.
Could someone shed some light on this? Am I completely daft for thinking that the classic ASP code would be the same as the VBScript code? I think it's using the same scripting engine and syntax so I should be ok, but I'm not 100% sure.
The result I'm seeing from my VBScript is the same as I'm seeing in ASP.
