CODEWITHSUNDEEP
c#exceptionstored-procedures
Omar Kooheji
Omar Kooheji

Reputation: 55760

Executing a stored procedure yields an error saying it's expecting a parameter that I have supplied?

I've written a piece of c# code which executes a stored procedure on an MS SQL box. I Create the SP like so:

SqlCommand command = new SqlCommand(creditLimitRequestSP, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;

I then create a set of parameters using the constructor for the parameters:

SqlParameter p1 = new SqlParameter(p1, p1Value);
vdnParam.Size = 7;

Some of my parameters are integers in which case I parse them like so:

int p4Value = 0;
bool parsedP4 = Int32.TryParse(p4AsAString, out p4Value);
SqlParameter p4;
if (parsedP4)
{
    p4 = new SqlParameter("@p4", SqlDbType.Int, p4Value);
    p4.Size = sizeof(Int32);
}
else
{
    throw new InvalidFieldDataException("p4", p4AsString);
}

Then after I've parsed the parameters from a dictionary I add them like so:

command.Parameters.Add(p1);
command.Parameters.Add(p2);
command.Parameters.Add(p3);
command.Parameters.Add(p4);
command.Parameters.Add(p5);
command.Parameters.Add(p6);

When I execute the stored procedure I get an exception telling me is is expecting a parameter which I have added to the command. Why would it do this?

Initially I thought this might be because the parameters were being added in the wrong order but I changed them to match the order in the Stored procedure (event though it shouldn't matter as I'm using named parameters) and it didn't resolve anything.

The parameter in question does not have a default value in the SP, and it an integer value.

Upvotes: 0

Views: 386

Answers (2)

Omar Kooheji
Omar Kooheji

Reputation: 55760

I think I've found the answer...

p4 = new SqlParameter("@p4", SqlDbType.Int, p4Value);

The third Parameter is not the value it's the size of the param.

To set the value you need to call;

p4.Value = p4Value;

Going to test and will come back...

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294307

If the exception you get is telling that you are not adding a parameter, then it must be that you're not adding it. The parameters must match by name indeed (order doesn't matter), and all non-optional parameters must be present. Direction (Input, Output) must also be correctly set.

The exception message should tell you which parameter is not present, or has the wrong type.

There is no much to help you here, since there must be something at odd between what you believe you're passing and what you actually pass. You should monitor the RPC:Starting Event Class in the SQL Profiler and see exactly what are you passing.

Upvotes: 1

Related Questions