从存储过程中返回一个值经典asp
作为存储过程的noob,我无法弄清楚如何让一个经典的ASP(VBScript)页面从存储过程返回一个值。我可以将数据写入表格,这只是检索我卡住的东西。从存储过程中返回一个值经典asp
如果我使用一个非常基本的例子。这是一个存储过程,应该在一个表中返回的记录数:
CREATE PROCEDURE [dbo].[SP_RecordCount]
AS
SELECT COUNT(*) FROM MyTable
什么经典的ASP代码(和什么样的变化对存储过程)我需要编写能在一个显示该值浏览器吗?一旦我明白如何获得这样的基本输出,我希望(!)能够建立在知识基础上。
谢谢。
见How to call SQL Server stored procedures from ASP
<%@ LANGUAGE="VBSCRIPT" %>
<!--#include virtual="/ASPSAMP/SAMPLES/ADOVBS.INC"-->
<HTML>
<HEAD><TITLE>Place Document Title Here</TITLE></HEAD>
<BODY>
This first method queries the data source about the parameters
of the stored procedure. This is the least efficient method of calling
a stored procedure.<BR>
<%
Set cn = Server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command")
cn.Open "data source name", "userid", "password"
Set cmd.ActiveConnection = cn
cmd.CommandText = "sp_test"
cmd.CommandType = adCmdStoredProc
' Ask the server about the parameters for the stored proc
cmd.Parameters.Refresh
' Assign a value to the 2nd parameter.
' Index of 0 represents first parameter.
cmd.Parameters(1) = 11
cmd.Execute
%>
Calling via method 1<BR>
ReturnValue = <% Response.Write cmd.Parameters(0) %><P>
<!-- ************************************************************ -->
Method 2 declares the stored procedure, and then explicitly declares
the parameters.<BR>
<%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "data source name", "userid", "password"
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "sp_test"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, _
adParamReturnValue)
cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, _
adParamInput)
' Set value of Param1 of the default collection to 22
cmd("Param1") = 22
cmd.Execute
%>
Calling via method 2<BR>
ReturnValue = <% Response.Write cmd(0) %><P>
<!-- ************************************************************ -->
Method 3 is probably the most formal way of calling a stored procedure.
It uses the canocial
<%
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "data source name", "userid", "password"
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
' Define the stored procedure's inputs and outputs
' Question marks act as placeholders for each parameter for the
' stored procedure
cmd.CommandText = "{?=call sp_test(?)}"
' specify parameter info 1 by 1 in the order of the question marks
' specified when we defined the stored procedure
cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, _
adParamReturnValue)
cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, _
adParamInput)
cmd.Parameters("Param1") = 33
cmd.Execute
%>
Calling via method 3<BR>
ReturnValue = <% Response.Write cmd("RetVal") %><P>
</BODY>
</HTML>
感谢您的回复,但我仍然遇到麻烦。我试图修改示例2来检索我的值,但出现以下错误: SQL Server的Microsoft OLE DB提供程序错误'80040e14' 过程SP_RecordCount没有参数且提供了参数。 recordcount.asp,第14行 – user2335705 2013-05-02 13:50:32
@ user2335705:如果您有任何代码处理'cmd.Parameters',则需要将其删除,因为您的SP不期望得到任何代码。 – shahkalpesh 2013-05-02 13:57:03
也许最彻底的方法是修改你的存储过程是这样的:
CREATE PROCEDURE [dbo].[SP_RecordCount]
@CountResult int OUTPUT
AS
SELECT @CountResult = COUNT(*) FROM MyTable
...这是服务器端的代码来调用它,并宣读值:
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = .... previously opened ADO connection here
cmd.CommandType = adCmdStoredProc 'be sure adCmdStoredProc constant is set in scope, or hardcode relevant integer instead'
cmd.CommandText = "SP_RecordCount"
cmd.Parameters.Refresh
cmd.Execute
Dim count
count = cmd.Parameters(1)
可能的重复http://*.com/questions/2689736/execute-stored-procedure-from- classic-asp – 2013-05-02 13:30:22