从经典 ASP 执行存储过程 [英] Execute Stored Procedure from Classic ASP

问题描述

出于某种奇妙的原因，我发现自己正在调试经典 ASP 页面中的问题(在过去 2 天里，我的生命中至少失去了 10 年).

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).

我正在尝试执行一个包含一些 OUT 参数的存储过程.问题是存储过程返回时未填充其中一个 OUT 参数.我可以从 SQL 管理工作室(这是 2008 年)执行存储过程，并且所有值都按照预期设置和返回.

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.

是的，因为我正在尝试调试经典 ASP 页面，所以我将代码复制到 VBScript 文件中以尝试缩小问题范围.

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.

这是我想出的:

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))

不起作用.一点儿都没有.(我生命中的又一个十年白费了)第三个参数只是 NULL - 这也是我在 Classic ASP 页面中遇到的情况.

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.

有人可以对此有所了解吗?我是否认为经典的 ASP 代码与 VBScript 代码相同?我认为它使用相同的脚本引擎和语法，所以我应该没问题，但我不是 100% 确定.

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.

我从 VBScript 中看到的结果与我在 ASP 中看到的结果相同.

The result I'm seeing from my VBScript is the same as I'm seeing in ASP.

推荐答案

尝试

With objCommandSec
 Set .ActiveConnection = Conn
 .CommandType = 4
 .CommandText = "theStoredProc"
 .Parameters.Append .CreateParameter("@inVar1", 200, 1, 255, VALUE1)
 .Parameters.Append .CreateParameter("@inVar2", 200, 1, 255, VALUE2)
 .Parameters.Append .CreateParameter("@outVar1", 200, 2, 255)
 .Parameters.Append .CreateParameter("@outVar2", 200, 2, 255)

 .Execute

 Response.Write .Parameters(3).Value
End With 

如果您知道参数详细信息，您也应该避免使用 .Refresh，因为它涉及返回服务器的行程.

You should also avoid .Refresh if you know the parameter details as it involves a trip back to the server.

这篇关于从经典 ASP 执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！