wrong number or types of arguments in call to 'SQLINSERTCOMPANYREP'

From: Phil Powell <soazine_at_erols.com>
Date: 13 Aug 2002 14:02:04 -0700
Message-ID: <1cdca2a7.0208131302.44f3ff8c_at_posting.google.com>

I am constantly getting this error in the attempt to use a working SearchProcedure function I wrote in ASP to call an Oracle stored procedure.

Following are the input parameters from the stored proc:

(

	strCompanyRepType		IN VARCHAR2,
	strCompanyRepFirstName 	IN VARCHAR2,
	strCompanyRepLastName 	IN VARCHAR2,
	strCompanyRepAddress1 	IN VARCHAR2,
	strCompanyRepAddress2 	IN VARCHAR2,
	strCompanyRepCity 		IN VARCHAR2,
	strCompanyRepStateProvince IN VARCHAR2,
	strCompanyRepZip 		IN VARCHAR2,
	strCompanyRepCountry	IN VARCHAR2,
	strCompanyRepPhone1 	IN VARCHAR2,
	strCompanyRepPhone2 	IN VARCHAR2,
	strCompanyRepEmail 		IN VARCHAR2,
	strCompanyRepFax 		IN VARCHAR2,
	strCompanyRepLink 		IN VARCHAR2,
	strCompanyRepTerritory 	IN VARCHAR2,

  intCompanyRepID OUT VARCHAR2 /* Story Server requires char type OUT parameters */
)

Following is the code snippet calling the working SearchProcedure function (note: this function is used as-is in several other calls in other templates; these other templates have identically-styled coding and they all work, this one doesn't!):

Dim hasSubmittedDB, companyRepObject, companyRepObjArray

   if hasSubmittedRep = 1 then
    ReDim companyRepObjArray(3)

        companyRepObjArray(0) = "SQLINSERTCOMPANYREP"    else
    ReDim companyRepObjArray(2)

        companyRepObjArray(0) = "SQLUPDATECOMPANYREP"    end if
   set companyRepObjArray(1) =
Server.CreateObject("Scripting.Dictionary")

   if hasSubmittedRep = 1 then set companyRepObjArray(2) = Server.CreateObject("Scripting.Dictionary")

   hasSubmittedDB = false
   if hasEditedRep = 1 then
    companyRepObjArray(1).Add "intCompanyRepID", Array(adNumeric, 38, CInt(Request.Form("companyRepID")))

   end if
   companyRepObjArray(1).Add "strCompanyRepType", Array(adVarChar, 255, Request.Form("repType"))

   companyRepObjArray(1).Add "strCompanyRepFirstName", Array(adVarChar, 255, Request.Form("firstname"))

   companyRepObjArray(1).Add "strCompanyRepLastName", Array(adVarChar, 255, Request.Form("lastname"))

   companyRepObjArray(1).Add "strCompanyRepAddress1", Array(adVarChar, 255, Request.Form("address1"))

   companyRepObjArray(1).Add "strCompanyRepAddress2", Array(adVarChar, 255, Request.Form("address2"))

   companyRepObjArray(1).Add "strCompanyRepCity", Array(adVarChar, 255, Request.Form("city"))

   companyRepObjArray(1).Add "strCompanyRepStateProvince", Array(adVarChar, 255, Request.Form("stateprovince"))

   companyRepObjArray(1).Add "strCompanyRepZip", Array(adVarChar, 255, Request.Form("zip"))

   companyRepObjArray(1).Add "strCompanyRepCountry", Array(adVarChar, 255, Request.Form("country"))

   companyRepObjArray(1).Add "strCompanyRepPhone1", Array(adVarChar, 255, Request.Form("phone1"))

   companyRepObjArray(1).Add "strCompanyRepPhone2", Array(adVarChar, 255, Request.Form("phone2"))

   companyRepObjArray(1).Add "strCompanyRepEmail", Array(adVarChar, 255, Request.Form("email"))

   companyRepObjArray(1).Add "strCompanyRepFax", Array(adVarChar, 255, Request.Form("fax"))

   companyRepObjArray(1).Add "strCompanyRepLink", Array(adVarChar, 255, Request.Form("website"))

   companyRepObjArray(1).Add "strCompanyRepTerritory", Array(adVarChar, 1000, Request.Form("territory"))

   if hasSubmittedRep = 1 then
    companyRepObjArray(2).Add "intCompanyRepID", Array(adVarChar, 4000)

   end if    

   on error goto 0
    set companyRepObject = SearchProcedure(dbConn, companyRepObjArray)

Is there anyone out there that can tell me the bare bones of calling an Oracle Stored Procedure using ASP???

 Function SearchProcedure (dbConn, procParamArray)    Dim key, hasInput, hasOutput, cmd, param, returnObj    set returnObj = Server.CreateObject("Scripting.Dictionary")  

   If UBound(procParamArray) = 1 Then
    hasInput = false
    hasOutput = false
   Elseif UBound(procParamArray) = 2 Then     hasInput = true
    hasOutput = false
   Elseif UBound(procParamArray) = 3 Then     hasInput = true
    hasOutput = true
   Else
    hasInput = false
    hasOutput = false
   End If  

   If hasInput Or hasOutput Then
    set cmd = Server.CreateObject("ADODB.Command")

    cmd.ActiveConnection = dbConn
    cmd.CommandText = procParamArray(0)
    cmd.CommandType = 4
 

    if hasInput then  

' Your array element (1) will be the input Dictionary object with
the key of the

' input parameter name(s) and its value an array consisting of:
' 0: input data type
' 1: input data size
' 2: input value
 

     for each key in procParamArray(1)
      set param = cmd.CreateParameter(key,

procParamArray(1).item(key)(0), 1, procParamArray(1).item(key)(1), procParamArray(1).item(key)(2))

      cmd.Parameters.Append param
     next
 

    end if  

    if hasOutput then  

' Your array element (2) will be the output Dictionary object
with the key of the

' output parameter name(s) and its value an array consisting of:
' 0: output data type
' 1: output data size
 

     for each key in procParamArray(2)
      set param = cmd.CreateParameter(key,
procParamArray(2).item(key)(0), 2, procParamArray(2).item(key)(1))
      cmd.Parameters.Append param
     next
 

    end if  

    cmd.execute   

    if hasOutput then

     for each key in procParamArray(2)
      if not IsNull(cmd(key)) then
       returnObj.Add key, CStr(cmd(key))
      else
       returnObj.Add key, ""
      end if
     next

    end if   

    set cmd = nothing  

   End If  

   set SearchProcedure = returnObj
   set returnObj = nothing  

  End Function

Thanx
Phil Received on Tue Aug 13 2002 - 23:02:04 CEST