Problem calling Stored Procedure

[rico14d]

Hi there,

Im trying to execute a stored procedure but i keep getting this error..

ADODB.Command (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range,
or are in conflict with one another.


If i remove the parameters from the SP and when i call it, it works fine.

Here is my code.

---------------------------------------------

dim objConn,objCmd,param

set objConn = server.CreateObject("ADODB.connection&quot
objConn.Open connection_string

set objCmd = server.CreateObject("ADODB.command&quot
objCmd.CommandText = "my_proc"
set objCmd.ActiveConnection = objConn

set Param = objCmd.CreateParameter("@test",nChar,adParamInput,100,"test&quot

objCmd.Parameters.Append Param

objCmd.Execute

---------------------------------------------

And this is the SP definition

---------------------------------------------

CREATE PROCEDURE my_proc
@test nchar(100)
etc.
---------------------------------------------

Can Anyone help?

Thanks,
Rich.

 

[rac2]

Did you assign a value to nChar?
nChar = 130 'adWChar
nChar = 202 'adVarWChar

Do you need an NCHAR(100) datatype in the stored procedure? Or would a CHAR(100) work?
If so then
nChar = 129 'adChar
nChar = 200 'adVarCahr

 

[codestorm]

set Param = objCmd.CreateParameter("@test",nChar,adParamInput,len("test&quot,"test&quot codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
You're not a complete programmer unless you know how to guess.
I hope I never consider myself an 'expert'.
<insert witticism here>

 

[rico14d]

I tried using len(&quot;test&quot but that made no difference.

Does anyone have an example of calling a stored procedure, Ive tried using one from a text book but it is no help.

Rich.

 

[AWithers]

here's some code that works for me

Andy

dim rs,objcommand,objparam,strcountry

strcountry=&quot;france&quot;'actually retrived from submitted form

set objcommand = server.createobject(&quot;adodb.command&quot
objcommand.activeconnection = dbconnection'your db connection string
objcommand.CommandText = &quot;et_REP_sp_generic_report_final&quot;
objcommand.CommandType = adCmdStoredProc

'assign the parameters (must be in correct order for SQL stored procedure or the order they are encountered in in the Access where clause

'PARAMETER 1
set objparam = objcommand.createparameter(&quot;@country&quot;,advarchar, adparaminput, 50)
objcommand.parameters.append objparam
objcommand.parameters(&quot;@country&quot = strcountry'

' Execute the sql and set the results of the stored procedure as the recordset
set rs=objcommand.execute

'release memory re parameter and command objects
set objparam=nothing
set objcommand=nothing

 

[rico14d]

Ive finally got it working.
Heres the solution
------------------------------------
dim objConn,objCmd

set objConn = server.CreateObject(&quot;ADODB.connection&quot
objConn.Open (Conn_string)

set objCmd = server.CreateObject(&quot;ADODB.command&quot
objCmd.CommandText = &quot;my_proc&quot;
objCmd.CommandType = 4
set objCmd.ActiveConnection = objConn

objCmd.Parameters(&quot;@param1&quot.Value = test1

objCmd.Execute
------------------------------------
Thanks all for your time and help.

Rich