Cmd Parameter Size Resrictions/limits ?

[bassguy]

Hello All,
I have been chasing this one for a few hours and finally figured I would ask the collective.

I have written a stored procedure on SQL2000 and am trying to execute it and pass it parameters. I have done this before and I know how to do it. Here is the problem...I had a procedure that worked and the ASP passed it Just fine. I have now expanded the procedure and need to pass more parameters (about 6 or 7 more) but when I run the page I get the error:

Procedure 'testproc' expects parameter '@FirstName'

IS there A limit to the Number of Parameters that can be passed? Is there a Buffer size that I am Maxing out?

this works: (notice remmed @sex variable)

ALTER Proc TestProc
@FirstName varchar(20),
@MiddleInitial varchar(1),
@LastName varchar(20),
@Email varchar(50),
@Address varchar(50),
@City varchar(20),
@State varchar(2),
@zip varchar(10),
@Phone varchar(13),
@SocialSecurity varchar(11),
/*@sex bit,*/
@ReturnValue varchar(500)= null OUTPUT
as
set @ReturnValue=/*@FirstName+*/@MiddleInitial+@LastName+@Email+
@Address+@City+@State+@zip+@phone+@SocialSecurity/*+Cast@Sex as varchar)*/

With this ASP: (again remmed out paramSex)

<%set cn=server.createobject(&quot;adodb.connection&quot
set cmd=server.createobject(&quot;adodb.command&quot
set ParamReturnValue= cmd.CreateParameter(&quot;ReturnValue&quot;,adVarChar,adParamOutput,500)
set ParamFirstName=cmd.createparameter(&quot;FirstName&quot;,adVarChar,adParamInput,10)
set ParamMiddleInitial=cmd.createparameter(&quot;MiddleInitial&quot;,adVarChar,adParamInput,1)
set ParamLastName=cmd.createparameter(&quot;LastName&quot;,adVarChar,adParamInput,10)
set ParamEmail=cmd.createparameter(&quot;Email&quot;,adVarChar,adParamInput,20)
set ParamAddress=cmd.createparameter(&quot;Address&quot;,adVarChar,adParamInput,20)
set ParamCity=cmd.createparameter(&quot;City&quot;,adVarChar,adParamInput,15)
set ParamState=cmd.createparameter(&quot;State&quot;,adVarChar,adParamInput,2)
set ParamZip=cmd.createparameter(&quot;Zip&quot;,adVarChar,adParamInput,10)
set ParamPhone=cmd.createparameter(&quot;Phone&quot;,adVarChar,adParamInput,13)
set ParamSocialSecurity=cmd.createparameter(&quot;SocialSecurity&quot;,adVarChar,adParamInput,11)
'set ParamSex=cmd.createparameter(&quot;Sex&quot;,adBoolean,adParamInput)
cn.Open application(&quot;applicanttracking_Connectionstring&quot
set cmd.ActiveConnection=cn
cmd.CommandText=&quot;testProc&quot;
cmd.Parameters.Append ParamFirstName
cmd.Parameters.Append ParamMiddleInitial
cmd.Parameters.Append ParamLastName
cmd.Parameters.Append ParamEmail
cmd.Parameters.Append ParamAddress
cmd.Parameters.Append ParamCity
cmd.Parameters.Append ParamState
cmd.Parameters.Append ParamZip
cmd.Parameters.Append ParamPhone
cmd.Parameters.Append ParamSocialSecurity
'cmd.Parameters.Append ParamSex
cmd.Parameters.Append ParamReturnValue
ParamFirstName.value=&quot;aaa&quot;
ParamMiddleInitial.value=&quot;b&quot;
ParamLastName.value=&quot;ccccc&quot;
ParamEmail.value=&quot;dddddddd&quot;
ParamAddress.value=&quot;eeeeeeeeee&quot;
ParamCity.value=&quot;fff&quot;
ParamState.value=&quot;gg&quot;
ParamZip.value=&quot;hhhh&quot;
ParamPhone.value=&quot;iii&quot;
ParamSocialSecurity.value=&quot;jjj&quot;
'ParamSex.value=0
Cmd.execute
response.write cmd.parameters(&quot;returnValue&quot.Value
%>

So all is well above.
This Also works: (note remmed out @FirstName)

ALTER Proc TestProc
/*@FirstName varchar(20),*/
@MiddleInitial varchar(1),
@LastName varchar(20),
@Email varchar(50),
@Address varchar(50),
@City varchar(20),
@State varchar(2),
@zip varchar(10),
@Phone varchar(13),
@SocialSecurity varchar(11),
@sex bit,
@ReturnValue varchar(500)= null OUTPUT
as
set @ReturnValue=/*@FirstName+*/@MiddleInitial+@LastName+@Email+
@Address+@City+@State+@zip+@phone+@SocialSecurity+
Cast(@Sex as varchar)

With this ASPagain ParamFirstName is remmed out)

<%set cn=server.createobject(&quot;adodb.connection&quot
set cmd=server.createobject(&quot;adodb.command&quot
set ParamReturnValue= cmd.CreateParameter(&quot;ReturnValue&quot;,adVarChar,adParamOutput,500)
'set ParamFirstName=cmd.createparameter(&quot;FirstName&quot;,adVarChar,adParamInput,10)
set ParamMiddleInitial=cmd.createparameter(&quot;MiddleInitial&quot;,adVarChar,adParamInput,1)
set ParamLastName=cmd.createparameter(&quot;LastName&quot;,adVarChar,adParamInput,10)
set ParamEmail=cmd.createparameter(&quot;Email&quot;,adVarChar,adParamInput,20)
set ParamAddress=cmd.createparameter(&quot;Address&quot;,adVarChar,adParamInput,20)
set ParamCity=cmd.createparameter(&quot;City&quot;,adVarChar,adParamInput,15)
set ParamState=cmd.createparameter(&quot;State&quot;,adVarChar,adParamInput,2)
set ParamZip=cmd.createparameter(&quot;Zip&quot;,adVarChar,adParamInput,10)
set ParamPhone=cmd.createparameter(&quot;Phone&quot;,adVarChar,adParamInput,13)
set ParamSocialSecurity=cmd.createparameter(&quot;SocialSecurity&quot;,adVarChar,adParamInput,11)
set ParamSex=cmd.createparameter(&quot;Sex&quot;,adBoolean,adParamInput)
cn.Open application(&quot;applicanttracking_Connectionstring&quot
set cmd.ActiveConnection=cn
cmd.CommandText=&quot;testProc&quot;
'cmd.Parameters.Append ParamFirstName
cmd.Parameters.Append ParamMiddleInitial
cmd.Parameters.Append ParamLastName
cmd.Parameters.Append ParamEmail
cmd.Parameters.Append ParamAddress
cmd.Parameters.Append ParamCity
cmd.Parameters.Append ParamState
cmd.Parameters.Append ParamZip
cmd.Parameters.Append ParamPhone
cmd.Parameters.Append ParamSocialSecurity
cmd.Parameters.Append ParamSex
cmd.Parameters.Append ParamReturnValue
'ParamFirstName.value=&quot;aaa&quot;
ParamMiddleInitial.value=&quot;b&quot;
ParamLastName.value=&quot;ccccc&quot;
ParamEmail.value=&quot;dddddddd&quot;
ParamAddress.value=&quot;eeeeeeeeee&quot;
ParamCity.value=&quot;fff&quot;
ParamState.value=&quot;gg&quot;
ParamZip.value=&quot;hhhh&quot;
ParamPhone.value=&quot;iii&quot;
ParamSocialSecurity.value=&quot;jjj&quot;
ParamSex.value=0
Cmd.execute
response.write cmd.parameters(&quot;returnValue&quot.Value%>

Again all is well which implies that ParamFirstname works and ParamSex Work--When they are seperate.
If I turn them both on I get the error listed above. I assume it is a buffer that is full but have not ever used those....any help would be appreciated. sorry for the long post but I wanted to avoid people saying the Spelling was off.
thanks,

Bassguy

 

[Antzz]

Your parameter list in the stored procedure and the list in the ASP does not seem to match.

If you look at your first procedure, you have @FirstName varchar(20) as the fist parameter. In the ASP you have @ReturnValue as the first parameter to the SP.

If you look at the last section, you have @MiddleInitial varchar(1),as teh first parameter while in the ASP you have RETurnvalue as the first parameter to the ASP

 

[bassguy]

I do not think the order of the Parameters is an issue here...I moved it and it still does not work.

 

[bassguy]

Sorry, as I said in the first post....Both of those examples WORK. it is when I UN rem in either one that it breaks

Bassguy

 

[Antzz]

Did you check the number of parameters in the stored procedure as well as the those in the SP?

I thought, you have not defined &quot;ReturnValue&quot; as a paremeter in the Stored Proc., even though you defined it when calling from the ASP page.

 

[Antzz]

Ahhh...forget it. I saw where you are calling it

 

[bassguy]

yeah is it is in there. Lots of code one the page Easy to miss

thanks again
Bassguy

 

[Tarwn]

Hmm, I don't see why this would be failing either, but I can see how you would think it's something to do with the number of parameters except that the error occurs on the first parameter if all of them are there. Have you tried setting up a simple table with 12 integer values and writing a stored proc to it? Perhaps writing another real quick would show you something new or back up the previous theory of max number, though I find it hard to believe that someone would set a max for anything at 11.
Sorry I couldn't be more helpful, but I see your quandry, it's one of those errors that shouldn't be (like my self rounding floats at work today).
-Tarwn ------------ My Little Dictionary ---------
Reverse Engineering - The expensive solution to not paying for proper documentation

 

[codestorm]

I've had problem before with passing in params out of order from ADO (even when all params are named in ADO; normally with optional params).

As an aside (re:buffer size), the example below works:

create table table1 (
idfield int identity(1,1) primary key,
field1 varchar(500),
field2 varchar(500),
field3 varchar(500),
field4 varchar(500),
field5 varchar(500),
field6 varchar(500),
field7 varchar(500),
field8 varchar(500),
field9 varchar(500),
field10 varchar(500),
field11 varchar(500),
field12 varchar(500),
field13 varchar(500),
field14 varchar(500),
field15 varchar(500),
field16 varchar(500)
)
go
create procedure addtotable1
@f1 varchar(500),
@f2 varchar(500),
@f3 varchar(500),
@f4 varchar(500),
@f5 varchar(500),
@f6 varchar(500),
@f7 varchar(500),
@f8 varchar(500),
@f9 varchar(500),
@f10 varchar(500),
@f11 varchar(500),
@f12 varchar(500),
@f13 varchar(500),
@f14 varchar(500),
@f15 varchar(500),
@f16 varchar(500),
@out int out
as
insert table1 (
field1,
field2,
field3,
field4,
field5,
field6,
field7,
field8,
field9,
field10,
field11,
field12,
field13,
field14,
field15,
field16
)
values (
@f1,
@f2,
@f3,
@f4,
@f5,
@f6,
@f7,
@f8,
@f9,
@f10,
@f11,
@f12,
@f13,
@f14,
@f15,
@f16
)
select @out = max(idfield) from table1
go
declare @a int
exec addtotable1 'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p', @a out
print @a
go
drop procedure addtotable1
go
drop table table1 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>