过程或函数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屋!