Procedure or function 'DropDownList_Instructors' expects parameter '@Facility', which was not supplied.

Sorry, forgot the attachment.
Server-Error-in.docx

The SQL command in following line
SqlDataAdapter dataAdapter = new SqlDataAdapter( command )
is important to identify the problem.

Could you also post what string is in the command variable?

I did find out that I needed to add all the parameters from the stored procedure .  I've done that. So right now I'm getting a different error.  "No overload for method "DropdownList_Instructors' takes 3 arguments.  I'm thinking I must not have all of the parameters somewhere?  I'm going through that now to see if I can find it and fix this one.  Then I should be able to build and run it again.

Ok.  Got it to build again.  A person I work with said I had to add the "AllowAll" parameter?  But for some reason that doesn't seem right.  On the report, I set that to yes and then delete it so that is doesn't ask for it.  Anyway, now I'm getting the following error:

Could not find control "AllowAll" in ControlParameter 'AllowAll'

Ok, got past the control issue and I'm back to procedure or function 'DropdownList_Instructors' expects '@Location', which was not supplied.  I'll attatch the page  for the database class
Database.cs

I've stepped through this.  It retrieves the facility, location, reason and instructors datasets, adds the parameters.  Goes to the connection and loops through the foreach statement for each parameter, just above the dataAdapter method in what I sent.  It then jumps to the commandTimeOut.  The return result shows 300 (instructors).  Goes through more of the commandTimeOut and back to the dataAdapter.  It fails at the dataAdapter.Fill(dataSet)

You have to check what parameters are required in DropdownList_Instructors Stored procedure on SQL Server. It seems you are not passing all of them.

Optionally you may set some default values to this parameters in SP or ensure all these parameters are known prior to the SP call. I suppose you are selecting Facility and Location and maybe Reason from some combo before the report is called. It is also possible you did not select all these values etc. etc.

You should also set a breakpoint in your C# DropdownList_Instructors method and see what's going on and what are parameters before the error occurs.

The params are facility, location, reason, allowall.  I stepped through the param.add and it stepped through adding the params with no problems.

       

 public IDictionary<string, string> DropdownList_Instructors(int Facility, int Location, int Reason, string AllowAll)
        {
            DataSet ds = new DataSet();
            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter param_Facility = ParameterFactory.FACILITY(Facility);
            parameters.Add(param_Facility);
            SqlParameter param_Location = ParameterFactory.CalloutLocation(Location);
            parameters.Add(param_Location);
            SqlParameter param_Reason = ParameterFactory.CalloutReason(Reason);
            parameters.Add(param_Reason);
            SqlParameter param_AllowAll = ParameterFactory.AllowAll(AllowAll);
            parameters.Add(param_AllowAll);
            const string STORED_PROCEDURE_NAME = "DropdownList_Instructors";
            ds = RetrieveDataSet(STORED_PROCEDURE_NAME, parameters);
            IDictionary<string, string> result = ConvertDatasetToDictionary(ds, CONSTANT_DROPDOWNLIST_KEY_NAME, CONSTANT_DROPDOWNLIST_VALUE_NAME);
            return result;
        }

Select allOpen in new window

It then goes to the connection and loops through the foreach statement for each parameter

 using ( SqlConnection connection = new SqlConnection( connectionString ) )
            {
                using ( SqlCommand command = new SqlCommand( storedProcedureName, connection ) )
                {
                    command.CommandType = CommandType.StoredProcedure;
                    if ( parameters == null )
                    {
                        // Do nothing
                    }
                    else
                    {
                        foreach ( SqlParameter parameter in parameters ) // added 10/20/2009 to allow the passing of NULL values into the database. otherwise have to check each parameter if its a null and convert to DBNull.Value
                        {
                            if ( parameter.Value == null )
                            {
                                parameter.Value = DBNull.Value;
                            }
                            //AutoTestCaseConsoleApplication.DebugClass.DebugForceToDisplay( new object[] { parameter.ParameterName + " [" + parameter.UdtTypeName + "] x [" + parameter.Size.ToString(  ) + "] = " + parameter.Value.ToString(  ) }, 3 );
                            command.Parameters.Add( parameter );
                        }
                    }
                    command.CommandTimeout = SetSQLCommandTimeOut( command );

Select allOpen in new window

But then it goes through the timeout set and when it then goes to the dataAdapter, it fails.  There is another dropdown it should go through to populate.  It has sorting options, using a stored procedure.

You have to look at parameter values in the foreach loop. Do all necessary parameters have value defined? It is visible when stepping the code.
How looks the SP definition?

The parameter values in the foreach loop are correct.  All parameters have a value defined and I see it stepping through.  I'll attach the sp.  I'm beginning to think it may have something in defining my params.  The parameters for the three dropdowns prior to mine:
FACILITY - ID, Description
CalloutLocation - ID, Description
CalloutReason - ID, Description

My dropdown:
Facility : I have this pointing to FACILITY in the paramFactory
Location : Location - I created in the paramFactory, it's on my report
Reason : Reason - I created this in the paramFactory, it's on my report
AllowAll : AllowAll - I created

Key and Value for the DropdownList_Instructors - InstructorID and InstructorName
DropDownList-Instructors.sql

I get the error when it starts the DropdownList_Instructors dataset at the code below

  public IDictionary<string, string> ConvertDatasetToDictionary( DataSet dataSet, string keyName, string valueName )
        {
            IDictionary<string, string> result = new Dictionary<string, string>(  );
            if ( dataSet.Tables.Count >= 1 )
            {
                foreach ( DataRow dataRow in dataSet.Tables[ 0 ].Rows ) // convert dataset to dictionary<key_name, value_name>
                {
                    string key_name = dataRow[ keyName ].ToString(  );
                    string value_name = dataRow[ valueName ].ToString(  );
                    result.Add( new KeyValuePair<string, string>( key_name, value_name ) );
                }
            }
            return result; 

Select allOpen in new window

I changed the sp as shown.  I can't run the trace - permissions.  When I started the application and selected the report, I got the following error.

Adding the specified count to the semaphore would cause it to exceed its maximum count.

It seems to be rather different error... It, of course, does not mean the previous error is fixed. It is just suppressed by providing default values...

Default values can also cause a large number of records returned from the SP but I cannot say how this is related to your semaphores...

So you have to step through the code and check the number of records returned from the SP. If this is the problem then you have to find why not all parameters are passed to the SP.

I got the AllowAll to work correctly.  Thank you again for your help.

So you know, the <All> option requires AllowAll to be 'y'.

You did the work. Great!

Thank you.  It does make me feel pretty good to have figured things out.  Points in the right direction helped.  Have great holidays !

My answer was not accurate but as slslusher wrote "Points in the right direction helped".