Click here to Skip to main content
15,908,013 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
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.
SQL
USE [LeaveMgt]
GO
/****** Object:  StoredProcedure [dbo].[LeaveApply]    Script Date: 05/10/2013 02:06:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[LeaveApply]
	/*
	(
	@parameter1 int = 5,
	@parameter2 datatype OUTPUT
	)
	*/
	@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) 
	/* SET NOCOUNT ON */
	END


When I execute the SP I got the following error!

SQL
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.

C#
public partial class LeaveApplying : System.Web.UI.Page
    {
        /*-----------Setting database connection!-------------------*/
        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
            {
                /*-----------Setting data adapter--------------*/
                SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM LeaveApplying", con);

                /*-----------Setting data set----------------*/
                DataSet ds = new DataSet();

                /*-----------Filling the Data adapter-------------*/
                da.Fill(ds, "LeaveApplying");

                lblMessage.Text = "Okkkkkkkkkkkkk";

            }
            catch {

                lblMessage.Text = "baddddd";
            
            }
            /*---------Opening the database connection------*/
            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");
           
            /*----------Commanding the record insertion-----------*/
            SqlCommand comm = new SqlCommand();
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText="LeaveApply";


            try
            {
                /*----------Using SQL Stred Procedure to inset data into the table--------- */

                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
Posted

1 solution

Try adding parameters like this:
SqlParameter dataParameter = new SqlParameter();
dataParameter.Value = txtEmpNo.Text.Trim();
dataParameter.ParameterName = "@EmpNo";

SqlParameter dataParameter2 = new SqlParameter();
dataParameter2.Value = txtEmpName.Text.Trim();
dataParameter2.ParameterName = "@EmpName";

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(dataParameter);
cmd.Parameters.Add(dataParameter2);
 
Share this answer
 
v3
Comments
Chiranthaka Sampath 9-May-13 17:34pm    
Ok pal then can I use this same technique to insert many parameters ? As an example like the following

dataParameter.Value = txtEmpName.Text.Trim();
dataParameter.ParameterName = "@EmpName";

dataParameter.Value = txtDesignation.Text.Trim();
dataParameter.ParameterName = "@Designation";

dataParameter.Value = txtDept.Text.Trim();
dataParameter.ParameterName = "@Dept";


cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(dataParameter);


Likewise as could as much !
Richard C Bishop 9-May-13 17:38pm    
Yes, just be sure to create a new parameter each time. See my updated solution above.
Chiranthaka Sampath 9-May-13 17:55pm    
When I execute the SP in VS 2010 Server explorer I got the following error!

"The Stored procedure <[dbo].[LeaveApply] requires the following parameter. At a table The Type(sql datatype), Direction, Name(parameter), Value (DEFAULT) shows! I think not assigning of the values is the problem not the assigning method. Could you kindly correct me!
Richard C Bishop 9-May-13 17:59pm    
Are you filling in all your text boxes with the data your stored procedure requires when you run it? It will not work if they are empty.
Chiranthaka Sampath 9-May-13 18:01pm    
Certainly I am filling all the text boxes!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900