CODEWITHSUNDEEP
c#sqlsql-serversql-server-2008
aa003
aa003

Reputation: 131

How to Handle SQL Exception Must Pass Parameter number 'x'?

I am trying to store Binary Data by using store procedure. Store procedure has three parameters. Last parameter will be containing Binary Data. But when I run a code it gives SQL Exception

Must pass parameter number 3 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

 SqlCommand cmd = new SqlCommand("EXEC myProc @param1 = 8, @param2= '5.png', @FileSignature");
 using (SqlConnection conn = new SqlConnection(myConnString))
 {
    cmd.Connection = conn;

    if (FileSignature == null) //FileSignature  is byte[]
    {
        cmd.Parameters.Add("@FileSignature", SqlDbType.VarBinary, -1);
        cmd.Parameters["@FileSignature"].Value = System.DBNull.Value;
    }
    else
        cmd.Parameters.AddWithValue("@FileSignature", FileSignature); //FileSignature is byte[]
        int iReturn = cmd.ExecuteNonQuery();
  }

Upvotes: 0

Views: 1007

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

You can't use @param=val for some parameters and @param for others. Also, this is not the proper way to execute a stored procedure using sqlCommand.
Please try reading the exception message. It's plain english and is there to help.
Try this:

using(SqlConnection conn = new SqlConnection(myConnString),
      SqlCommand cmd = new SqlCommand("myProc", conn) 
{
    cmd.CommandType = SqlCommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@param1", 8);
    cmd.Parameters.AddWithValue("@param2", '5.png');

    if (FileSignature == null) //FileSignature  is byte[]
    {
        cmd.Parameters.Add("@FileSignature", SqlDbType.VarBinary, -1);
        cmd.Parameters["@FileSignature"].Value = System.DBNull.Value;
    }
    else
    {
        cmd.Parameters.AddWithValue("@FileSignature", FileSignature); //FileSignature is byte[]
    }
    conn.Open();
    int iReturn = cmd.ExecuteNonQuery();
    conn.Close();
}

Upvotes: 1

jradich1234
jradich1234

Reputation: 1425

Because you supplied the first two parameters by name you must do so for all three. So you need to update you SqlCommand text to include it SqlCommand cmd = new SqlCommand("EXEC myProc @param1 = 8, @param2= '5.png', **@Parameter3** = @FileSignature");

Replace @Parameter3 with the name from your Stored Proc or optional you could just not pass any names (as long as the parameters are in the same order in your procedure.

SqlCommand cmd = new SqlCommand("EXEC myProc 8, '5.png', @FileSignature");

Upvotes: 1

Related Questions