Error: Process or Function has Too Many Arguments specified

Question

Criei uma procedure para popular uma grid view:


   create procedure [dbo].[spc_listaafiliadosadmin]
(
    @nome varchar(100),
    @login varchar(100),
    @cpf varchar(100)    
)
as
begin
    if(@nome is not null)
        begin
            select 
            a.idafiliado,
            u.login, u.nome NomeUsuario, upai.login  loginpai, u.email,u.cpfcnpj,
            'Sempre exclusivo - Grupo de Consumo' as NomeClube
            ,dbo.getsaldo(a.idafiliado) as saldo
            from usuario u
            inner join afiliado a on a.idusuario = u.idusuario
            inner join afiliado apai on apai.idafiliado = a.idafiliadopai
            inner join usuario upai on upai.idusuario = apai.idusuario
            where u.ativo = 1 and u.nome like @nome
        end
    else 
        if (@login is not null)
            begin
                select 
                a.idafiliado,
                u.login, u.nome NomeUsuario, upai.login  loginpai, u.email,u.cpfcnpj,
                'Sempre exclusivo - Grupo de Consumo' as NomeClube
                ,dbo.getsaldo(a.idafiliado) as saldo
                from usuario u
                inner join afiliado a on a.idusuario = u.idusuario
                inner join afiliado apai on apai.idafiliado = a.idafiliadopai
                inner join usuario upai on upai.idusuario = apai.idusuario
                where u.ativo = 1 and u.login like @login
            end
    else 
        if (@cpf is not null )
            begin
                select 
                a.idafiliado,
                u.login, u.nome NomeUsuario, upai.login  loginpai, u.email,u.cpfcnpj,
                'Sempre exclusivo - Grupo de Consumo' as NomeClube
                ,dbo.getsaldo(a.idafiliado) as saldo
                from usuario u
                inner join afiliado a on a.idusuario = u.idusuario
                inner join afiliado apai on apai.idafiliado = a.idafiliadopai
                inner join usuario upai on upai.idusuario = apai.idusuario
                where u.ativo = 1 and u.cpfcnpj = @cpf
            end
end
GO

in the code the Procedure is called as follows:

if (UsuarioUtil.IsAdmin())
{
    //UsuarioUtil uUtil = new UsuarioUtil();
    string nome = txtNome.Text;
    string login = TxtLogin.Text;
    string cpf = TxtCpf.Text;
    gdvAfiliados.DataSource = _DB.spc_listaafiliadosadmin(nome,login,cpf).ExecuteDataTable(); //_DB.vw_Afiliado.Where(a => a.ativo).ToList(); // uUtil.getAfils("");
    gdvAfiliados.DataBind();

    gdvAfiliados.HeaderRow.TableSection = TableRowSection.TableHeader;

    ScriptManager.RegisterStartupScript(Page, typeof(Page), "", "SetDataTable();", true);

}

But when it is executed it gives this error

Procedure or Function spc_listaafiliadosadmin has Too Many Arguments specified.

Method of processing:

public SPClass spc_listaafiliadosadmin(string nome,string login,string cpf)
{       
    SPClass c = new SPClass(this);                
    DbCommand cmd = c.context.Database.Connection.CreateCommand();
    cmd.CommandText = "spc_listaafiliadosadmin";
    cmd.CommandType = CommandType.StoredProcedure; 
    cmd.CommandTimeout = 600000;

    if (nome != null) 
        cmd.Parameters.Add(new SqlParameter("@nome",nome));          

    if (login != null) 
        cmd.Parameters.Add(new SqlParameter("@login",login));            

    if (cpf != null) 
        cmd.Parameters.Add(new SqlParameter("@cpf",cpf));            

    c.command = cmd;

    return c;
}  

Answer 1

This problem can only be in passing your parameters to the proc, when you do your

if (nome != null) 
if (cpf != null) 
if (cpf != null)

this can be null and with that the settings will not be added to proc.

Fix this part to pass something even if it is an Empty value.

cmd.CommandText = "spc_listaafiliadosadmin";
cmd.CommandType = CommandType.StoredProcedure; 
cmd.CommandTimeout = 600000;

if (nome != null) 
    cmd.Parameters.Add(new SqlParameter("@nome",nome));          

if (login != null) 
    cmd.Parameters.Add(new SqlParameter("@login",login));            

if (cpf != null) 
    cmd.Parameters.Add(new SqlParameter("@cpf",cpf));