ASP.Net与SQL Server 2008 Express&存储过程！ [英] ASP.Net with SQL Server 2008 Express & Stored Procedures !

问题描述

我在SQL Server Express实例中创建了以下存储过程。



我正在使用 Visual Studio 2010 Professional和SQl Server 2008 Express版。

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
/****** 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

当我执行SP时，我得到以下错误或者！

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)

我使用ASP.Net网络表格将数据插入数据库表格。

I have used an ASP.Net web form to insert data to the database table.

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();
            
            }

有人可以帮我解决这个问题！





谢谢

Chiranthaka

Could someone help me to solve this !


Thanks
Chiranthaka

推荐答案

尝试添加像这样的参数：

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);

这篇关于ASP.Net与SQL Server 2008 Express&存储过程！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！