从ASP调用mysql的SP产生错误
问题描述:
当我打过电话,从传统的ASP MySQL的存储过程,我得到这个错误:从ASP调用mysql的SP产生错误
OUT或INOUT参数5例行spAddContactUsEmail不前的变量或新的伪变量触发
下面是SP(我可以从MySQL工作台成功地调用):
CREATE DEFINER=`root`@`localhost` PROCEDURE `spAddContactUsEmail`(
In vcName varchar(50),
In vcPhone varchar(50),
In vcEmail varchar(50),
In txtComments text,
Out intNewID int
)
BEGIN
Insert Into contact_us (`Name`, Phone, Email, Comments, DateCreated)
Values (vcName, vcPhone, vcEmail, txtComments, now());
Set intNewID = Last_Insert_ID();
END
这里是ASP代码:
Dim con
Dim cmd
Set con = Server.CreateObject("ADODB.Connection")
con.Open "connection string"
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spAddContactUsEmail"
cmd.Parameters.Append cmd.CreateParameter("vcName", adVarChar, adParamInput, 50, "John Doe")
cmd.Parameters.Append cmd.CreateParameter("vcPhone", adVarChar, adParamInput, 50, "333-4444")
cmd.Parameters.Append cmd.CreateParameter("vcEmail", adVarChar, adParamInput, 50, "[email protected]")
cmd.Parameters.Append cmd.CreateParameter("txtComments", adLongVarChar, adParamInput, Len("hey"), "hey")
cmd.Parameters.Append cmd.CreateParameter("intNewID", adInteger, adParamReturnValue)
cmd.Execute
错误发生在cmd.Execute行上。提前致谢。
答
变化adParamReturnValue到adParamOutput:
<%
cmd.Parameters.Append cmd.CreateParameter("intNewID", adInteger, adParamOutput, 4)
%>
包含错误消息的详细信息通过'Execute'方法返回。 –
错误消息是:-2147467259,[MySQL] [ODBC 5.1驱动程序] [mysqld-5.7.17-log] OUT或INOUT参数5用于例程spAddContactUsEmail不是变量或BEFORE触发器中的NEW伪变量 – hobster
似乎您需要在这里使用'adParamOutput'设置输出参数:'cmd.Parameters.Append cmd.CreateParameter(“intNewID”,adInteger,adParamOutput,adParamReturnValue)'。 –