Procedure expects a parameter which was not supplied

Question

Towards the end of my code I am calling a stored procedure which updates a table based on the parameters passed by my page. I get the following error:

Procedure or function 'Res_invpush_UpdateInv' expects parameter '@InventoryPushSubscriptionId', which was not supplied.

Even thought my parameter value is being successfully passed - I know this because I have tested using breakpoints and when I mouse over on the parameter mentioned it gives the value of 1 so I don't know why the message is still coming.

Can somebody please show me where exactly am I going wrong or how to fix it?

SendInvUpdate.InvServices.UpdateRatePackagesRequest ur = new SendInvUpdate.InvServices.UpdateRatePackagesRequest();
    SendInvUpdate.InvServices.UpdateRatePackagesOperationResponse or = new SendInvUpdate.InvServices.UpdateRatePackagesOperationResponse();


    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            string connStr = ConfigurationManager.ConnectionStrings["bb"].ConnectionString;
            SqlConnection Con = new SqlConnection(connStr);
            Con.Open();
            SqlCommand cmd = new SqlCommand("invpush_PollForAvailableChanges", Con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter NewSysChangeVersionParam = new SqlParameter("@NewSysChangeVersion", SqlDbType.Int);
            NewSysChangeVersionParam.Value = (object)NewSysChangeVersionParam ?? DBNull.Value;
            NewSysChangeVersionParam.Direction = ParameterDirection.InputOutput;
            NewSysChangeVersionParam.SqlDbType = SqlDbType.BigInt;
            SqlDataReader sdr = cmd.ExecuteReader();

            InventoryPushSubscriptionRecord rec = new InventoryPushSubscriptionRecord();

            while (sdr.Read())
            {

                rec.InventoryPushSubId = sdr.GetInt32(0);
                rec.CMName = sdr.GetString(1);
                rec.NotifUrl = sdr.GetString(2);
                rec.Options = sdr.GetString(3);
                rec.LastSysChangeVersion = sdr.IsDBNull(4)?(long?)null:sdr.GetInt32(4);

            }

            if(!sdr.NextResult()) throw new System.Exception("Expected Result set 1 for InventoryChangeRecord");
            InventoryChangeRecord inrec = new InventoryChangeRecord();
            while (sdr.Read())
            {
                inrec.InventoryPushSubId= sdr.GetInt32(0);
                inrec.SysChangeVersion=sdr.IsDBNull(1)?(long?)null:sdr.GetInt32(1);
                inrec.InvDate=sdr.GetDateTime(2);
                inrec.ResId=sdr.GetInt32(3);
                inrec.RoomType=sdr.GetString(4);
                inrec.InvCount=sdr.GetInt32(5);
                inrec.ResName=sdr.GetString(6);

            }

            sdr.Close();
            sdr.Dispose();

            if (NewSysChangeVersionParam != null)
            {
                SendInvUpdate.InvServices.InventoryServiceClient isc = new SendInvUpdate.InvServices.InventoryServiceClient();
                        or = isc.UpdateRatePackages(request);


                        res = or.Results.ToString();
                        int Subid;
                        SubId=inrec.InventoryPushSubscriptionId;
                        SqlCommand ucmd = new SqlCommand("Res_invpush_UpdateInv", Con);
                        ucmd.CommandType = CommandType.StoredProcedure;
                        SqlParameter LastChange = new SqlParameter("@NewLastSysChangeVersion", SqlDbType.Int);
                        LastChange.Value = NewSysChangeVersionParam;
                        SqlParameter SubscriptionId = new SqlParameter("InventoryPushSubscriptionId", SqlDbType.Int);
                        SubscriptionId.Value = SubId;
                        ucmd.ExecuteNonQuery();


                    }

                }
            }                             

        }

        catch (Exception ex)
        {
            throw (ex);
        }
    }

}

TL;DR. Please simplify this example. Makes getting help easier. — usr, Apr 19 '12 at 00:31
No-one want to trawl through such an amount of code. http://www.sscce.org — spender, Apr 19 '12 at 00:32
You're being inconsistent in your parameter naming by the looks of it. Some of your parameters are being passed from your C# code using an "@" and some aren't. I suspect your problem is that you've crossed them up on that parameter. — Mike Parkhill, Apr 19 '12 at 00:35
You probably need to pass parameters e.g. like `command.Parameters.Add(param)` — NSGaga-mostly-inactive, Apr 19 '12 at 00:37
Again - you don't add the `SqlParameter SubscriptionId` to your command anywhere — NSGaga-mostly-inactive, Apr 19 '12 at 00:39

score 2 · Answer 1 · edited Oct 21 '12 at 19:41

You are not setting the parameters property for command object, your code is,

SqlCommand ucmd = new SqlCommand("Res_invpush_UpdateInv", Con);
                    ucmd.CommandType = CommandType.StoredProcedure;

                    SqlParameter LastChange = new SqlParameter("@NewLastSysChangeVersion", SqlDbType.Int);
                    LastChange.Value = NewSysChangeVersionParam;

                    SqlParameter SubscriptionId = new SqlParameter("InventoryPushSubscriptionId", SqlDbType.Int);

You can see that parameter are not assigned to command object's parameter property and hence stored procedure is not getting parameters. Just add the following before ExecuteNonQuery statement,

command.Parameters.Add(LastChange);
command.Parameters.Add(SubscriptionId );

score 2 · Accepted Answer · answered Apr 19 '12 at 00:46

First, as Mike mentions, you should be consistent with the parameter naming.

e.g.

SqlParameter SubscriptionId = new SqlParameter("InventoryPushSubscriptionId", SqlDbType.Int);

should be

SqlParameter SubscriptionId = new SqlParameter("@InventoryPushSubscriptionId", SqlDbType.Int);

And then as @NSGaga points out, you are not really passing the parameters to the command, you just create the objects and aren't using them anywhere.

Like this:

SqlParameter LastChange = new SqlParameter("@NewLastSysChangeVersion", SqlDbType.Int);
LastChange.Value = NewSysChangeVersionParam;
ucmd.Parameters.Add(LastChange);
SubscriptionId = new SqlParameter("@InventoryPushSubscriptionId", SqlDbType.Int);
SubscriptionId.Value = SubId;
ucmd.Parameters.Add(SubscriptionId);

Hope this helps

Procedure expects a parameter which was not supplied

2 Answers2