Error With SqlCommand not accepting Parameter Arguments

Question

I am trying to execute on a stored procedure in my database with the following code:

using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["myCNN"].ConnectionString))
{
    using (var cmd = new SqlCommand("dbo.myStoredProcedure", connection))
    {
        cmd.Parameters.Add("@PatientId", SqlDbType.VarChar, 40).Value = number;
        cmd.Parameters.Add("@NickName", SqlDbType.VarChar, 20).Value = "stlukeshs";                            
        var reader = cmd.ExecuteReader();
        while(reader.Read())
        {
            //Do Stuff..
        }
        //closer reader
        reader.Close();
    }
}

However, on execution I am getting an error that says:

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: Procedure or function 'myStoredProcedure' expects parameter '@PatientId', which was not supplied.

Clearly you see I am adding the parameters, and my value for number is 100, not null. So I am a bit confused on why it is not getting my parameter arguments.

That's clearly not your real stored procedure name, so presumably other things may not quite be as in the real code... are you 100% sure this isn't a typo? — Jon Skeet
– Jon Skeet, Commented Feb 24, 2017 at 17:38
I'm surprised it would not complain about you not opening the connection. — pilotcam
– pilotcam, Commented Feb 24, 2017 at 17:41

Akash KC · Accepted Answer · 2017-02-24 17:40:21Z

1

You need to specify CommandType which is Text in default. You have to specify StoredProcedure as CommandType i.e.

cmd.CommandType = CommandType.StoredProcedure;

So, your complete code should be :

using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["myCNN"].ConnectionString))
{
    using (var cmd = new SqlCommand("dbo.myStoredProcedure", connection))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@PatientId", SqlDbType.VarChar, 40).Value = number;
        cmd.Parameters.Add("@NickName", SqlDbType.VarChar, 20).Value = "stlukeshs";                            
        var reader = cmd.ExecuteReader();
        while(reader.Read())
        {
            //Do Stuff..
        }
        //closer reader
        reader.Close();
    }
}

answered Feb 24, 2017 at 17:40

Akash KC

16.4k6 gold badges41 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rinktacular Over a year ago

This fixed my problem exactly. I thought that would have been implicit, alas I am wrong. Thank you!

Collectives™ on Stack Overflow

Error With SqlCommand not accepting Parameter Arguments

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related