SQL Server Stored Procedure . missing parameter error but param definitely there

Question

I am trying to get a stored procedure running and for the life of me cannot get past the error.

If I take the SQL out of the stored procedure and place it into hard coded ASP (Classic) it works 100% so its not the code.

I have about 8 other stored procedures which are similar and they work fine so must be something specific to this one but cannot seem to spot what is wrong.

System is an old Windows 2000 and SQL Server 2000

Error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'lbg_UpdateUserRegInfo_ById' expects parameter '@email_mananger', which was not supplied.
/inc/db_users.asp, line 316

Actual stored procedure code

ALTER PROCEDURE [dbo].[lbg_UpdateUserRegInfo_ById]
    @id int,
@title nvarchar(10),
@firstname nvarchar(50),
@lastname nvarchar(50),
@jobtitle nvarchar(50),
@grade nvarchar(50),
@division nvarchar(50),
@department nvarchar(50),
@communitybank int,
@personalcontactnumber nvarchar(50),
@email nvarchar(75),
@linemanagerfirstname nvarchar(50),
@linemanagersurname nvarchar(50),
@groupmanagerfirstname nvarchar(50),
@groupmanagersurname nvarchar(50),
@seniorgroupmanagerfirstname nvarchar(50),
@seniorgroupmanagersurname nvarchar(50),
@email_mananger nvarchar(75),
@linked_manager_id int
AS
UPDATE lbg_userreginfo
SET
    [title] = @title, 
    [firstname] = @firstname, 
    [lastname] = @lastname, 
    [jobtitle] = @jobtitle, 
    [grade] = @grade, 
    [division] = @division, 
    [department] = @department, 
    [communitybank] = @communitybank, 
    [personalcontactnumber] = @personalcontactnumber, 
    [email] = @email, 
    [linemanagerfirstname] = @linemanagerfirstname, 
    [linemanagersurname] = @linemanagersurname, 
    [groupmanagerfirstname] = @groupmanagerfirstname, 
    [groupmanagersurname] = @groupmanagersurname, 
    [seniorgroupmanagerfirstname] = @seniorgroupmanagerfirstname, 
    [seniorgroupmanagersurname] = @seniorgroupmanagersurname , 
    [email_mananger] = @email_mananger,
    [linked_manager_id] = @linked_manager_id,
    [dt_modified] = GETDATE()
    WHERE [id_userreginfo] = @id

The CLASSIC ASP code/function I am using is below (arrayUserRegInfo is a Dictionary object)

function db_UpdateUserRegInfo(ByRef arrayUserRegInfo)
    Dim cn, cmd, rs, userRegInfoID
    Set cn = Server.CreateObject("ADODB.Connection")
    cn.Open(strConnection)
    Set cmd = Server.CreateObject("ADODB.Command")
    Set cmd.ActiveConnection = cn
    Set rs = Server.CreateObject("ADODB.Recordset")        

    Dim sql
    sql = ""

    Dim manangerEmail
    manangerEmail = arrayUserRegInfo.Item("email_mananger")

    if(NOT arrayUserRegInfo.Count = 16)then
        cmd.CommandText = "lbg_UpdateUserRegInfo_ById"
        cmd.CommandType = adCmdStoredProc

        With cmd
            .Parameters.Append .CreateParameter("@id_userreginfo", adVarChar, adParamInput, 50)
            .Parameters("@id_userreginfo") = arrayUserRegInfo.Item("id_userreginfo")

            .Parameters.Append .CreateParameter("@title", adVarChar, adParamInput, 50)
            .Parameters("@title") = arrayUserRegInfo.Item("title")

            .Parameters.Append .CreateParameter("@firstname", adVarChar, adParamInput, 50)
            .Parameters("@firstname") = arrayUserRegInfo.Item("firstname")

            .Parameters.Append .CreateParameter("@lastname", adVarChar, adParamInput, 50)
            .Parameters("@lastname") = arrayUserRegInfo.Item("lastname")

            .Parameters.Append .CreateParameter("@jobtitle", adVarChar, adParamInput, 50)
            .Parameters("@jobtitle") = arrayUserRegInfo.Item("jobtitle")

            .Parameters.Append .CreateParameter("@grade", adVarChar, adParamInput, 50)
            .Parameters("@grade") = arrayUserRegInfo.Item("grade")

            .Parameters.Append .CreateParameter("@division", adVarChar, adParamInput, 50)
            .Parameters("@division") = arrayUserRegInfo.Item("division")

            .Parameters.Append .CreateParameter("@department", adVarChar, adParamInput, 50)
            .Parameters("@department") = arrayUserRegInfo.Item("department")

            .Parameters.Append .CreateParameter("@communitybank", adInteger, adParamInput, 4)
            .Parameters("@communitybank") = arrayUserRegInfo.Item("communitybank")

            .Parameters.Append .CreateParameter("@personalcontactnumber", adVarChar, adParamInput, 50)
            .Parameters("@personalcontactnumber") = arrayUserRegInfo.Item("personalcontactnumber")

            .Parameters.Append .CreateParameter("@email", adVarChar, adParamInput, 75)
            .Parameters("@email") = arrayUserRegInfo.Item("email")

            .Parameters.Append .CreateParameter("@linemanagerfirstname", adVarChar, adParamInput, 50)
            .Parameters("@linemanagerfirstname") = arrayUserRegInfo.Item("linemanagerfirstname")

            .Parameters.Append .CreateParameter("@linemanagersurname", adVarChar, adParamInput, 50)
            .Parameters("@linemanagersurname") = arrayUserRegInfo.Item("linemanagersurname")

            .Parameters.Append .CreateParameter("@groupmanagerfirstname", adVarChar, adParamInput, 50)
            .Parameters("@groupmanagerfirstname") = arrayUserRegInfo.Item("groupmanagerfirstname")

            .Parameters.Append .CreateParameter("@groupmanagersurname", adVarChar, adParamInput, 50)
            .Parameters("@groupmanagersurname") = arrayUserRegInfo.Item("groupmanagersurname")

            Call Debugging_OutputDictionObj(arrayUserRegInfo, "db_UpdateUserRegInfo->arrayUserRegInfo")

            .Parameters.Append .CreateParameter("@linked_manager_id", adInteger, adParamInput, 4)
            .Parameters("@linked_manager_id") = arrayUserRegInfo.Item("linked_manager_id")

            Response.Write "arrayUserRegInfo.Item(email_mananger) = " & arrayUserRegInfo.Item("email_mananger") & "<br>"

            .Parameters.Append .CreateParameter("@email_mananger", adVarChar, adParamInput, 75)
            .Parameters("@email_mananger") = manangerEmail

            .Execute
        End With
    else
        Response.Write "Error 008: Parameters missing<br>"
    end if

    cn.Close
    Set cn = Nothing
    Set cmd = Nothing
    Set rs = Nothing
    Response.End
    db_CreateUserRegInfo = userRegInfoID
end function

Answer 1

If your using SQL Server, use SQL Profiler to see what is actually being sent to SQL. This way you can see if something is missing/misspelled/whatever.

In .Net if a variable is null it won't add the parameter even if you tell it to. So you may need to check that that field actually has a value.

Answer 2

You don't specify parameters for @seniorgroupmanagerfirstname or @seniorgroupmanagersurname.

Try adding those and keeping your parameters in the same order in your code as in the stored procedure declaration (You've swapped the last two).