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.
Best Answer
Try
You should also avoid
.Refresh
if you know the parameter details as it involves a trip back to the server.