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

I have an application I'm trying to update an SSRS report in.  I have the report updated and it runs fine.  But, this is my first coding.  The application builds fine, but when I try to open the report, I get the above error.  I'm attaching the screen shot of the full error message screen.
This is written in asp.net.

Any help I can get in finding the issue would be greatly appreciated.  Thanks
SherryDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SherryDeveloperAuthor Commented:
Sorry, forgot the attachment.
Server-Error-in.docx
0
pcelbaCommented:
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?
0
SherryDeveloperAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SherryDeveloperAuthor Commented:
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'
0
SherryDeveloperAuthor Commented:
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
0
SherryDeveloperAuthor Commented:
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)
0
pcelbaCommented:
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.
0
SherryDeveloperAuthor Commented:
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;
        }

Open 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 );

Open 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.
0
pcelbaCommented:
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?
0
SherryDeveloperAuthor Commented:
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
0
SherryDeveloperAuthor Commented:
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; 

Open in new window

0
pcelbaCommented:
Surely parameters are missing when calling SP because your SP requires all four parameters to be passed.

And if the SP reports an error then it does not create DataSet and above code must also fail.

You may see passed parameters when you start SQL Server Profiler (available in SQL Server Management Studio - Tools menu).

Optionally make SP parameters non-mandatory:
CREATE PROCEDURE [dbo].[DropDownList_Instructors]
	-- Add the parameters for the stored procedure here
	
		@Facility	 Int = 0,
		@Location Int = 0,
		@Reason  Int = 0,
		@AllowAll VARCHAR(1) = 'n'

Open in new window

and test the code to get some results.
0
SherryDeveloperAuthor Commented:
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.
0
pcelbaCommented:
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.
0
SherryDeveloperAuthor Commented:
I can run the report now!  I had to add the sp name to the following:

 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_DropdownList_Instructors, CONSTANT_DROPDOWNLIST_VALUE_NAME_DropdownList_Instructors);
            return result;

I also had on "Instructor"  not "InstructorName" in the sp.   Everything works execpt when I go to the screen to select my parameters, I don't get the <All> option for the instructors.  So will work on that now.  I really appreciate your help in looking for the things to check.

I know that if I run the report directly from reporting services, I get the All option.  Just not from the application and report screen.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SherryDeveloperAuthor Commented:
I got the AllowAll to work correctly.  Thank you again for your help.
0
pcelbaCommented:
So you know, the <All> option requires AllowAll to be 'y'.

You did the work. Great!
0
SherryDeveloperAuthor Commented:
Thank you.  It does make me feel pretty good to have figured things out.  Points in the right direction helped.  Have great holidays !
0
pcelbaCommented:
My answer was not accurate but as slslusher wrote "Points in the right direction helped".
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.