Passing parameters in C# to SQL Server stored procedure - too many arguments specified

78 Views Asked by At

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

0

There are 0 best solutions below