SirReadAlot
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.AppS ettings["c onnectLoca l"];
SqlConnection myConnection = new SqlConnection(ConnectionSt ring);
SqlCommand myCommand = new SqlCommand("sp_Destination Elements", myConnection);
myCommand.CommandType = CommandType.StoredProcedur e;
try
{
myConnection.Open();
dgCompensation.DataSource = myCommand.ExecuteReader(Co mmandBehav ior.CloseC onnection) ;
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.SqlE xception: Procedure 'sp_DestinationElements' expects parameter '@employerId', which was not supplied. at System.Data.SqlClient.SqlC ommand.Exe cuteReader (CommandBe havior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlC ommand.Exe cuteReader (CommandBe havior behavior) at AutoDataCapture.MapCompens ationEleme nts.BindGr id() in c:\inetpub\wwwroot\autodat acapture_2 \mapcompen sationelem ents.aspx. cs:line 99
How can I rectify this?
thanks.
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.AppS
SqlConnection myConnection = new SqlConnection(ConnectionSt
SqlCommand myCommand = new SqlCommand("sp_Destination
myCommand.CommandType = CommandType.StoredProcedur
try
{
myConnection.Open();
dgCompensation.DataSource = myCommand.ExecuteReader(Co
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.SqlE
How can I rectify this?
thanks.
ASKER
will try this
ASKER
hi, don't really know where to add the code
private void BindGrid()
{
string ConnectionString = ConfigurationSettings.AppS ettings["c onnectLoca l"];
SqlConnection myConnection = new SqlConnection(ConnectionSt ring);
SqlCommand myCommand = new SqlCommand("sp_Destination Elements", myConnection);
myCommand.CommandType = CommandType.StoredProcedur e;
try
{
myConnection.Open();
dgCompensation.DataSource = myCommand.ExecuteReader(Co mmandBehav ior.CloseC onnection) ;
dgCompensation.DataBind();
}
catch
(SqlException Sqlexc)
{
lblStatus.Text = "Error while Generating Data. Error is " + Sqlexc.ToString();
}
}
#endregio
private void BindGrid()
{
string ConnectionString = ConfigurationSettings.AppS
SqlConnection myConnection = new SqlConnection(ConnectionSt
SqlCommand myCommand = new SqlCommand("sp_Destination
myCommand.CommandType = CommandType.StoredProcedur
try
{
myConnection.Open();
dgCompensation.DataSource = myCommand.ExecuteReader(Co
dgCompensation.DataBind();
}
catch
(SqlException Sqlexc)
{
lblStatus.Text = "Error while Generating Data. Error is " + Sqlexc.ToString();
}
}
#endregio
Hi, sorry,
just place it under the bit where you have 'myCommand.commandType = CommandType.StoredProcedur e'
Wint
just place it under the bit where you have 'myCommand.commandType = CommandType.StoredProcedur
Wint
ASKER
okay
ASKER
I have done thsi
private void BindGrid()
{
string ConnectionString = ConfigurationSettings.AppS ettings["c onnectLoca l"];
SqlConnection myConnection = new SqlConnection(ConnectionSt ring);
SqlCommand myCommand = new SqlCommand("sp_Destination Elements", myConnection);
myCommand.CommandType = CommandType.StoredProcedur e;
SqlParameter param = new SqlParameter("@employerID" ,"varchar" );
param.Value = THEID;
myCommand.Parameters.Add(p aram);
try
{
myConnection.Open();
dgCompensation.DataSource = myCommand.ExecuteReader(Co mmandBehav ior.CloseC onnection) ;
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
private void BindGrid()
{
string ConnectionString = ConfigurationSettings.AppS
SqlConnection myConnection = new SqlConnection(ConnectionSt
SqlCommand myCommand = new SqlCommand("sp_Destination
myCommand.CommandType = CommandType.StoredProcedur
SqlParameter param = new SqlParameter("@employerID"
param.Value = THEID;
myCommand.Parameters.Add(p
try
{
myConnection.Open();
dgCompensation.DataSource = myCommand.ExecuteReader(Co
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
ASKER
i fink this is it
private void BindGrid()
{
string ConnectionString = ConfigurationSettings.AppS ettings["c onnectLoca l"];
SqlConnection myConnection = new SqlConnection(ConnectionSt ring);
SqlCommand myCommand = new SqlCommand("sp_Destination Elements", myConnection);
myCommand.CommandType = CommandType.StoredProcedur e;
SqlParameter param = new SqlParameter("@employerID" ,"varchar" );
param.Value = "employerID";
myCommand.Parameters.Add(p aram);
try
{
myConnection.Open();
dgCompensation.DataSource = myCommand.ExecuteReader(Co mmandBehav ior.CloseC onnection) ;
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
private void BindGrid()
{
string ConnectionString = ConfigurationSettings.AppS
SqlConnection myConnection = new SqlConnection(ConnectionSt
SqlCommand myCommand = new SqlCommand("sp_Destination
myCommand.CommandType = CommandType.StoredProcedur
SqlParameter param = new SqlParameter("@employerID"
param.Value = "employerID";
myCommand.Parameters.Add(p
try
{
myConnection.Open();
dgCompensation.DataSource = myCommand.ExecuteReader(Co
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
It should look like this:
SqlParameter param = new SqlParameter( "@employerID", SqlDbType.VarChar, 50 );
Wint.
SqlParameter param = new SqlParameter( "@employerID", SqlDbType.VarChar, 50 );
Wint.
ASKER
okay
ASKER
Hi,
I used this
private void BindGrid()
{
string ConnectionString = ConfigurationSettings.AppS ettings["c onnectLoca l"];
SqlConnection myConnection = new SqlConnection(ConnectionSt ring);
SqlCommand myCommand = new SqlCommand("sp_Destination Elements", myConnection);
myCommand.CommandType = CommandType.StoredProcedur e;
SqlParameter param = new SqlParameter( "@employerID", SqlDbType.VarChar, 50 );
param.Value = "@employerID";
myCommand.Parameters.Add(p aram);
try
{
myConnection.Open();
dgCompensation.DataSource = myCommand.ExecuteReader(Co mmandBehav ior.CloseC onnection) ;
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
I used this
private void BindGrid()
{
string ConnectionString = ConfigurationSettings.AppS
SqlConnection myConnection = new SqlConnection(ConnectionSt
SqlCommand myCommand = new SqlCommand("sp_Destination
myCommand.CommandType = CommandType.StoredProcedur
SqlParameter param = new SqlParameter( "@employerID", SqlDbType.VarChar, 50 );
param.Value = "@employerID";
myCommand.Parameters.Add(p
try
{
myConnection.Open();
dgCompensation.DataSource = myCommand.ExecuteReader(Co
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
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.
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.
i.e. where you put:
param.Value = "@employerID";
instead put:
param.Value = "20";
Wint.
param.Value = "@employerID";
instead put:
param.Value = "20";
Wint.
ASKER
will do this
ASKER
it works!!
But i can't predict which employerid will be chosen
thanks
But i can't predict which employerid will be chosen
thanks
ASKER
sp looks like this
CREATE PROCEDURE sp_DestinationElements @employerId varchar(50) AS
Select distinct
employerId,
DestinationElement,
Deduction
from DestinationElements
where employerId = @employerID
CREATE PROCEDURE sp_DestinationElements @employerId varchar(50) AS
Select distinct
employerId,
DestinationElement,
Deduction
from DestinationElements
where employerId = @employerID
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
just gonna debug
You need to add the Parameter to your command:
SqlParameter param = new SqlParameter("@employerID"
param.Value = THEID;
myCommand.Parameters.Add(p
then try calling it.
(I may have the args wrong for constructing a new Parameter, but the intellisense will put you right!! :))
Wint.