Solved

Error while Generating Data. Error is System.Data.SqlClient.SqlException: Procedure 'sp_DestinationElements' expects parameter '@employerId', which was not supplied

Posted on 2006-07-03
17
289 Views
Last Modified: 2010-04-16
Morning experts,

I created this Sp
CREATE PROCEDURE sp_DestinationElements @employerId varchar(50) AS
Select distinct
DestinationElement,  
  Deduction
  from DestinationElements
where employerId  = @employerID

and in my c# code, i called it like

string ConnectionString = ConfigurationSettings.AppSettings["connectLocal"];
                  SqlConnection  myConnection = new SqlConnection(ConnectionString);

                  SqlCommand myCommand = new SqlCommand("sp_DestinationElements", myConnection);
                  myCommand.CommandType = CommandType.StoredProcedure;
                  try
                  {
                        myConnection.Open();
                        dgCompensation.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                        dgCompensation.DataBind();
                  }
                  catch
                        (SqlException Sqlexc)
                  {
                        lblStatus.Text = "Error while Generating Data. Error is " + Sqlexc.ToString();
            
      }

but when I ran it, it gave this error

Error while Generating Data. Error is System.Data.SqlClient.SqlException: Procedure 'sp_DestinationElements' expects parameter '@employerId', which was not supplied. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at AutoDataCapture.MapCompensationElements.BindGrid() in c:\inetpub\wwwroot\autodatacapture_2\mapcompensationelements.aspx.cs:line 99



How can I rectify this?

thanks.
0
Comment
Question by:SirReadAlot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 6
17 Comments
 
LVL 9

Expert Comment

by:WinterMuteUK
ID: 17029639
Hi SirReadAlot,

You need to add the Parameter to your command:

  SqlParameter param = new SqlParameter("@employerID", VarChar);
  param.Value = THEID;
  myCommand.Parameters.Add(param);

then try calling it.

(I may have the args wrong for constructing a new Parameter, but the intellisense will put you right!! :))

Wint.
0
 

Author Comment

by:SirReadAlot
ID: 17029641
will try this
0
 

Author Comment

by:SirReadAlot
ID: 17029654
hi, don't really know where to add the code

private void BindGrid()
            {

                  string ConnectionString = ConfigurationSettings.AppSettings["connectLocal"];
                  SqlConnection  myConnection = new SqlConnection(ConnectionString);

                  SqlCommand myCommand = new SqlCommand("sp_DestinationElements", myConnection);
                  myCommand.CommandType = CommandType.StoredProcedure;

                  try
                  {
                        myConnection.Open();
                        dgCompensation.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                        dgCompensation.DataBind();
                  }
                  catch
                        (SqlException Sqlexc)
                  {
                        lblStatus.Text = "Error while Generating Data. Error is " + Sqlexc.ToString();
                  }
            }
            #endregio
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:WinterMuteUK
ID: 17029663
Hi, sorry,

just place it under the bit where you have 'myCommand.commandType = CommandType.StoredProcedure'

Wint
0
 

Author Comment

by:SirReadAlot
ID: 17029668
okay
0
 

Author Comment

by:SirReadAlot
ID: 17029687
I have done thsi
private void BindGrid()
            {

                  string ConnectionString = ConfigurationSettings.AppSettings["connectLocal"];
                  SqlConnection  myConnection = new SqlConnection(ConnectionString);

                  SqlCommand myCommand = new SqlCommand("sp_DestinationElements", myConnection);
                  myCommand.CommandType = CommandType.StoredProcedure;
                  SqlParameter param = new SqlParameter("@employerID","varchar");
                  param.Value = THEID;
                  myCommand.Parameters.Add(param);


                  try
                  {
                        myConnection.Open();
                        dgCompensation.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                        dgCompensation.DataBind();
                  }
                  catch
                        (SqlException Sqlexc)
                  {
                        lblStatus.Text = "Error while Generating Data. Error is " + Sqlexc.ToString();
                  }
            }

what should go here

param.Value = THEID;

i tried param.Value = @employeeID
0
 

Author Comment

by:SirReadAlot
ID: 17029715
i fink this is it
private void BindGrid()
            {

                  string ConnectionString = ConfigurationSettings.AppSettings["connectLocal"];
                  SqlConnection  myConnection = new SqlConnection(ConnectionString);

                  SqlCommand myCommand = new SqlCommand("sp_DestinationElements", myConnection);
                  myCommand.CommandType = CommandType.StoredProcedure;
                  SqlParameter param = new SqlParameter("@employerID","varchar");
                  param.Value = "employerID";
                  myCommand.Parameters.Add(param);


                  try
                  {
                        myConnection.Open();
                        dgCompensation.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                        dgCompensation.DataBind();
                  }
                  catch
                        (SqlException Sqlexc)
                  {
                        lblStatus.Text = "Error while Generating Data. Error is " + Sqlexc.ToString();
                  }
            }
            #endregion

i will test and let you know if the code works
0
 
LVL 9

Expert Comment

by:WinterMuteUK
ID: 17029756
It should look like this:

SqlParameter param = new SqlParameter( "@employerID", SqlDbType.VarChar, 50 );

Wint.
0
 

Author Comment

by:SirReadAlot
ID: 17029798
okay
0
 

Author Comment

by:SirReadAlot
ID: 17029833
Hi,

I used this
private void BindGrid()
            {

                  string ConnectionString = ConfigurationSettings.AppSettings["connectLocal"];
                  SqlConnection  myConnection = new SqlConnection(ConnectionString);

                  SqlCommand myCommand = new SqlCommand("sp_DestinationElements", myConnection);
                  myCommand.CommandType = CommandType.StoredProcedure;
                  SqlParameter param = new SqlParameter( "@employerID", SqlDbType.VarChar, 50 );
                  param.Value = "@employerID";
                  myCommand.Parameters.Add(param);


                  try
                  {
                        myConnection.Open();
                        dgCompensation.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                        dgCompensation.DataBind();
                  }
                  catch
                        (SqlException Sqlexc)
                  {
                        lblStatus.Text = "Error while Generating Data. Error is " + Sqlexc.ToString();
                  }
            }


do you know why only the datagrid's heading show and not the datagrid with
all the necessary data

thanks
0
 
LVL 9

Expert Comment

by:WinterMuteUK
ID: 17029842
The value shouldn't be '@employerId' the value should be the employer, for example if you db looks like this:

  employerId    | first   | last
    10              | Jeff   | Bloggs
    20              | Sir     | ReadAlot

you would pass in '20' as the value to see 'SirReadAlot'.

try passing in a correct value and see what you get.

Wint.
0
 
LVL 9

Expert Comment

by:WinterMuteUK
ID: 17029847
i.e. where you put:

   param.Value = "@employerID";

instead put:

   param.Value = "20";

Wint.
0
 

Author Comment

by:SirReadAlot
ID: 17029852
will do this
0
 

Author Comment

by:SirReadAlot
ID: 17029863
it works!!

But i can't predict which employerid will be chosen

thanks
0
 

Author Comment

by:SirReadAlot
ID: 17029866
sp looks like this

CREATE PROCEDURE sp_DestinationElements @employerId varchar(50) AS
Select distinct
employerId,
DestinationElement,  
Deduction
from DestinationElements
where employerId  = @employerID
0
 
LVL 9

Accepted Solution

by:
WinterMuteUK earned 500 total points
ID: 17029874
I would have thought the bottom line should read:

    where employerId = @employerId   --NOT @employerID  different cases

but I'm not sure how SqlServer deals with case...

Surely the input to the stored proc is selected by a user? If you want to just display all the employers then change the stored proc to be:

   select distinct
            employerId,
            DestinationElement,  
            Deduction
   from
            DestinationElements

but I imagine that's not what you want.

Wint.
0
 

Author Comment

by:SirReadAlot
ID: 17029916
just gonna debug
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

726 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