Solved

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

Posted on 2014-11-18
20
116 Views
Last Modified: 2014-12-02
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
0
Comment
Question by:Sherry
  • 12
  • 7
20 Comments
 

Author Comment

by:Sherry
ID: 40450239
Sorry, forgot the attachment.
Server-Error-in.docx
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40452007
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
 

Author Comment

by:Sherry
ID: 40452619
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
 

Author Comment

by:Sherry
ID: 40452664
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
 

Author Comment

by:Sherry
ID: 40452797
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
 

Author Comment

by:Sherry
ID: 40452934
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
 
LVL 41

Expert Comment

by:pcelba
ID: 40453003
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
 

Author Comment

by:Sherry
ID: 40453030
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
 
LVL 41

Expert Comment

by:pcelba
ID: 40453184
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Sherry
ID: 40453221
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
 

Author Comment

by:Sherry
ID: 40453234
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
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 500 total points
ID: 40453321
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
 

Author Comment

by:Sherry
ID: 40453335
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
 
LVL 41

Expert Comment

by:pcelba
ID: 40453460
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
 

Accepted Solution

by:
Sherry earned 0 total points
ID: 40453505
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
 

Author Comment

by:Sherry
ID: 40453593
I got the AllowAll to work correctly.  Thank you again for your help.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40453813
So you know, the <All> option requires AllowAll to be 'y'.

You did the work. Great!
0
 

Author Comment

by:Sherry
ID: 40453817
Thank you.  It does make me feel pretty good to have figured things out.  Points in the right direction helped.  Have great holidays !
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40470430
My answer was not accurate but as slslusher wrote "Points in the right direction helped".
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article will show, step by step, how to integrate R code into a R Sweave document
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now