How to fix "expects parameter" error

[BRawn]

Hi,

I'm writing an application which needs SQL parameters to be passed, and even though I've assigned parameters to the stored procedure, I keep getting the following error message from the debugger:

'usp_PopulateManualSelectionForEdit' expects parameter '@BinID', which was not supplied.

The stored proc:

CREATE PROCEDURE usp_PopulateManualSelectionForEdit
 
    @BinID int,
    @ProductID int,
    @ToteID int,
    @PickAmount int
 
AS
 
SELECT
    c.CON_ID,
    c.CON_DESC,
    p.PROD_ID,
    p.PROD_NAME,
    pic.PICKINSTRLINE_ID,
    pic.PICKINSTRLINE_QTY_NEEDED,
    t.TOTE_ID,
    t.TOTE_NR
FROM TBLCONTAINERS c
    INNER JOIN TBLPRODUCT p WITH(NOLOCK) ON p.PROD_ID = c.PROD_ID
    INNER JOIN TBLPICKINSTRLINES pic WITH(NOLOCK) ON pic.CON_ID = c.CON_ID
    INNER JOIN TBLTOTES t WITH(NOLOCK) ON t.TOTE_ID = pic.TOTE_ID
WHERE c.CON_ID = @BinID
AND c.CON_ACTIVE = 1
AND p.PROD_ID = @ProductID
AND p.PROD_ACTIVE = 1
AND t.TOTE_ID = @ToteID
AND t.TOTE_ACTIVE = 1
AND pic.PICKINSTRLINE_QTY_NEEDED = @PickAmount
 

The stored proc executes fine in SQL when I give the parameters values, so it can't be an incorrect script.

The code-behind (C#):

public DataSet populateManualSelectionForEdit(DataSet dataSet, int binID, int productID, int toteID, int pickAmount)
        {
            using (SqlConnection connection = InstancesTest.IDataInterface.DefineConnection())
            {
                SqlCommand command = new SqlCommand();
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "usp_PopulateManualSelectionForEdit";
                command.Connection = connection;
 
                try
                {
                    if (connection.State == ConnectionState.Closed)
                    {
                        connection.Open();
                    }
 
                    command.Parameters.Add("@BinID", SqlDbType.Int);
                    command.Parameters["@BinID"].Value = binID;
                    command.Parameters.Add("@ProductID", SqlDbType.Int);
                    command.Parameters["@ProductID"].Value = productID;
                    command.Parameters.Add("@ToteID", SqlDbType.Int);
                    command.Parameters["@ToteID"].Value = toteID;
                    command.Parameters.Add("@PickAmount", SqlDbType.Int);
                    command.Parameters["@PickAmount"].Value = pickAmount;
 
                    command.ExecuteNonQuery();
 
                    SqlDataAdapter da = new SqlDataAdapter(command.CommandText, connection);
                    da.Fill(dataSet);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString(), "Error in Stored Procedure");
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }
                return dataSet;
            }
        }
 

As you can see, I'm giving the parameters their values, but they're not getting passed to the stored proc. I initially left the command.ExecuteNonQuery() method out of the logic, but that didn't work either. I'm not too sure whether or not I should use a DataReader.
I don't want my query in a string in C#, so please don't suggest someting like selectQuery = "SELECT * FROM TBLPROJECTS";
Any other suggestions to a work-around would be appreciated.

Thanks,
BRawn

[Christian Binder]

On line 28 you do

SqlDataAdapter da = new SqlDataAdapter(command.CommandText, connection);
 

So just the CommandText will be executed (without parameters)

You could do it like that:

SqlDataReader dr = command.ExecuteReader();
System.Data.DataSet ds = new System.Data.DataSet();
System.Data.DataTable dt = new System.Data.DataTable("table");
dt.Load(dr);
ds.Tables.Add(dt);
 

[BRawn]

Thanks a lot, seems to have worked and what you said makes sense..."The CommandText will be executed (without parameters)"

[broger]

You could also default your SQL input parameters:

@BinID int DEFAULT -1

Or something like that