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

2022-01-16 00:00:00 asp-classic oracle vbscript

我已经编写了代码来插入用 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

但我收到错误

strcmd2.Execute

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

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;

谁能帮我解决这个问题

推荐答案

更新: - 显然经过一些研究(因为我不使用 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:使用 OraOleadb 驱动程序调用 Oracle 存储过程的经典 ASP

将其余答案留在下面，因为一旦此问题得到解决，它可能仍然相关.

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.

仅查看 Oracle 中的过程定义与您的 ADODB.Command 对象相比，我可以看到 p_return 参数似乎不正确.我在 previous answer 对 类似问题.

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.

根据数据类型映射 (ADO 中数据类型映射的绝佳资源) adInteger (即 3) 映射到 Int 在 Oracle 中不是 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 error '800a0e7c' 参数对象定义不正确.提供了不一致或不完整的信息 (推荐此以了解如何使用 global.asa 中的 METADATA 使 ADO 命名常量始终可用于 ASP网络应用)

    • 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)

相关文章