sproc has too many arguments problem

[AkAlan]

I'm using a sproc to return role membership values and it worked fine when I
used just one input and output parameter. I run the sproc on the adp's main
form and set different global variables to true for every role the user is a
member of. I want to try and pass all roles to the sproc at one time rather
than run it several times passing to it a different role name but I'm getting
an error "The sproc has too many arguments". I'll post the sproc and vba code
I'm using. Thanks for any help

sproc:


CREATE Procedure pr_IsRoleMember_IMMS
(
@RoleManagers sysname OUTPUT,
@RoleASWSQC sysname OUTPUT
)

AS
BEGIN
DECLARE @RetManagers INTEGER
DECLARE @RetASWSQC INTEGER


IF IS_MEMBER (@RoleManagers) = 1
SET @RetManagers = 1
ELSE IF IS_MEMBER (@RoleManagers) = 0
SET @RetManagers = 0
ELSE IF IS_MEMBER (@RoleManagers) IS NULL
SET @RetManagers = NULL

IF IS_MEMBER (@RoleASWSQC) = 1
SET @RetASWSQC = 1
ELSE IF IS_MEMBER (@RoleASWSQC) = 0
SET @RetASWSQC = 0
ELSE IF IS_MEMBER (@RoleASWSQC) IS NULL
SET @RetASWSQC = NULL

RETURN
END

GO

VBA that calls the sproc

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "pr_IsRoleMember_IMMS"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("RetASWSQC", adInteger, adParamReturnValue)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@RoleASWSQC", adBSTR, adParamInput, 11,
"ASWS_QC")
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("RetManagers", adInteger, adParamReturnValue)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@RoleManagers", adBSTR, adParamInput, 11,
"Managers")
cmd.Parameters.Append prm
cmd.Execute

 

[Sylvain Lafontaine]

You are mixing up adParamReturnValue and adParamOutput. You can have only
one adParamReturnValue parameter and its value is returned with the RETURN
statement. It must also be the first in the list.

You need to add @RetManagers and @RetASWSQC into the parameters list and use
adParamOutput for them:

CREATE Procedure pr_IsRoleMember_IMMS
(
@RoleManagers sysname INPUT,
@RoleASWSQC sysname INPUT,

@RetManagers INTEGER OUTPUT,
@RetASWSQC INTEGER OUTPUT
)

SQL-Server doesn't make any distinction between INPUT/OUTPUT and OUTPUT
only, so your two OUTPUT variables are also INPUT variables.

Finally, I'm not sure if adBSTR must be used for sysname; you can use the
method described in http://support.microsoft.com/kb/q165156/ to check for
that.

 

[AkAlan via AccessMonster.com]

Thanks Sylvan I got it to work now, you should write a book.