ASP stored procedures call error

[stevenfagan]

I am having a problem calling a stored procedure from a sql database that inserts variables into a database.

The stored procedure is:

PROCEDURE [dbo].[sp_test_table_testInsert]
@NameStr varchar(50),
@Qualifier bit,
@Notes varchar(50)
AS
BEGIN
insert into test_table
(NameStr, Qualifier, Notes)
values
(@NameStr, @Qualifier, @Notes)
END

the call I have on the asp page is:

dim sp, rs
set sp = new storedProcedure
call sp.setup(cnStingerAthletics, "sp_test_table_testInsert")
call sp.addParam("@NameStr", 200, 1,,"Stuff and Things")
call sp.addParam("@Qualifier", 11, 1,,1)
call sp.addParam("@Notes", 200, 1,,"Nothing and something, but definitely not something in between")
set rs = sp.callStoredProcedure
set sp = nothing

I created a class in asp that I use in the previous code, here it is:

Class storedProcedure
private cmd

'Constructor
Private Sub Class_Initialize
set cmd = server.CreateObject("ADODB.Command")
End sub
'Destructor
Private Sub Class_Terminate
End sub

'Methods
'
'This Class only works when the Methods are called In-Order
'Therefore, setup() must be called before addParam and callStoredProcedure
'
'------------------
Public sub setup(databaseConnection, storedProcedureName)
'Note: The Connection must already be made and open to the desired database
cmd.ActiveConnection = databaseConnection
cmd.CommandText = storedProcedureName
cmd.CommandType = 4
End sub

Public sub addParam(variableNameStr, variableType, inputOrOutput, sz, variableValue)
cmd.Parameters.Append = cmd.CreateParameter(variableNameStr, variableType, inputOrOutput, sz, variableValue)
End sub

Public property Get callStoredProcedure
set callStoredProcedure = cmd.Execute
End property
End Class

and the error that I am getting is:

ADODB.Parameters error '800a0e7c'

Parameter object is improperly defined. Inconsistent or incomplete information was provided.

/source/storedProcedureClass.asp, line 27

It seems that every time I try to declare a parameter before calling the stored procedure on the asp page, I get some kind of parameter error if the parameter is NOT an integer.

Does anybody have any thoughts about this?

[hielo]

On those string parameters, include the size of the fields as the fourth parameter.
EX: assuming that NameStr is defined (in your DB Table) to be of size 100, try: call sp.addParam("@NameStr", 200, 1, [B]100[/B],"Stuff and Things")

[stevenfagan]

That's exactly what the problem was. I was under the impression that the size was an optional parameter, which was obviously incorrect. Thanks

[hielo]

Glad to help.

Regards,
Hielo

PS: Don't forget to mark the thread as solved.