为什么在更新时出现此错误？附加信息：过程或函数Sp_Neetha指定了太多参数。 [英] Why Getting This Error When I Update?Additional Information: Procedure Or Function Sp_Neetha Has Too Many Arguments Specified.

问题描述

我正在尝试一个可编辑的网格，但它显示了一些我无法追踪的错误..请帮助我。我在...时收到这些错误...



< pre lang =css> 更新
附加 信息： 过程 或 function sp_neetha 许多 参数 指定。

删除

附加 信息： 连接 不 已关闭。 连接的当前 州 open 。

我的代码在这里......



我的桌面名称NEETHA （PLS阅读我的DOUBTS这是评论）

 创建 过程 sp_neetha（ @ id   int ， @school   nchar （ 10 ）， @office   nchar （ 10 ）， @flag   nchar （ 10 ））
  as  
  begin  
  if （@ flag = '  insert'）
 开始 
 插入 进入 neetha（id，school，office） values （ @ id ， @ school ， @ office ）
  end  
  if （@ flag = '  delete'）
 开始 
 删除 来自 neetha 其中 id = @ id 
 结束 
 如果 （@ flag = '  选择'）
 开始 
 选择 * 来自 neetha 
  end  
 < span class =code-keyword> if （@ flag = '  update'） 
 开始 
  update  neetha  set  school = @ school，office = @ office 其中 id = @ id 
  end  
 结束 

C＃CODE

使用System; 
使用System.Collections.Generic; 
使用System.Linq; 
使用System.Web; 
使用System.Web.UI; 
使用System.Web.UI.WebControls; 
使用System.Data; 
使用System.Data.SqlClient; 
 
 namespace加入
 {
公共部分类WebForm1：System.Web.UI.Page 
 {
 protected void Page_Load（object sender，EventArgs e） 
 {
 
 
 if（！IsPostBack）
 {
 
 
 grid（）; 
} 
} 
 
 SqlConnection con = new SqlConnection（@Data Source =（LocalDB）\ v11.0; AttachDbFilename = D：\ MyPractice \ join \ joins\App_Data \Database1.mdf; Integrated Security = True）; 
 SqlCommand cmd = new SqlCommand（）; 
 
 
 public void grid（）//在这里获取错误..我们应该在select查询中传递所有值吗？我给了cmd.Parameters.AddWithValue（@ flag，select）;仅显示预期参数，如... y？ 
 {
 con.Open（）; 
 cmd.Connection = con; 
 cmd.CommandText =sp_neetha; 
 cmd.CommandType = CommandType.StoredProcedure; 
 cmd.Parameters.AddWithValue（@ id，DbType.Int32）; 
 cmd.Parameters.AddWithValue（@ school，DbType.String）; 
 cmd.Parameters.AddWithValue（@ office，DbType.String）; 
 cmd.Parameters.AddWithValue（@ flag，select）; 
 
 cmd.ExecuteNonQuery（）; 
 
 SqlDataAdapter da = new SqlDataAdapter（cmd）; 
 DataTable dt = new DataTable（）; 
 da.Fill（dt）; 
 con.Close（）; 
 GridView1.DataSource = dt; 
 GridView1.DataBind（）; 
 
 
 
} 
 
 
 
 protected void GridView1_RowEditing（object sender，GridViewEditEventArgs e）
 {
 GridView1.EditIndex = e.NewEditIndex; 
 
 grid（）; 
} 
 
 protected void GridView1_RowUpdating（object sender，GridViewUpdateEventArgs e）
 {
 
 int id = Convert.ToInt32（GridView1.DataKeys [e.RowIndex] ] .Values [ ID]的ToString（））; 
 
 
 TextBox txtid =（TextBox）GridView1.Rows [e.RowIndex] .FindControl（txteditid）; 
 TextBox txtschool =（TextBox）GridView1.Rows [e.RowIndex] .FindControl（txteditscool）; 
 TextBox txtoffs =（TextBox）GridView1.Rows [e.RowIndex] .FindControl（txteditoffs）; 
 con.Open（）; 
 cmd.Connection = con; 
 cmd.CommandText =sp_neetha; 
 cmd.CommandType = CommandType.StoredProcedure; 
 cmd.Parameters.AddWithValue（@ id，txtid.Text）; 
 cmd.Parameters.AddWithValue（@ school，txtschool.Text）; 
 cmd.Parameters.AddWithValue（@ office，txtoffs.Text）; 
 
 cmd.Parameters.AddWithValue（@ flag，update）; 
 
 cmd.ExecuteNonQuery（）; 
 con.Close（）; 
 GridView1.EditIndex = -1; 
 grid（）; 
 
 
} 
 
 
 protected void Button6_Click（object sender，EventArgs e）
 {
 grid（）; 
} 
 
 protected void GridView1_RowDeleting（object sender，GridViewDeleteEventArgs e）
 {
 
 int id1 = Convert.ToInt32（GridView1.DataKeys [e.RowIndex ] .Values [ ID]的ToString（））; 
 con.Open（）; 
 cmd.Connection = con; 
 cmd.CommandText =sp_neetha; 
 cmd.CommandType = CommandType.StoredProcedure; 
 cmd.Parameters.AddWithValue（@ id，DbType.Int32）; 
 cmd.Parameters.AddWithValue（@ school，DbType.String）; 
 cmd.Parameters.AddWithValue（@ office，DbType.String）; 
 cmd.Parameters.AddWithValue（@ flag，delete）; 
 grid（）; 
 int result = cmd.ExecuteNonQuery（）; 
 if（result == 1）
 {
 
 grid（）; 
 
} 
 
 
 con.Close（）; 
} 
 
 protected void GridView1_RowCommand（object sender，GridViewCommandEventArgs e）
 {
 
 
 if（e.CommandName.Equals（add ））
 {
 
 
 TextBox txtid1 =（TextBox）GridView1.FooterRow.FindControl（txteditfooter）; 
 TextBox txtschool1 =（TextBox）GridView1.FooterRow.FindControl（txteditscool）; 
 TextBox txtoffs1 =（TextBox）GridView1.FooterRow.FindControl（txteditfooterofs）; 
 
 con.Open（）; 
 cmd.Connection = con; 
 cmd.CommandText =sp_neetha; 
 cmd.CommandType = CommandType.StoredProcedure; 
 cmd.Parameters.AddWithValue（@ id，txtid1.Text）; 
 cmd.Parameters.AddWithValue（@ school，txtschool1.Text）; 
 cmd.Parameters.AddWithValue（@ office，txtoffs1.Text）; 
 cmd.Parameters.AddWithValue（@ flag，insert）; 
 int result = cmd.ExecuteNonQuery（）; 
 if（result == 1）
 {
 
 GridView1.EditIndex = -1; 
 grid（）; 
 
 
 
} 
 
 con.Close（）; 
 
 
 
 
} 
} 
 
 protected void GridView1_RowCancelingEdit（object sender，GridViewCancelEditEventArgs e）
 {
 GridView1.EditIndex = -1; 
 grid（）; 
} 
 
 
 
} 
} 

解决方案

为什么不能使用varchar而不是nChar（存储过程变量）？



我刚刚读了你的问题，看起来没问题...试一试



在为SqlCommand分配参数之前清除参数

cmd.Parameters.Clear（）;



让我知道如果问题仍然存在，我可以进一步检查...

根据你的代码看起来你将命令存储在类级变量或类似的。为什么？而不是这样做只需在你使用它时定义命令。



类似

 ... 
 
 con.Open（）; 
 SqlCommand cmd =  new  SqlCommand（  sp_neetha ，con）; 
 cmd.CommandType = CommandType.StoredProcedure; 
 cmd.Parameters.AddWithValue（  @ id，txtid.Text）; 
 cmd.Parameters.AddWithValue（  @ school，txtschool.Text）; 
 cmd.Parameters.AddWithValue（  @ office，txtoffs.Text）; 
 cmd.Parameters.AddWithValue（  @ flag，  update）; 
 
 cmd.ExecuteNonQuery（）; 
 con.Close（）; 
 ... 

这样做你不必担心命令是否已被使用等等。

你好，

在Delete方法中，你调用了grid（），它负责出错。在该方法（GridView1_RowDeleting）中，您已经打开了连接并从数据库加载数据。现在，当再次打开连接时，您尝试通过调用grid（）来打开连接，这会引发错误。因此，首先关闭连接然后调用grid（）方法。

 con.open（）; 
 //其他东西
 con.close（）; 
 
 //最后调用方法
 grid（）; 

I am trying an editable grid but it showing some error which I cant trace..please help me .I am getting these errors when...

Update
Additional information: Procedure or function sp_neetha has too many arguments specified.

Delete

Additional information: The connection was not closed. The connection's current state is open.

my code is here...

MY STORED PROCEDURE FOR TABLE NAME NEETHA( PLS READ MY DOUBTS WHICH IS COMMENTED)

create procedure sp_neetha (@id int ,@school nchar(10),@office nchar(10),@flag nchar(10))
as
begin
if(@flag='insert')
begin
insert into neetha (id,school ,office) values(@id,@school,@office)
end
if(@flag='delete')
begin
delete from neetha where id=@id
end
if(@flag='select')
begin
select * from neetha
end
if(@flag='update')
begin
update neetha set school=@school,office=@office where id=@id
end
end

C# CODE

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace joins
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {


            if(!IsPostBack)
            {


                grid();
            }
        }

        SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=D:\MyPractice\joins\joins\App_Data\Database1.mdf;Integrated Security=True");
        SqlCommand cmd = new SqlCommand();


        public void grid()//HERE AM GETTING ERRORS..should we pass all the values in in select query? I gave cmd.Parameters.AddWithValue("@flag", "select"); only but showing expected parameter like... y?
        {
            con.Open();
            cmd.Connection = con;
            cmd.CommandText = "sp_neetha";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@id",DbType.Int32);
            cmd.Parameters.AddWithValue("@school", DbType.String);
            cmd.Parameters.AddWithValue("@office", DbType.String);
            cmd.Parameters.AddWithValue("@flag", "select");
            
            cmd.ExecuteNonQuery();
            
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt=new DataTable();
            da.Fill(dt);
            con.Close();
            GridView1.DataSource = dt;
            GridView1.DataBind();

          

        }



        protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
        {
            GridView1.EditIndex = e.NewEditIndex;
          
            grid();
        }

        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {

            int id =   Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["id"].ToString());


            TextBox txtid = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txteditid");
            TextBox txtschool = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txteditscool");
            TextBox txtoffs = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txteditoffs");
            con.Open();
            cmd.Connection = con;
            cmd.CommandText = "sp_neetha";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@id", txtid.Text);
            cmd.Parameters.AddWithValue("@school", txtschool.Text);
            cmd.Parameters.AddWithValue("@office", txtoffs.Text);

            cmd.Parameters.AddWithValue("@flag","update");

            cmd.ExecuteNonQuery();
            con.Close();
            GridView1.EditIndex = -1;
            grid();


        }


        protected void Button6_Click(object sender, EventArgs e)
        {
            grid();
        }

        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {

            int id1=   Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["id"].ToString());
            con.Open();
            cmd.Connection = con;
            cmd.CommandText = "sp_neetha";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@id", DbType.Int32);
            cmd.Parameters.AddWithValue("@school", DbType.String);
            cmd.Parameters.AddWithValue("@office", DbType.String);
            cmd.Parameters.AddWithValue("@flag", "delete");
            grid();
           int result = cmd.ExecuteNonQuery();
           if (result==1)
           {

               grid();

            }


            con.Close();
      }

        protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
        {


            if(e.CommandName.Equals("add"))
            {


                TextBox txtid1 = (TextBox)GridView1.FooterRow.FindControl("txteditfooter");
                TextBox txtschool1 = (TextBox)GridView1.FooterRow.FindControl("txteditscool");
                TextBox txtoffs1 = (TextBox)GridView1.FooterRow.FindControl("txteditfooterofs");
              
                con.Open();
                cmd.Connection = con;
                cmd.CommandText = "sp_neetha";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@id", txtid1.Text);
                cmd.Parameters.AddWithValue("@school", txtschool1.Text);
                cmd.Parameters.AddWithValue("@office", txtoffs1.Text);
                cmd.Parameters.AddWithValue("@flag", "insert");
                int result = cmd.ExecuteNonQuery();
                if(result==1)
                {

                    GridView1.EditIndex = -1;
                        grid();

                      

                }

                con.Close();




            }
        }

        protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            GridView1.EditIndex = -1;
            grid();
        }



    }
}

解决方案

Why can't you use varchar instead of nChar (Store procedure variables) ?

I just read your question, it seems ok... just give it a try

Clear Parameters before assigning parameters to the SqlCommand
cmd.Parameters.Clear();

let me know if problem still persist, I can check further...

Based on your code it looks like you store the command in a class level variable or similar. Why? Instead of doing that simply define the command when you use it.

Something like

...

            con.Open();
            SqlCommand cmd = new SqlCommand("sp_neetha", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@id", txtid.Text);
            cmd.Parameters.AddWithValue("@school", txtschool.Text);
            cmd.Parameters.AddWithValue("@office", txtoffs.Text);
            cmd.Parameters.AddWithValue("@flag","update");
 
            cmd.ExecuteNonQuery();
            con.Close();
...

Making it this way you don't need to worry if the command is used already and so on.

Hello ,
In the Delete method , you have called grid() , which is responsible for the error . In that method (GridView1_RowDeleting) you have already opened the connection and load the data from database .Now , while the connection is opened again you try to open the connection by calling the grid() , which throws error . So, first close the connection then call that grid() method .

con.open();
//other stuffs
con.close();

//last call the method
grid();

这篇关于为什么在更新时出现此错误？附加信息：过程或函数Sp_Neetha指定了太多参数。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！