Code Project

Link Unit

Wednesday, September 15, 2010

Procedure has no parameters and arguments were supplied

Problem : Procedure has no parameters and arguments were supplied

Stored procedure code

ALTER procedure [dbo].[GetCompany]
AS
begin
select * from Company
end


Method which calls the above procedure

public void RefreshCompany()
{
try
{
SQLCommand command=new SQLCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "GetCompany";
da.SelectCommand = command;
da.Fill(StaticValues.data.Tables["Company"]);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}

Now the problem is that first time everything is fine, but when the application calls it again (e.g. refresh after update)
it throws the exception "Procedure has no parameters and arguments were supplied". As it is evident that stored procedure does not actually expect any parameters.
It is just a plain select statement


Cause:
The same command object was used in all procedure calls, and it's caching the parameters of earlier call to another procedure

command.parameters collection wasn't cleared.

Solution
#1 command.parameters.Clear() => Clear the parameter cache.
#2 Use another command object for each query / procedure call.


Hope it helps
Jatinder Singh