I ran into this problem today because sometimes the FormView control will pass more parameters to your stored procedure than it will accept (using SqlDataSource).
Here’s some more info on the error: http://geekswithblogs.net/chrishan/archive/2005/07/02/45278.aspx
The best solution I could come up with is below. Before the insert, the command arguments are inspected and any that are not in the <InsertParameters> collection are discarded.
public void MessageDataSource_OnInserting(object sender, SqlDataSourceCommandEventArgs e)
{
//Remove insert parameters that .NET is attempting to pass
//if they are not defined in the SqlDataSource InsertParameters list.
foreach (DbParameter param in e.Command.Parameters)
{if (MessageDataSource.InsertParameters[param.ParameterName] == null)
e.Command.Parameters.RemoveAt(param.ParameterName);}
}
May 25, 2007 at 7:02 pm
This passage is regarding to ‘Procedure or function has too many arguments specified’ Error
I have read many belogs, though many were usefule but what I found is:
I have used Stored Procedures to select, insert, update, and delete.
1- Visual Studio based on the parameters declared in the stored procedure creates the parameter list for any of the actions like insert
2- Based on the fields on the form VS creates the values as the parameters.
If your stored procedure has a variable as inClientID and your table field is ClientID, VS creates insertparametes as
And Binding the field on the form is
ClientID:
‘>
When iserting VS takes to parametert to pass to stored procedure
@inClientID
@Client ID
so the stored prcedure expects one parameter but receives two parameters and generates error.
The solution is :
Declare the parameter in the stored procedure as the name of the field
Like
Create Procedure insert
@client ID int
As
insert into clienttable ([clientID]) values (@clientID)
Go
In this way VS creates the insert parameter and value with the same name as clientID
To find how many parameters the page passes to a command use this code that I found in one of the belogs:
Protected Sub YourSqlDataSource_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles HousinClientList.Inserting
For x As Integer = 0 To e.Command.Parameters.Count – 1
Trace.Write(e.Command.Parameters(x).ParameterName)
Trace.Write(e.Command.Parameters(x).Value)
Next
End Sub
Enable trace = true on your page. You will see what I am saying.
October 25, 2007 at 8:39 am
This generates exception if more than 1 param needs to removed since the collection is being modified:
Exception:
Collection was modified; enumeration operation may not execute.
Solution:
Use for loop starting from collection end to 0 index, so you do not miss any param when it moves:
for (int i = e.Command.Parameters.Count – 1; i >= 0; i–)
if(MySqlDataSource.UpdateParameters[e.Command.Parameters[i].ParameterName] == null)
e.Command.Parameters.RemoveAt(e.Command.Parameters[i].ParameterName);