错误:经典ASP的ADODB代码中不支持参数类型

问题描述

我已经编写了代码来插入用oracle pl/sql编写的调用参数化存储过程.我已经正确地给了所有参数，如下面的代码所示.

I have written code to insert call parameterized stored procedure written in oracle pl/sql. I have given all parameters properly as displayed in below code.

 function CallSp(str_id, ref_no, note, userId, strdatestamp, writtenDate)
   Dim strcon2 : set strcon2=server.createObject("ADODB.Connection")
   Dim strcmd2
   Dim sql2
   Dim ReturnVal
   strcon2.Open "Proper Connectionstring provided here"
   sql2 = "Fr_Store_Notes"

   Set strcmd2 = Server.CreateObject("ADODB.Command")
   Set strcmd2.ActiveConnection = strCOn2
   strcmd2.CommandText = sql2
   strcmd2.CommandType = 4
   strcmd2.Parameters.Refresh
   strcmd2.Parameters.Append strcmd2.CreateParameter("p_str_id", 12,1)
   strcmd2.Parameters("p_str_id") = str_id
   strcmd2.Parameters.Append strcmd2.CreateParameter("p_ref_no", 12,1)
   strcmd2.Parameters("p_ref_no") = ref_no
   strcmd2.Parameters.Append strcmd2.CreateParameter("p_UserId", 12,1)
   strcmd2.Parameters("p_UserId") = userId
   strcmd2.Parameters.Append strcmd2.CreateParameter("p_note", 12,1)
   strcmd2.Parameters("p_note") = note
   strcmd2.Parameters.Append strcmd2.CreateParameter("p_Datestamp", 12,1)
   strcmd2.Parameters("p_Datestamp") = strdatestamp
   strcmd2.Parameters.Append strcmd2.CreateParameter("p_WrittenDate", 12,1)
   strcmd2.Parameters("p_WrittenDate") = writtenDate
   strcmd2.Parameters.Append strCmd2.CreateParameter("p_return", 3, 2)
   strcmd2.Execute
   ReturnVal = strcmd2.Parameters("p_return").Value

   CallSp=ReturnVal
   set strCmd2=Nothing
   strCon2.close
end function

但是我收到错误消息

数据库存储过程如下所示，如果我们从数据库执行它，则可以正常工作

Database stored procedure is as like below and working fine if we execute it from database

create or replace
procedure Fr_Store_Notes (
  P_STR_ID IN VARCHAR2,
  p_Ref_no in VARCHAR2,
  P_UserId in VARCHAR2,
  P_Note IN VARCHAR2,
  P_datestamp IN VARCHAR2,
  p_WrittenDate IN VARCHAR2,
  p_return OUT number)
AS
BEGIN
--Expected Code Block is there and working fine
End;

有人可以帮我解决这个问题吗

Can anyone help me in sorting out this issue

推荐答案

更新:-经过一些研究(因为我不使用Oracle) ADODB不支持adVariant (它是12)，您应该改用adVarChar (它是200).

Update: - Apparently after a bit of research (as I don't work with Oracle) ADODB doesn't support adVariant (which is 12) and you should use adVarChar (which is 200) instead.

请参见 A:经典的ASP使用OraOleadb驱动程序调用Oracle存储过程

此问题解决后，剩下的答案可能仍然有意义.

Leaving the rest of the answer below as it's probably still relevant once this issue is fixed.

原因在于，一旦ADODB与连接定义的提供程序进行对话，通常会导致特定类型的数据不匹配.

The cause is of that particular error is usually a mismatch of data type once the ADODB talks to the provider defined by the connection.

仅查看与您的ADODB.Command对象相比的Oracle中的过程定义，我可以看到p_return参数似乎不正确.我在上一个答案中对.

Just looking at the procedure definition in Oracle in comparison to your ADODB.Command object I can see that the p_return parameter appears to be incorrect. I talk about this in a previous answer to a similar question.

根据数据类型映射 ( adInteger (它是3)的重要资源，它在Oracle中映射到Int而不是Number.相反，您应该使用adNumeric (它是131)来解决该特定错误.

According to Data Type Mapping (a great resource for Data Type Mapping in ADO) adInteger (which is 3) maps to Int in Oracle not Number. Instead, you should use adNumeric (which is 131) which should fix that particular error.

尝试更改此行

strcmd2.Parameters.Append strCmd2.CreateParameter("p_return", 3, 2)

到

strcmd2.Parameters.Append strCmd2.CreateParameter("p_return", 131, 2)

---

有用的链接

• A:在经典ASP中使用存储过程..执行并获取结果
• A:ADODB.Parameters错误'800a0e7c'参数对象定义不正确.提供了不一致或不完整的信息 (建议您学习如何在global.asa中使用METADATA以使ADO命名常量始终可用于ASP Web应用程序)

---

Useful Links

• A: Using Stored Procedure in Classical ASP .. execute and get results
• A: ADODB.Parameters error '800a0e7c' Parameter object is improperly defined. Inconsistent or incomplete information was provided (recommend this to learn how to use METADATA in global.asa to have ADO Named Constants always available to an ASP Web Application)

这篇关于错误:经典ASP的ADODB代码中不支持参数类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！