CODEWITHSUNDEEP
oracle-databasevbscriptasp-classic
Audumbar
Audumbar

Reputation: 99

Error: Parameter Type is not supported in ADODB code of classic ASP

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

But I am receiving error as

Parameter Type is not supported at the line 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;

Can anyone help me in sorting out this issue

Upvotes: 1

Views: 1579

Answers (1)

user692942
user692942

Reputation: 16681

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.

See A: Classic ASP calling Oracle stored procedure with OraOleadb Driver

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

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.

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.

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.

Try changing this line

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

to

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

Useful Links

Upvotes: 1

Related Questions