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)
insert into test_table
(NameStr, Qualifier, Notes)
(@NameStr, @Qualifier, @Notes)

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

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

'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?

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

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

Glad to help.


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