在经典ASP中执行SQL存储过程

问题描述：

下面是ASP页面，此页面的onload我需要执行一个存储过程.我需要将参数传递到此存储过程.我从会话获取的此参数的值.存储过程应该在表中插入一条记录，而我不需要它来返回任何值. 无论我做什么，我都无法从ASP页执行它.请帮忙. 我在这里做什么错了?

Below is the ASP page, onload of this page I need to execute a stored procedure. I need to pass a parameter into this stored procedure. The value for this parameter I am getting from the Session. Stored procedure is supposed to insert a record into a table, and I don't need it to return any value back. I can't get it to execute from the ASP page, no matter what I do. Please help. What am I doing wrong here?

<% @Language=VBScript %>
<%Response.Buffer = true%>

<html>
<head>
</head>

<body>
<!--#include file = "connect.txt" -->    
<!--#include file= "adovbs.inc" -->

<%
Set conn = Server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command") 

'this will be my parameter
Dim strID
strID = Session("ID")

ConnectionStr =       "Provider=SQLOLEDB;Server=***;Database=***;Uid=***;Pwd=***;"
conn.Open  ConnectionStr

With cmd
.ActiveConnection = conn
.CommandType = adCmdStoredProc
.CommandText = "sp_Application_Insert"
.Parameters.Append cmd.CreateParameter("@TUID ", adVarchar, adParamInput, 200, strID)
.Execute


End With
Set cmd = Nothing
conn.close 
%>

</body>
</html>

答

您应该验证参数.....是否存在，以及其对于预期数据类型的正确值
您需要某种错误警报/记录.下面是修改后的代码.

You should validate the parameter.....that it exists, and its a correct value for the expected datatype
You need some kind of error alert/logging. Below is your code modified.

混合ADODB.Connection代码(数据层)和html/asp(表示层)使我的眼睛受伤.

Mixing ADODB.Connection code (data layer) and html/asp (presentation layer) makes my eyes hurt.

我下面所做的更改来自

https://support.microsoft.com/en-us/kb/300043

<% @Language=VBScript %>
<%Response.Buffer = true%>

<html>
<head>
</head>

<body>
<!--#include file = "connect.txt" -->    
<!--#include file= "adovbs.inc" -->

<%
On Error Resume Next
Set conn = Server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command") 

'this will be my parameter
Dim strID
strID = Session("ID")

ConnectionStr =       "Provider=SQLOLEDB;Server=***;Database=***;Uid=***;Pwd=***;"
conn.Open  ConnectionStr

With cmd
.ActiveConnection = conn
.CommandType = adCmdStoredProc
.CommandText = "sp_Application_Insert"
.Parameters.Append cmd.CreateParameter("@TUID ", adVarchar, adParamInput, 200, strID)
.Execute

if Err.Number <> 0 then

  Response.Write "An Error Has Occurred on this page!<BR>"
  Response.Write "The Error Number is: " & Err.number & "<BR>"
  Response.Write "The Description given is: " & Err.Description & "<BR>"

'' you can try the below too
'    Set objASPError = Server.GetLastError

 '   response.write "Category: " & objASPError.Category & _
  '   "ASPCode: " & objASPError.ASPCode & _
   '  "Number: " & objASPError.Number & _
    ' "ASPDescription: " & objASPError.ASPDescription & _
    ' "Description: " & objASPError.Description & _
    ' "Source: " & objASPError.Source

end if


End With
Set cmd = Nothing
conn.close 
%>

</body>
</html>

APPEND

https://msdn.microsoft.com/en-us/library/ms345484.aspx

GRANT EXECUTE ON OBJECT::dbo.sp_Application_Insert
    TO ***; /* where *** is your uid value in your connection string that you did not show */

在经典ASP中执行SQL存储过程

相关推荐