Hey Guys,
I am trying to call a stored procedure that works fine in QueryAnalyzer, but I cannot get it to work on the web. I've been pulling my hair out with this for over a week, and could really use some help with this.

Okay first here is the error:
Microsoft VBScript runtime error '800a01c2'

Wrong number of arguments or invalid property assignment: 'Exec_sp_AddItem'

/testproc2.asp, line 6

------------------------------------------------------
Now heres the stored procedure:
CREATE procedure sp_AddItem
@i_Title varchar(50),
@i_CatId numeric,
@i_StatCD char(1),
@i_AuthId numeric,
@i_RateCD char(2),
@i_ComCD char(2),
@i_ShopCD char(1),
@i_URL varchar(255),
@i_Img varchar(75),
@i_Descr varchar(8000),
@i_Price money,
@i_ImgW char(3),
@i_ImgH char(3),
@i_CrDate datetime,
@io_ItemId int output,
@io_DetailId int output

as
Begin
-- Errors:
-- -1 Item Exists with ItemId Try Again
-- -2 No Such Item Type or Category

Declare @l_retCD int
Declare @i_TypeId int
Declare @i_TempAuth int

Select @l_retCD = 0
Select @i_TypeId = 0
Select @i_TempAuth = 0


-- ------------------------------------------------
-- Get Correct Type From Category
-- ------------------------------------------------
Begin
select @i_TypeId = TypeId from Category Where CatId = @i_CatId

If @i_TypeId < 0
Begin
Select @l_retCD = -2
Goto SPADDITEM_ERR
End


End
-- ------------------------------------------------
--Validate Author, if no author then use admin
-- ------------------------------------------------
Begin
Select @i_TempAuth = UserId From SiteUsers Where UserId = @i_AuthId
If @i_TempAuth = 0
Begin
Select @i_AuthId = 1
End
End


-- ------------------------------------------------
--Begin Insert Transaction of Data
-- ------------------------------------------------

Begin Transaction l_AddItem

INSERT INTO [VHEDesignscomDB].[dbo].[Items]
( [ItemTitle],
[ItemType],
[StatusLevel],
[CatId],
[AuthId],
[CrDate],
[RateOpt],
[ComOpt],
[ShopOpt])

VALUES
(@i_Title,
@i_TypeId,
@i_StatCD,
@i_CatId,
@i_AuthId,
@i_CrDate,
@i_RateCD,
@i_ComCD,
@i_ShopCD)



if @@ERROR != 0
begin
rollback tran l_AddItem
select @l_retCd = -500
goto SPADDITEM_ERR
end
else
begin
select @io_ItemId =@@IDENTITY
end

INSERT INTO [VHEDesignscomDB].[dbo].[ItemDetails]
( [ItemId],
[iPrice],
[iDescr],
[iImageUrl],
[iURL],
[ImgW],
[ImgH])

VALUES
( @io_ItemId,
@i_Price,
@i_Descr,
@i_Img,
@i_URL,
@i_ImgW,
@i_ImgH)

if @@ERROR != 0
begin
rollback tran l_AddItem
select @l_retCd = -500
goto SPADDITEM_ERR
end
else
begin
select @io_DetailId =@@IDENTITY
end



--
-- Commit the full transaction
--
commit Transaction l_AddItem

--
--Finish Insert and Return 0 For Success
--
return(@l_retCd)

SPADDITEM_ERR:
rollback tran l_AddItem
return(@l_retCd)
End
GO


---------------------------------------------------------------
And finally the code used to call the store procedure:

<%

Public Function Exec_sp_AddItem(ByVal stri_Title, ByVal dbli_CatId, ByVal stri_StatCD, ByVal dbli_AuthId, ByVal stri_RateCD, ByVal stri_ComCD, ByVal stri_ShopCD, ByVal stri_URL, ByVal stri_Img, ByVal stri_Descr, ByVal curi_Price, ByVal stri_ImgW, ByVal stri_ImgH, ByVal dtei_CrDate, ByVal intio_ItemId, ByVal intio_DetailId, ByRef lngRetVal, ByRef strErrDesc)
Dim strSQL
Dim objCmd
Dim objCn
Dim objRs

On Error Resume Next
Set objCmd = Server.CreateObject("ADODB.Command"
Set objCn = Server.CreateObject("ADODB.Connection"
Set objRs = Server.CreateObject("ADODB.Recordset"
strSQL = "sp_AddItem"

objCn.Open g_strConnectionString
With objCmd
.Commandtext = strSQL
.Commandtype = adCmdStoredProc
Set .ActiveConnection = objCn

.Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 0)
.Parameters.Append .CreateParameter("i_Title", adVarChar, adParamInput, 50, stri_Title)
.Parameters.Append .CreateParameter("i_CatId", adNumeric, adParamInput, 20, dbli_CatId)
.Parameters("i_CatId".NumericScale = 0
.Parameters("i_CatId".Precision = 18
.Parameters.Append .CreateParameter("i_StatCD", adVarChar, adParamInput, 1, stri_StatCD)
.Parameters.Append .CreateParameter("i_AuthId", adNumeric, adParamInput, 20, dbli_AuthId)
.Parameters("i_AuthId".NumericScale = 0
.Parameters("i_AuthId".Precision = 18
.Parameters.Append .CreateParameter("i_RateCD", adVarChar, adParamInput, 2, stri_RateCD)
.Parameters.Append .CreateParameter("i_ComCD", adVarChar, adParamInput, 2, stri_ComCD)
.Parameters.Append .CreateParameter("i_ShopCD", adVarChar, adParamInput, 1, stri_ShopCD)
.Parameters.Append .CreateParameter("i_URL", adVarChar, adParamInput, 255, stri_URL)
.Parameters.Append .CreateParameter("i_Img", adVarChar, adParamInput, 75, stri_Img)
.Parameters.Append .CreateParameter("i_Descr", adVarChar, adParamInput, 8000, stri_Descr)
.Parameters.Append .CreateParameter("i_Price", adCurrency, adParamInput, 21, curi_Price)
.Parameters.Append .CreateParameter("i_ImgW", adVarChar, adParamInput, 3, stri_ImgW)
.Parameters.Append .CreateParameter("i_ImgH", adVarChar, adParamInput, 3, stri_ImgH)
.Parameters.Append .CreateParameter("i_CrDate", adDBTimeStamp, adParamInput, 16, dtei_CrDate)
.Parameters.Append .CreateParameter("io_ItemId", adInteger, adParamInputOutput, 4, NULL)
.Parameters.Append .CreateParameter("io_DetailId", adInteger, adParamInputOutput, 4, NULL)
End With
With objRs
.CursorLocation = adUseClient
.Open objCmd, , adOpenDynamic, adLockReadOnly
Set .ActiveConnection = Nothing
End With
Set Exec_sp_AddItem = objRs
objCn.Close
Set objCn = Nothing

Set objCmd = Nothing

If Err <> 0 Then
lngRetVal = Err.Number
strErrDesc = Err.Description
End If
End Function


Dim strFunction

strFunction = Exec_sp_AddItem("VHE Designs", 3, "Y", 1, "Y", "y", "y", "www.vhedesignsweb.com", "www.vhedesignsweb.com/logo.gif", "None... This is just a test", 252.02, "25", "90", "12/12/01"


Write.Response(strFunction)

%>

Thanks again for any and all help, it is greatly appreciated!
-Greg





<company> VHE Designs</company>
<services> ASP, SQL, VB, eCommerce, Hosting</services>
<web> www.vhedesignsweb.com </web>
<phone> 908.832.3082 </phone>

Here's your problem:

@i_CatId numeric, <----
@i_StatCD char(1),
@i_AuthId numeric, <----

As far as I know ADO can't handle Numeric or Decimal values.

If this is just an ID, for which whole numbers are fine then then use a data type of <b>int</b>.

If you really need a decimal (numeric) value take a look at this:
p2p.wrox.com/archive/pro_vb/2000-08/6.asp

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo