存储过程异常 [英] Stored Proceedure Exception

问题描述

我已经使用了两张桌子，我一次只插入两张桌子，桌子用外键重新连接：

i have used, two tables, i m inserting in two tables at a time, and tables are rellated with foreign key:

protected void btn_submit_Click(object sender, EventArgs e)
    {
        cnn.ConnectionString = ConfigurationManager.ConnectionStrings["jin"].ConnectionString;
        try
        {
            string path = @"~/images/";
            if (FileUpload1.HasFile)
            {
                string path1 = Server.MapPath(path);
                string fnm = FileUpload1.FileName;
                string p = path1 + fnm;
                FileUpload1.SaveAs(p);
                string img = @"~/images" + fnm;

                SqlCommand cm = new SqlCommand("menu", cnn);
                cm.CommandType = CommandType.StoredProcedure;
                cm.CommandText = "str_collection";
                cm.Parameters.Add("@itemname", txt_itemname.Text);
                cm.Parameters.Add("@item_img", img);
                SqlCommand cm1 = new SqlCommand("submenu", cnn);
                cm1.CommandType = CommandType.StoredProcedure;
                cm1.CommandText = "str_collection";
                cm1.Parameters.Add("@item_descript", txt_descript.Text);
                cm1.Parameters.Add("@item_rate",txt_rate.Text);
                cm1.Parameters.Add("@subitem_name", txt_subitem.Text);
                cnn.Open();
                 SqlDataReader dr = cm.ExecuteReader();
                SqlDataReader dr1 = cm1.ExecuteReader();
                cnn.Close();
                Response.Write("<script>alert('Your Collection is Done Successfully!!')</script>");
            }
        }
        catch (Exception ex)
        {
            lbl_msg.Text = ex.Message;
            //Response.Write("<script>alert('There is some problem')</script>");
        }

存储过程是：

The Stored Proceedure is:

ALTER PROCEDURE [dbo].[str_collection]
@itemname nvarchar(50),
@item_img nvarchar(MAX)
AS
BEGIN
SET NOCOUNT ON
declare @subitem_name nvarchar(50)
declare @item_rate decimal(18,2)
declare @item_descript nvarchar(100)
declare @id varchar(50)
SET @id= COUNT(Getdate())
declare @item_id nvarchar(50)
SET @item_id=@itemname+left(convert(varchar,datepart(YYYY,GETDATE())),2)+@id
INSERT INTO menu(itemname,item_id,item_img)values(@itemname,@item_id,@item_img)
INSERT INTO submenu(item_id,item_descript,item_rate,subitem_name)values(@item_id,@item_descript,@item_rate,@subitem_name)
END

异常：我得到的是：

已经有一个与此命令关联的开放DataReader必须先关闭。



请让我解决这个问题......

The Exception: I m getting is:
There is already an open DataReader associated with this Command which must be closed first.

Please make me, resolve this...

推荐答案

你为什么使用



SqlDataReader dr = cm.ExecuteReader（）;

SqlDataReader dr1 = cm1.ExecuteReader（）;



插入数据，



尝试使用，

cm.ExecuteScalar（）插入值。



希望这可以解决您的问题。

Why you are using

SqlDataReader dr = cm.ExecuteReader();
SqlDataReader dr1 = cm1.ExecuteReader();

to insert the data,

try to use,
cm.ExecuteScalar() to insert the values.

hopes this can solve your problem.

是因为没有关闭一个datareader而引发的异常，因此您无法创建具有相同连接的另一个。您可以使用（ var <更新您的代码，例如

Yes the exception raised because without closing one datareader then you can not create another with same connection. You can update your code like

using (var conn = new SqlConnection(connString))
{
    string sql1 = "INSERT INTO MyTable1(Name) VALUES ('Morning')";
    string sql2 = "INSERT INTO MyTable2(Name) VALUES ('Evening')";
    var cmd1 = new SqlCommand(sql1, conn);
    var cmd2 = new SqlCommand(sql2, conn);
    conn.Open();
    using(SqlDataReader reader1 = cmd1.ExecuteReader()){}
    using(SqlDataReader reader2 = cmd2.ExecuteReader()){}
}

但我建议你是否需要执行sp只有动作查询（插入/更新/然后执行SqlCommand对象的ExecuteNonQuery方法。

But I suggest if you need to execute sp where only action query(insert/update/delete) and no return row set then execute ExecuteNonQuery method of SqlCommand object.

using (var conn = new SqlConnection(connString))
{
    string sql1 = "INSERT INTO MyTable1(Name) VALUES ('Morning')";
    string sql2 = "INSERT INTO MyTable2(Name) VALUES ('Evening')";
    var cmd1 = new SqlCommand(sql1, conn);
    var cmd2 = new SqlCommand(sql2, conn);
    conn.Open();
    cmd1.ExecuteNonQuery();
    cmd2.ExecuteNonQuery();
}

试试这个：

Try this:

protected void btn_submit_Click(object sender, EventArgs e)
{
    cnn.ConnectionString = ConfigurationManager.ConnectionStrings["jin"].ConnectionString;
    try
    {
        string path = @"~/images/";
        if (FileUpload1.HasFile)
        {
            string path1 = Server.MapPath(path);
            string fnm = FileUpload1.FileName;
            string p = path1 + fnm;
            FileUpload1.SaveAs(p);
            string img = @"~/images" + fnm;    
            cnn.Open(); //Open the connection to execute first command
            SqlCommand cm = new SqlCommand("str_collection", cnn);
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.Add("@itemname", txt_itemname.Text);
            cm.Parameters.Add("@item_img", img);
            cm.Parameters.Add("@item_descript", txt_descript.Text);
            cm.Parameters.Add("@item_rate",txt_rate.Text);
            cm.Parameters.Add("@subitem_name", txt_subitem.Text);
            cm.ExecuteNoneQuery();
            cnn.Close();//Close the connection
            Response.Write("<script>alert('Your Collection is Done Successfully!!')</script>");
        }
    }
    catch (Exception ex)
    {
        lbl_msg.Text = ex.Message;
        //Response.Write("<script>alert('There is some problem')</script>");
    }
}

- 发送

--Amit

这篇关于存储过程异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！