Error: procedure or function has too many arguments specified

2
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 executed it gives this error

  

Procedure or function spc_listaafiliadosadmin has too many arguments specified.

Procedure method:

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;
}  
    
asked by anonymous 18.09.2015 / 21:05

1 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 paramentos will not be added to the proc.

Corrected this part so that something happens 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));  
    
17.08.2016 / 21:25