过程或函数sp_XXX指定的参数过多. [英] Procedure or function sp_XXX has too many arguments specified.

问题描述

大家好，

尝试将信息保存到数据库时出现异常.我不认为我迷失了概念或逻辑，但这使我发疯.

代码遍历DataTable以获取每一行，并将每一行中包含的值传递到存储过程中.它适用于单行，但是当行数超过一时失败.

例外是过程或函数sp_XXX指定的参数过多."；

这是示例代码:

Hallo everyone,

I am getting an exception when I try to save information into a database. I don''t think I lost the concept or logic but it is getting me crazy.

The code iterates through a DataTable to get each row and passes the value contained in each row into a stored procedure. It works for a single row but it fails when the number of rows are more than one.

The Exception is "Procedure or function sp_XXX has too many arguments specified.";

Here is the sample code:

public string Create_XXXInformation(DataTable dt, string OwnerID)
        {
            int row_counter = 0;
            string Result = String.Empty;
            SqlCommand command = new SqlCommand();
            command.CommandText = "sp_XXX";
            command.CommandType = CommandType.StoredProcedure;

            foreach (DataRow row in dt.Rows)
            {
                command.Connection = TrnsObj();               
                
                command.Parameters.Add("@OwnerID", SqlDbType.Int).Value = Convert.ToInt32(OwnerID); // Foreignkey at...
                command.Parameters.Add("@XXXID", SqlDbType.Int).Value = Convert.ToInt32(row["XXXID"].ToString());
                command.Parameters.Add("@YYYID", SqlDbType.Int).Value = Convert.ToInt32(row["YYYID"].ToString());
                
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    //dt.Rows.RemoveAt(0);
                    TrnsObj().Close();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            if (reader["ReturnCode"].ToString() == "0")
                            {
                                row_counter++;
                                Result = row_counter + " new rows inserted.";
                            }
                        }
                    }
                }
            }
            return Result;
        }

ALTER PROCEDURE [dbo].[sp_XXX] 
		@YYYID Int,
		@XXXID Int,
		@OwnerID Int
		
AS
BEGIN
	If(@OwnerID <> 0 And @YYYID <> 0 And @XXXID <> 0)
		Begin;
			-- Insert statements for procedure here
			Insert InTo Table_XXX(YYYID, XXXID, OwnerID)
			Values(@YYYID, @XXXID, @OwnerID);
			
			Select '0' As ReturnCode, 'Success' As Message;
		End;	
	Else
		Begin;
			Select '-1' As ReturnCode, 'Failure' As Message;
		End;
END

实话实说，我对原始存储过程做了一些修改，实际上我已重命名并减少了参数数量，这是唯一的更改.
请让我知道，如果有什么我可以详细说明的.

To tell you the truth I did a little modification to the original Stored Procedure, actually I renamed and decrease the number of the parameters, these are the only changes.
Please let me know, if there is any thing I can more get elaborated.

推荐答案

在每次迭代中，您都为语句添加新参数.而是只添加一次参数，并在循环中更改值.像这样的东西:

On every iteration you add new parameters for the statement. Instead add the parameters only once and in the loop change the values. Something like:

...
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@OwnerID", SqlDbType.Int); 
command.Parameters.Add("@XXXID", SqlDbType.Int);
command.Parameters.Add("@YYYID", SqlDbType.Int);
command.Connection = TrnsObj();

foreach (DataRow row in dt.Rows)
{
   command.Parameters["@OwnerID"].Value = Convert.ToInt32(OwnerID); 
   command.Parameters["@XXXID"].Value = Convert.ToInt32(row["XXXID"].ToString()); 
   command.Parameters["@YYYID"].Value = Convert.ToInt32(row["YYYID"].ToString()); 

   using (SqlDataReader reader = command.ExecuteReader())
   {
   ...

首先，不要使用Parameters.Add，请使用Parameters.AddWithValue:

Firstly, don''t use Parameters.Add, use Parameters.AddWithValue:

command.Parameters.AddWithValue("@OwnerID", OwnerID); // Foreignkey at...
command.Parameters.Add("@XXXID",(int) row["XXXID"]);
command.Parameters.Add("@YYYID",(int) row["YYYID"]);

其次，我们需要定义存储过程以确保确切的错误.

Secondly, we would need the definition of the stored procedure to be sure what the error is exactly.

这篇关于过程或函数sp_XXX指定的参数过多.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！