CODEWITHSUNDEEP
sql-servervbams-accessadodb
EliSauder
EliSauder

Reputation: 138

Access ADODB - Procedure or function has too many arguments specified

I have recently started using ADODB to execute stored procedures and I have run into a bit of a hitch. Whenever I try and run this certain stored procedure it is giving me the error: Run-time error '-2147217900 (80040e14)': [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function CheckTeamMember has too many arguments specified. However, when I double check both the call in vba and the SP in sql server they both have the same number of arguments, 3.

Here is the SP:

CREATE PROC [dbo].[CheckTeamMember] (
    @projectID VARCHAR(45),
    @empID INT,
    @exists BIT OUTPUT
)
AS
    DECLARE @trueFalse BIT;

    SET @trueFalse = 0

    IF EXISTS (SELECT TOP 1 projectteammember_id FROM ci_projectteammember 
                    INNER JOIN ci_projectteam ON ci_projectteammember.projectteammember_team = ci_projectteam.projectteam_id 
                    WHERE ci_projectteam.projectteam_project = @projectID AND projectteammember_member = @empID)
    BEGIN
        SET @trueFalse = 1
    END
    ELSE
    BEGIN
        SET @trueFalse = 0
    END

    SELECT @exists = @trueFalse

Here is the code in vba (this is a class module. I run init() before running the actual query function):

Option Compare Database
Option Explicit

Private conDB As adodb.Connection
Private cmdCheckTeamMember As adodb.Command

Public Sub init(con As String)
    Set conDB = New adodb.Connection
    conDB.ConnectionString = con
    conDB.Open

    Set cmdCheckTeamMember = New adodb.Command

    With cmdCheckTeamMember
        .ActiveConnection = conDB
        .CommandType = adCmdStoredProc
        .CommandText = "CheckTeamMember"
        .Prepared = True
    End With

End Sub

Public Function checkTeamMember(projectID As String, empID As Integer) As Boolean

    Dim exists As Boolean

    With cmdCheckTeamMember
        .Parameters.Append .CreateParameter("@projectID", adVarChar, adParamInput, 45, projectID)
        .Parameters.Append .CreateParameter("@empID", adInteger, adParamInput, , empID)
        .Parameters.Append .CreateParameter("@exists", adBoolean, adParamInputOutput, , exists)
        .Execute
    End With

    checkTeamMember = exists

End Function

Upvotes: 0

Views: 398

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89051

If you want to re-use a Command, you need to add the parameters only once. So add the parameters in Init, and only access the parameters in ckdCheckTeammember. Something like:

Public Function checkTeamMember(projectID As String, empID As Integer) As Boolean

    Dim exists As Boolean

    With cmdCheckTeamMember
        .Parameters("@projectID").value = projectID
        .Parameters("@empID").value = empID

        .Execute

        exists = .Parameters("@exists").value 
    End With

    checkTeamMember = exists

End Function

Upvotes: 1

Related Questions