I have this stored procedure in SQL Server:
ALTER PROCEDURE [dbo].[stpr_WPC_UpdatePOReply]
@eComments NVarChar(200)
AS
BEGIN
SET NOCOUNT ON;
UPDATE dbo.SupplierProductionScheduleReplies
SET Comments = @eComments
WHERE id = 39906;
END
I have hard coded the id column value above to simplify the code until I understand what is going wrong - once sorted, this will be passed as a parameter, too.
I have this code I am running from Bizagi, using their modified version of C#:
//UPDATING FULL PO (PO CHECK)
//https://stackoverflow.com/questions/50519158/executing-a-sql-server-stored-procedure-from-c-sharp#50519216
//accessed by MO 20221215
//https://stackoverflow.com/questions/50519158/executing-a-sql-server-stored-procedure-from-c-sharp?rq=3
//accessed by MO 20240320
//https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16
//accessed by MO 20240320
// Set the connection string
var strcnx = CHelper.getParameterValue("SV11SAGE01-StockControl");
// set the variable and value from a form
var eComments = <m_WPC_WeeklyProductChase.idm_WPC_POCheck.eKPComments>
var connection = new System.Data.SqlClient.SqlConnection();
// identify the stored procedure
var cmd = new SqlCommand("dbo.stpr_WPC_UpdatePOReply");
connection.ConnectionString = strcnx;
cmd.CommandType = CommandType.StoredProcedure;
// add a parameter, based on the variable eComments
cmd.Parameters.Add("@eComments", SqlDbType.NVarChar, 200).Value = eComments;
try
{
// running a stored procedure
connection.Open();
cmd.Connection = connection;
cmd.ExecuteNonQuery();
}
catch(exc)
{
CHelper.ThrowValidationError(exc);
}
finally
{
connection.Close();
}
When I run the code I get an error:
Procedure or function stpr_WPC_UpdatePOReply has too many arguments specified.
I have tried removing the parameters, and just calling the stored procedure with the parameters hard coded in the stored procedure, and the update runs with no issues.
However, when I add in parameters, the code errors. I have about 15 parameters to add in, but I have reduced the code and stored procedure to one and 2 parameters until I can sort out the problem, at which time I will add in the extra ones.
I have looked through lots of articles, and as far as I can see the variable name and data type match (I am assuming NVarChar 200 specified in the C# is equivalent to NVarChar(Max), the SQL parameter query on the stored procedure suggested in one post gave the following:
ParameterName: @eComments;
DataType: nvarchar;
max_length: 400;
is_output: 0
I have tried running with 2 parameters, the other one being a parameter for the record's id.
I have tried using AddWithValue. I have tried with and without the cmd.Parameters.Clear() line. The column Comments is defined in SQL Server as (nvarchar(max), null).
I have checked that the stored procedure name matches - and it works fine if there are no parameters specified.
I have simplified what I am doing as much as possible to reduce the likelihood of silly mistakes and typos, but can't see what I am doing wrong. Originally I was running the query without parameters with no problem, but was warned that this risked SQL injection attack so I am reworking the procedure to run with parameters instead. Any help appreciated. Cheers