I have created the following Stored Procedure in an SQL Server Express Instance.
I am using
Visual Studio 2010 Professional and SQl Server 2008 Express edition.
USE [LeaveMgt]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[LeaveApply]
@EmpNo varchar(6),
@EmpName varchar(50),
@Desig varchar(50),
@Dept varchar(50),
@NodCas int,
@NodAnu int,
@NodSick int,
@LevTkCas int,
@LevTkAnu int,
@LevTkSick int,
@DateAppoin date,
@LevComFrm date,
@LevComTo date,
@ResLev varchar(100),
@DateApply date
AS
BEGIN
INSERT INTO LeaveApplying(EmpNo, EmpName, Designation, Dept, NodCas, NodAnu, NodSick, LevTkCas, LevTkAnu, LevTkSick, DateAppoin, LevComFrm, LevComTo, ResLev, DateApply)
VALUES
(@EmpNo,
@EmpName,
@Desig,
@Dept,
@NodCas,
@NodAnu,
@NodSick,
@LevTkCas,
@LevTkAnu,
@LevTkSick,
@DateAppoin,
@LevComFrm,
@LevComTo,
@ResLev,
@DateApply)
END
When I execute the SP I got the following error!
Msg 201, Level 16, State 4, Procedure LeaveApply, Line 0
Procedure or function 'LeaveApply' expects parameter '@EmpNo', which was not supplied.
(1 row(s) affected)
I have used an ASP.Net web form to insert data to the database table.
public partial class LeaveApplying : System.Web.UI.Page
{
public SqlConnection con = new SqlConnection();
protected void Page_Load(object sender, EventArgs e)
{
try
{
con.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\MyProjects\LeaveMgt\LeaveMgt\App_Data\LeaveMgt.mdf;Integrated Security=True;User Instance=True";
lblMessage.Text = "ok";
}
catch {
lblMessage.Text = "bad";
}
try
{
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM LeaveApplying", con);
DataSet ds = new DataSet();
da.Fill(ds, "LeaveApplying");
lblMessage.Text = "Okkkkkkkkkkkkk";
}
catch {
lblMessage.Text = "baddddd";
}
con.Open();
setDefVal();
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
CommonVariables.strEmpNo = txtEmpNo.Text;
CommonVariables.strEmpName = txtEmpName.Text;
CommonVariables.strLevApplyDate = txtDtLevApp.Text;
resetVal();
Response.Redirect("LeaveCoverUp.aspx");
SqlCommand comm = new SqlCommand();
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText="LeaveApply";
try
{
comm.Parameters.Add("@EmpNo", SqlDbType.VarChar).Value = txtEmpNo.Text.Trim();
comm.Parameters.Add("@EmpName", SqlDbType.VarChar).Value = txtEmpName.Text.Trim();
comm.Parameters.Add("@Desig", SqlDbType.VarChar).Value = txtDesig.Text.Trim();
comm.Parameters.Add("@Dept", SqlDbType.VarChar).Value = txtDept.Text.Trim();
comm.Parameters.Add("@NodCas", SqlDbType.Int).Value = txtNodCas.Text.Trim();
comm.Parameters.Add("@NodAnu", SqlDbType.Int).Value = txtNodAnu.Text.Trim();
comm.Parameters.Add("@NodSick", SqlDbType.Int).Value = txtNodSick.Text.Trim();
comm.Parameters.Add("@LevTkCas", SqlDbType.Int).Value = txtLevTkCas.Text.Trim();
comm.Parameters.Add("@LevTkAnu", SqlDbType.Int).Value = txtLevTkAnu.Text.Trim();
comm.Parameters.Add("@LevTkSick", SqlDbType.Int).Value = txtLevTkSick.Text.Trim();
comm.Parameters.Add("@DateAppoin", SqlDbType.Date).Value = txtDtApp.Text.Trim();
comm.Parameters.Add("@LevComFrm", SqlDbType.Date).Value = txtLevFrm.Text.Trim();
comm.Parameters.Add("@LevComTo", SqlDbType.Date).Value = txtLevTo.Text.Trim();
comm.Parameters.Add("@ResLev", SqlDbType.VarChar).Value = txtResLev.Text.Trim();
comm.Parameters.Add("@DateApply", SqlDbType.Date).Value = txtDtLevApp.Text.Trim();
comm.Connection = con;
con.Open();
comm.ExecuteNonQuery();
lblMessage.Text = "Leave Applied !";
lblMessage.ForeColor = System.Drawing.Color.FromName("green");
}
catch
{
lblMessage.Text = "Leave Did Not Applied !";
lblMessage.ForeColor = System.Drawing.Color.FromName("red");
}
finally {
con.Close();
con.Dispose();
}
Could someone help me to solve this !
Thanks
Chiranthaka