Solved

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

Posted on 2014-11-18
20
119 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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
 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
silent install of security banner via msiexec command 4 87
Modify a small python script 19 108
Widget to get customer remakrs in our website. 3 74
PL SQL Developer 7 32
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This video teaches users how to migrate an existing Wordpress website to a new domain.
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

813 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