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);
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())
{
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 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.