SQLDataSource = "Procedure or function [Name] has too many arguments specified."

Question

I am not sure if its asp:SqlDataSource control or it's the ASPxTreeList from DevExpress I am using but I am getting a weird error when trying to fire a Stored Procedure using the Insert() method.

When adding a node I try to insert the node into the database

   protected void TagList_NodeInserting(object sender, DevExpress.Web.Data.ASPxDataInsertingEventArgs e)
    {
        SqlDataTagging.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
        SqlDataTagging.InsertParameters.Add("ParentID", e.NewValues["ParentTag_ID"].ToString());
        SqlDataTagging.InsertParameters.Add("TagName", e.NewValues["TagName_VC"].ToString());
        SqlDataTagging.InsertParameters.Add("UserID", "1");

        SqlDataTagging.InsertCommand = "sp_InsertTag";

        SqlDataTagging.Insert();
    }

However when I fire the above code, I get this error

Procedure or function sp_InsertTag has too many arguments specified.

My SP header

ALTER PROCEDURE [dbo].[sp_InsertTag] (@ParentID INT, @TagName VARCHAR(100), @UserID int)

But as you can see, there are 3 parameters in the SP and 3 in the C# code.

Weirdly, if I actually run the SQL via text e.g.

SqlDataTagging.InsertCommandType = SqlDataSourceCommandType.Text;

and write the SQL in C#, it will work...

Anyone have any ideas or any alternatives so I can use Stored Procedures?

Thanks

beware of the "sp_" prefix: http://stackoverflow.com/questions/20530211/avoid-naming-user-stored-procedures-sp-or-sp — Tanner, Feb 08 '17 at 13:52
Thanks for the warning, still does the same error when I changed the name but something to think about in the future. — user3428422, Feb 08 '17 at 13:56
Is `TagList_NodeInserting` being called a 2nd time when this happens? If so is it the same Parameters Collection object? If so are you Clearing the collection before the call? Even if you are clearing the collection, instantiate a new one. — radarbob, Feb 08 '17 at 14:53
No, the stored procedure actually fires but the AspxTreelist comes back saying "Procedure or function sp_InsertTag has too many arguments specified." all the time. — user3428422, Feb 08 '17 at 15:01

score 1 · Accepted Answer · answered Feb 09 '17 at 07:22

Assume you have SqlDataSource with ID SqlDataTagging to populate tree nodes, the error occurs if TagList_NodeInserting method fires more than once, hence at certain point InsertParameters collection populates with more than 3 parameters specified in stored procedure.

To mitigate this behavior, I suggest you clearing InsertParameters collection before adding parameters like below:

...
SqlDataTagging.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
SqlDataTagging.InsertParameters.Clear(); // add this line to clear InsertParameters collection before adding parameters
SqlDataTagging.InsertParameters.Add("ParentID", e.NewValues["ParentTag_ID"].ToString());
SqlDataTagging.InsertParameters.Add("TagName", e.NewValues["TagName_VC"].ToString());
SqlDataTagging.InsertParameters.Add("UserID", "1");
...

Related issue:

ASPxTreeList - The 'Procedure or function [name] has too many arguments specified' exception is raised when a node is deleted (similar approach)

SQLDataSource = "Procedure or function [Name] has too many arguments specified."

1 Answers1