Parameter Error in Stored Procedure

Pravinkumar Birajdar

7y876Learn iOS Programming

Error:

Procedure or function 'SaveUpdateEmp' expects parameter '@EmpID', which was not supplied.

Stored Procedure:

CREATE PROCEDURE [dbo].[SaveUpdateEmp]
@EmpID varchar(10),
@EmpName varchar(100),
@DesID int,
@Salary money,
@Result varchar(50) out
AS
begin
declare @ID varchar(10)
select @ID= EmpID from Emp where EmpName=@EmpName
if(@ID<>'' and @EmpID='')
begin
set @Result='Employee name is already exist !!!'
end
else
begin
if(@EmpID='')
begin
insert into Emp(EmpName,DesID,Salary) values(@EmpName,@DesID,@Salary)
set @Result='Employee saved successfully'
end
else
begin
update Emp set EmpName=@EmpName,DesID=@DesID,Salary=@Salary where EmpID=@EmpID
set @Result='Employee updated successfully'
end
end
end

protected void btnSave_Click(object sender, EventArgs e)
{
try
{
SqlConnection con = new SqlConnection(Commons.GetConnectionString);
SqlCommand cmd = new SqlCommand("SaveUpdateEmp", con);
cmd.Parameters.AddWithValue("@EmpID", HiddenField1.Value);
cmd.Parameters.AddWithValue("@EmpName", txtEmpName.Text);
cmd.Parameters.AddWithValue("@DesID", ddlDesignation.SelectedValue);
cmd.Parameters.AddWithValue("@Salary", txtSalary.Text);
cmd.Parameters.Add("@Result", SqlDbType.VarChar,30);
cmd.Parameters["@Result"].Direction = ParameterDirection.Output;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
lblMsg.Text = cmd.Parameters["@Result"].Value.ToString();
}
catch (Exception ex)
{
throw ex;
}
}

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Gopaiah Nekarukanti7y ago
Accepted answer
Try to add the follwing minor changes in to your code and check

// since you are not handling null check in procedure add null check in code, you are just checking condition @EmpID='' but your procedure will give wrong result if HiddenField1.Value is null.

var employeeId = HiddenField1.Value != null ? HiddenField1.Value : string.Empty;
cmd.Parameters.AddWithValue("@EmpID", employeeId );

//Add after cmd initialization
cmd.CommandType = System.Data.CommandType.StoredProcedure;
+1
Puneet Kankar7y ago
Hi Pravin,
Make sure you pass empid not null of hidden field if it is null try given below query.
alter PROCEDURE [dbo].[SaveUpdateEmp]
@EmpID varchar(10) =null,
@EmpName varchar(100),
@DesID int,
@Salary money,
@Result varchar(50) out
AS
begin
declare @ID varchar(10)
select @ID= EmpID from Emp where EmpName=@EmpName
if(@ID<>'' and isnull(@EmpID,'')='')
begin
set @Result='Employee name is already exist !!!'
end
else
begin
if(isnull(@EmpID,'')='')
begin
insert into Emp(EmpName,DesID,Salary) values(@EmpName,@DesID,@Salary)
set @Result='Employee saved successfully'

end
else
begin
update Emp set EmpName=@EmpName,DesID=@DesID,Salary=@Salary where EmpID=@EmpID
set @Result='Employee updated successfully'
end
end
end
0
Pravinkumar Birajdar7y ago
Actually I have set Identity Column to EmpID, thats why I haven't mentioned EmpID in insert query
CREATE TABLE [dbo].[Emp] (
    [EmpID]   INT           IDENTITY (1, 1) NOT NULL,
    [EmpName] VARCHAR (100) NULL,
    [DesID]   INT           NULL,
    [Salary]  MONEY         NULL,
    PRIMARY KEY CLUSTERED ([EmpID] ASC)
);
0
Madan Shekar7y ago
insert into Emp(EmpName,DesID,Salary) values(@EmpName,@DesID,@Salary)

please check above query i think you are missing EmpId.
0