asked on

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

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.

WinterMuteUK

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.

SirReadAlot

ASKER

will try this

SirReadAlot

ASKER

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

WinterMuteUK

Hi, sorry,

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

Wint

SirReadAlot

ASKER

okay

SirReadAlot

ASKER

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

SirReadAlot

ASKER

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

WinterMuteUK

It should look like this:

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

Wint.

SirReadAlot

ASKER

okay

SirReadAlot

ASKER

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

WinterMuteUK

WinterMuteUK

i.e. where you put:

param.Value = "@employerID";

instead put:

param.Value = "20";

Wint.

SirReadAlot

ASKER

will do this

SirReadAlot

ASKER

it works!!

But i can't predict which employerid will be chosen

thanks

SirReadAlot

ASKER

sp looks like this

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

ASKER CERTIFIED SOLUTION

WinterMuteUK

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SirReadAlot

ASKER

just gonna debug