上一篇
下一篇
asp調用帶輸出參數存儲過程的經典例子
作者:tank 日期:2004-12-12
asp調用帶輸出參數存儲過程的經典例子
<!--
作者:李萬峰
email: kvi@sina.com
本例通過表單提交數據,然後執行存儲過程,把表單數據存到數據庫中,然後返回輸出參數和返回值.先按照下面的內容,建好表和存儲過程,然後更改下麵的數據庫連接字串,再把下麵的程式放到你的虛擬目錄中就可以執行了!通過本例我相信你一定可以非常熟悉asp怎樣調用存儲過程.祝你成功!
********asp調用存儲過程演示********
數據庫如下:
id int identity
realname varchar(50)
memo nvarchar(4000)
存儲過程如下:
Create PROCEDURE addperson
(
@realname varchar(50),
@memo nvarchar(4000),
@id int output
)
AS
insert into person
(
realname,
memo
)
values
(
@realname,
@memo
)
select @id = @@identity
return @id+1
GO
<% @LANGUAGE = VBScript %>
<%
Function CheckStrInput(theString) '處理要輸入sql語句的字符串
CheckStrInput = Trim(theString)
If IsNull(theString) or isEmpty(theString) Then Exit Function
CheckStrInput = Replace(CheckStrInput,"'","''")
CheckStrInput = Replace(CheckStrInput,"--",")
CheckStrInput = Replace(CheckStrInput,"<","<")
CheckStrInput = Replace(CheckStrInput,">",">")
CheckStrInput = Replace(CheckStrInput,Chr(34),Chr(34)&Chr(34))
End Function
if request.ServerVariables("REQUEST_METHOD")="POST" then '如果提交表單
set conn=server.CreateObject("adodb.connection")
set cmd=server.CreateObject("adodb.command")
conn.Open "PROVIDER = SQLOLEDB.1;Persist Security Info = True;User ID = sa;Password = sa;Initial Catalog = TRK2003;Data Source = KVI;Connect Timeout = 1000"
set cmd.ActiveConnection=conn
adCmdSPStoredProc = 4 '變量必須聲名
adParamReturnValue = 4
adParaminput = 1
adParamOutput = 2
adInteger = 3
adVarChar = 200
adVarWChar = 202
cmd.CommandText="addperson" '存儲過程名稱
'說明：如果該存儲過程addperson沒有輸出參數@id,則上面的cmd.CommandText就可以這樣寫
'cmd.CommandText="addperson "& CheckStrInput(request.Form("realname")) & ","& CheckStrInput(request.Form("memo"))
'然後直接執行cmd.Execute(),就省略了以下很多的cmd.Parameters.Append
cmd.CommandType=adCmdSPStoredProc '類型為4
cmd.Parameters.Append cmd.CreateParameter("@return_value",adInteger,adParamReturnValue ) '定義返回變量
cmd.Parameters.Append cmd.CreateParameter("@realname",adVarChar,adParamInput,50,CheckStrInput(request.Form("realname")))
cmd.Parameters.Append cmd.CreateParameter("@memo",adVarWChar,adParamInput ,4000,CheckStrInput(request.Form("memo"))) 'Nvarchar型
cmd.Parameters.Append cmd.CreateParameter("@id",adInteger,adParamOutput) '定義輸出值
cmd.Execute()
Response.write "輸出值 @id="& cmd("@id") & "<br>" '輸出值
Response.write "返回值: "& cmd("@return_value") '返回值
Set Cmd.ActiveConnection = nothing '大掃除
Set Cmd = nothing
conn.Close
set conn=nothing
end if
%>
<HTML>
<HEAD>
<TITLE></TITLE>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=gb2312">
<LINK HREF="css/css.css" REL="stylesheet" TYPE="text/css">
</HEAD>
<BODY>
<script language="JavaScript" type="text/JavaScript">
function chk() {
if (form1.memo.value.length>4000){alert("備註不能超過4000!");form1.memo.select();return;}
form1.submit();
}
</script>
<form name="form1" method="post" action="">
<p>姓名
<input type="text" name="realname" maxlength="50">
</p>
<p> 備註
<textarea name="memo" cols="40" rows="10"></textarea>
</p>
<p>
<input type="button" name="Submit" value="提交" onClick="javascript:chk();">
</p>
</form>
</BODY>
</HTML>
文章來自: 
引用通告: 
Tags: