保存按钮，向两个表插入数据，运用事务处理，出错：无效操作。连接被关闭。

Question

代码如下： protected void saveBtn_click(object sender,EventArgs e)
{
SqlConnection Conn1=new SqlConnection(Conn);
Conn1.Open();  for( int i = 0; i < tempGrid.Items.Count; i++ )
{
// 启动一个事务 SqlTransaction myTrans = Conn1.BeginTransaction();
             
SqlCommand Cmd1 = new SqlCommand();
Cmd1.Connection=Conn1;
Cmd1.Transaction = myTrans; SqlCommand Cmd = new SqlCommand();
Cmd.Connection=Conn1;
Cmd.Transaction = myTrans;
Sql1="insert into bigSaleList(buyerName,buyerNum,buyerYwy,beginDate,endDate,beginDate1,endDate1,saleKind,saleFee,saleShop,saleRe,bigSaleId) values (@buyer_Name,@buyer_Num,@buyer_Ywy,@begin_Date,@end_Date,@begin_Date1,@end_Date1,@sale_Kind,@sale_Fee,@sale_Shop,@sale_Re,@bigSale_Id)";
Sql= "insert into bigSaleList(bigSaleId,proNumber,proName,proGuige,proOldPrice,proOldSale,proBigSale,proBigSale1,gysRl,guessSale,proR) values (@bigSale_Id,@pro_Number,@pro_Name,@pro_Guige,@pro_OldPrice,@pro_OldSale,@pro_BigSale,@pro_BigSale1,@gys_Rl,@guess_Sale,@pro_R)"; Cmd1.Parameters.Add(new SqlParameter("@buyer_Name", SqlDbType.NVarChar, 50));
Cmd1.Parameters["@buyer_Name"].Value =saleBuyerName.Text.Trim(); Cmd1.Parameters.Add(new SqlParameter("@buyer_Num", SqlDbType.NVarChar, 50));
Cmd1.Parameters["@buyer_Num"].Value =saleBuyerNum.Text.Trim();     Cmd1.Parameters.Add(new SqlParameter("@buyer_Ywy", SqlDbType.NVarChar, 50));
Cmd1.Parameters["@buyer_Ywy"].Value =saleBuyerYwy.Text.Trim(); Cmd1.Parameters.Add(new SqlParameter("@begin_Date", SqlDbType.NVarChar, 50));
Cmd1.Parameters["@begin_Date"].Value =saleBeginDate.Text.Trim(); Cmd1.Parameters.Add(new SqlParameter("@end_Date", SqlDbType.NVarChar, 50));
Cmd1.Parameters["@end_Date"].Value =saleEndDate.Text.Trim(); Cmd1.Parameters.Add(new SqlParameter("@begin_Date1", SqlDbType.NVarChar, 50));
Cmd1.Parameters["@begin_Date1"].Value =saleBeginDate1.Text.Trim(); Cmd1.Parameters.Add(new SqlParameter("@end_Date1", SqlDbType.NVarChar, 50));
Cmd1.Parameters["@end_Date1"].Value =saleEndDate1.Text.Trim(); Cmd1.Parameters.Add(new SqlParameter("@sale_Kind", SqlDbType.NVarChar, 50));
Cmd1.Parameters["@sale_Kind"].Value =saleKind.SelectedItem.Text.Trim(); Cmd1.Parameters.Add(new SqlParameter("@sale_Fee", SqlDbType.NVarChar, 50));
Cmd1.Parameters["@sale_Fee"].Value =saleFee.Text.Trim(); Cmd1.Parameters.Add(new SqlParameter("@sale_Shop", SqlDbType.NVarChar, 50));
Cmd1.Parameters["@sale_Shop"].Value =saleBuyerShu.Text.Trim(); Cmd1.Parameters.Add(new SqlParameter("@sale_Re", SqlDbType.NVarChar, 300));
Cmd1.Parameters["@sale_Re"].Value =saleRe.Text.Trim(); Cmd1.Parameters.Add(new SqlParameter("@bigSale_Id", SqlDbType.NVarChar, 50));
Cmd1.Parameters["@bigSale_Id"].Value =s;
Cmd.Parameters.Add(new SqlParameter("@bigSale_Id", SqlDbType.NVarChar, 50));
Cmd.Parameters["@bigSale_Id"].Value =s; Cmd.Parameters.Add(new SqlParameter("@pro_Number", SqlDbType.NVarChar, 50));
Cmd.Parameters["@pro_Number"].Value =tempGrid.Items[i].Cells[1].Text.Trim();     Cmd.Parameters.Add(new SqlParameter("@pro_Name", SqlDbType.NVarChar, 50));
Cmd.Parameters["@pro_Name"].Value =tempGrid.Items[i].Cells[2].Text.Trim(); Cmd.Parameters.Add(new SqlParameter("@pro_Guige", SqlDbType.NVarChar, 50));
Cmd.Parameters["@pro_Guige"].Value =tempGrid.Items[i].Cells[3].Text.Trim(); Cmd.Parameters.Add(new SqlParameter("@pro_OldPrice", SqlDbType.NVarChar, 50));
Cmd.Parameters["@pro_OldPrice"].Value =tempGrid.Items[i].Cells[4].Text.Trim(); Cmd.Parameters.Add(new SqlParameter("@pro_OldSale", SqlDbType.NVarChar, 50));
Cmd.Parameters["@pro_OldSale"].Value =tempGrid.Items[i].Cells[5].Text.Trim();     Cmd.Parameters.Add(new SqlParameter("@pro_BigSale", SqlDbType.NVarChar, 50));
Cmd.Parameters["@pro_BigSale"].Value =tempGrid.Items[i].Cells[6].Text.Trim(); Cmd.Parameters.Add(new SqlParameter("@pro_BigSale1", SqlDbType.NVarChar, 50));
Cmd.Parameters["@pro_BigSale1"].Value =tempGrid.Items[i].Cells[7].Text.Trim(); Cmd.Parameters.Add(new SqlParameter("@gys_Rl", SqlDbType.NVarChar, 50));
Cmd.Parameters["@gys_Rl"].Value =tempGrid.Items[i].Cells[8].Text.Trim(); Cmd.Parameters.Add(new SqlParameter("@guess_Sale", SqlDbType.NVarChar, 50));
Cmd.Parameters["@guess_Sale"].Value =tempGrid.Items[i].Cells[9].Text.Trim(); Cmd.Parameters.Add(new SqlParameter("@pro_R", SqlDbType.NVarChar, 300));
Cmd.Parameters["@pro_R"].Value =tempGrid.Items[i].Cells[10].Text.Trim(); try 
{ Cmd1.CommandText = "Sql1";
Cmd1.ExecuteNonQuery(); Cmd.CommandText = "Sql";
Cmd.ExecuteNonQuery(); myTrans.Commit();
Response.Write("<script language=javascript>alert('保存成功！')</script>");
Response.Write("<script language=javascript>window.location.href='../bigsale/saleApply.aspx'</script>");
}
catch (Exception Ex)
{
myTrans.Rollback();
Response.Write(Ex.ToString());
Response.Write("<script language=javascript>alert('保存失败！')</script>");
Response.Write("<script language=javascript>window.location.href='../bigsale/saleApply.aspx'</script>");
}
finally
{
  Conn1.Close();
}
}

Answer 1

参考：
关于ADO运行事务提示“无效操作，连接被关闭”的异常分类总结
http://hi.baidu.com/zwfec/blog/item/84fc331208e79154f919b8e9.html

Answer 2

  ''''''''''''''''''''''''''''''定义事务
        Dim mycontra As SqlTransaction
       Dim dscwtra As SqlTransaction
    
       Dim insertnum As Integer = 0 '插入到表中的行数
        Try
            '打开连接
             MyCommand = New SqlCommand(InsertCmd, MyConnection)
            MyCommand.Connection.Open()
            dscwcmd.Connection.Open()
            
            mycontra = MyCommand.Connection.BeginTransaction
            dscwtra = dscwcmd.Connection.BeginTransaction
         
              MyCommand.Transaction = mycontra
            dscwcmd.Transaction = dscwtra
                        insertnum += MyCommand.ExecuteNonQuery()
            insertnum += dscwcmd.ExecuteNonQuery
           
        Catch Exp As SqlException            '发错误则回滚
            mycontra.Rollback()
          dscwtra.Rollback()
            If Exp.Number = 2627 Then
                Message.InnerHtml = "错误：已存在具有相同的编号或客户记录"
            Else
                Message.InnerHtml = "错误：未能增加 编号：" + khbm + "，名称：" + khmc.Value + "  客户记录！"
            End If
            Message.Style("color") = "red"
            MyCommand.Connection.Close()
            dscwcmd.Connection.Close()
            Exit Sub
        End Try
        If insertnum = 2 Then
          '向数据库提交事务,确认插入
            mycontra.Commit()
          dscwtra.Commit()
        Else
            mycontra.Rollback()
            dscwtra.Rollback()
         End If
        MyCommand.Connection.Close()
        dscwcmd.Connection.Close()

Answer 3

 SqlTransaction myTrans = Conn1.BeginTransaction();
             
                SqlCommand Cmd1 = new SqlCommand();
                Cmd1.Connection=Conn1;
                Cmd1.Transaction = myTrans;写在for循环外面
然后循环里面每次给cmd指定不同的CommandText 就好了，然后参数的话，只能加一次
第二次加同一个参数会出错
在循环里面，会出现加多次的情况，会出异常事务这样写
    string connectionString = ConfigurationManager.ConnectionStrings["SQLConnString1"].ConnectionString;
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlTransaction tran = conn.BeginTransaction();            try
            {
                SqlCommand cmd = new SqlCommand("", conn, tran);
                for(int i = 0;i<=10;i++)
                {
                     cmd.CommandText = "";
                     cmd.execute....
                }                tran.Commit();
            }
            catch (Exception)
            {
                tran.Rollback();
                throw;
            }
            finally
            {
                conn.Close();
            }

Answer 4

你的代码 循环中 ，第一次成功，开启一个事务
然后执行try里面代码，然后提交事务，conn.close
第二次循环的时候肯定报conn被关闭的错误啊
  protected void saveBtn_click(object sender,EventArgs e)
        {
            SqlConnection Conn1=new SqlConnection(Conn);
            Conn1.Open();             for( int i = 0; i < tempGrid.Items.Count; i++ )
            {
                // 启动一个事务                SqlTransaction myTrans = Conn1.BeginTransaction();
             
                SqlCommand Cmd1 = new SqlCommand();
                Cmd1.Connection=Conn1;
                Cmd1.Transaction = myTrans;                SqlCommand Cmd = new SqlCommand();
                Cmd.Connection=Conn1;
                Cmd.Transaction = myTrans;
                Sql1="";
                Sql= "";                                try 
                {
                    Cmd1.CommandText = "Sql1";
                    Cmd1.ExecuteNonQuery();                    Cmd.CommandText = "Sql";
                    Cmd.ExecuteNonQuery();                    myTrans.Commit();
                    Response.Write("<script language=javascript>alert('保存成功！')</script>");
                    Response.Write("<script language=javascript>window.location.href='../bigsale/saleApply.aspx'</script>");
                }
                catch (Exception Ex)
                {
                    myTrans.Rollback();
                    Response.Write(Ex.ToString());
                    Response.Write("<script language=javascript>alert('保存失败！')</script>");
                    Response.Write("<script language=javascript>window.location.href='../bigsale/saleApply.aspx'</script>");
                }
                finally
                {
                  Conn1.Close();
                }
            }        

Answer 5

                Sql1="insert into bigSaleList(buyerName,buyerNum,buyerYwy,beginDate,endDate,beginDate1,endDate1,saleKind,saleFee,saleShop,saleRe,bigSaleId) values (@buyer_Name,@buyer_Num,@buyer_Ywy,@begin_Date,@end_Date,@begin_Date1,@end_Date1,@sale_Kind,@sale_Fee,@sale_Shop,@sale_Re,@bigSale_Id)";
                Sql= "insert into bigSaleList(bigSaleId,proNumber,proName,proGuige,proOldPrice,proOldSale,proBigSale,proBigSale1,gysRl,guessSale,proR) values (@bigSale_Id,@pro_Number,@pro_Name,@pro_Guige,@pro_OldPrice,@pro_OldSale,@pro_BigSale,@pro_BigSale1,@gys_Rl,@guess_Sale,@pro_R)";>>
楼主你 仔细看看我红色标注的部份!

Answer 6

编辑了大半天
这样试试
把for 放在try里面
然后添加参数只加一次，赋值循环进行
应该可以了
 protected void saveBtn_click(object sender,EventArgs e)
        {
            SqlConnection Conn1=new SqlConnection(Conn);
            Conn1.Open(); 

// 启动一个事务
SqlTransaction myTrans = Conn1.BeginTransaction();

try 
            {
             
            SqlCommand Cmd1 = new SqlCommand();
            Cmd1.Connection=Conn1;
            Cmd1.Transaction = myTrans;            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection=Conn1;
            Cmd.Transaction = myTrans;  Sql1="insert into bigSaleList(buyerName,buyerNum,buyerYwy,beginDate,endDate,beginDate1,endDate1,saleKind,saleFee,saleShop,saleRe,bigSaleId) values (@buyer_Name,@buyer_Num,@buyer_Ywy,@begin_Date,@end_Date,@begin_Date1,@end_Date1,@sale_Kind,@sale_Fee,@sale_Shop,@sale_Re,@bigSale_Id)";
            Sql= "insert into bigSaleList(bigSaleId,proNumber,proName,proGuige,proOldPrice,proOldSale,proBigSale,proBigSale1,gysRl,guessSale,proR) values (@bigSale_Id,@pro_Number,@pro_Name,@pro_Guige,@pro_OldPrice,@pro_OldSale,@pro_BigSale,@pro_BigSale1,@gys_Rl,@guess_Sale,@pro_R)"; Cmd1.Parameters.Add(new SqlParameter("@buyer_Name", SqlDbType.NVarChar, 50));
Cmd1.Parameters.Add(new SqlParameter("@buyer_Num", SqlDbType.NVarChar, 50));
Cmd1.Parameters.Add(new SqlParameter("@buyer_Ywy", SqlDbType.NVarChar, 50));
Cmd1.Parameters.Add(new SqlParameter("@begin_Date", SqlDbType.NVarChar, 50));
Cmd1.Parameters.Add(new SqlParameter("@end_Date", SqlDbType.NVarChar, 50));
Cmd1.Parameters.Add(new SqlParameter("@begin_Date1", SqlDbType.NVarChar, 50));
Cmd1.Parameters.Add(new SqlParameter("@end_Date1", SqlDbType.NVarChar, 50));
Cmd1.Parameters.Add(new SqlParameter("@sale_Kind", SqlDbType.NVarChar, 50));
Cmd1.Parameters.Add(new SqlParameter("@sale_Fee", SqlDbType.NVarChar, 50));
Cmd1.Parameters.Add(new SqlParameter("@sale_Shop", SqlDbType.NVarChar, 50));
Cmd1.Parameters.Add(new SqlParameter("@sale_Re", SqlDbType.NVarChar, 300));
Cmd1.Parameters.Add(new SqlParameter("@bigSale_Id", SqlDbType.NVarChar, 50));
Cmd.Parameters.Add(new SqlParameter("@bigSale_Id", SqlDbType.NVarChar, 50));
Cmd.Parameters.Add(new SqlParameter("@pro_Number", SqlDbType.NVarChar, 50));
Cmd.Parameters.Add(new SqlParameter("@pro_Name", SqlDbType.NVarChar, 50));
Cmd.Parameters.Add(new SqlParameter("@pro_Guige", SqlDbType.NVarChar, 50));
Cmd.Parameters.Add(new SqlParameter("@pro_OldPrice", SqlDbType.NVarChar, 50));
Cmd.Parameters.Add(new SqlParameter("@pro_OldSale", SqlDbType.NVarChar, 50));
Cmd.Parameters.Add(new SqlParameter("@pro_BigSale", SqlDbType.NVarChar, 50));
Cmd.Parameters.Add(new SqlParameter("@pro_BigSale1", SqlDbType.NVarChar, 50));
Cmd.Parameters.Add(new SqlParameter("@gys_Rl", SqlDbType.NVarChar, 50));
Cmd.Parameters.Add(new SqlParameter("@guess_Sale", SqlDbType.NVarChar, 50));
Cmd.Parameters.Add(new SqlParameter("@pro_R", SqlDbType.NVarChar, 300));

            for( int i = 0; i < tempGrid.Items.Count; i++ )
            {                
                Cmd1.Parameters["@buyer_Name"].Value =saleBuyerName.Text.Trim();            
                Cmd1.Parameters["@buyer_Num"].Value =saleBuyerNum.Text.Trim();                
                Cmd1.Parameters["@buyer_Ywy"].Value =saleBuyerYwy.Text.Trim();                
                Cmd1.Parameters["@begin_Date"].Value =saleBeginDate.Text.Trim();                
                Cmd1.Parameters["@end_Date"].Value =saleEndDate.Text.Trim();                
                Cmd1.Parameters["@begin_Date1"].Value =saleBeginDate1.Text.Trim();                
                Cmd1.Parameters["@end_Date1"].Value =saleEndDate1.Text.Trim();                
                Cmd1.Parameters["@sale_Kind"].Value =saleKind.SelectedItem.Text.Trim();                
                Cmd1.Parameters["@sale_Fee"].Value =saleFee.Text.Trim();                
                Cmd1.Parameters["@sale_Shop"].Value =saleBuyerShu.Text.Trim();                
                Cmd1.Parameters["@sale_Re"].Value =saleRe.Text.Trim();                
                Cmd1.Parameters["@bigSale_Id"].Value =s;  
                Cmd.Parameters["@bigSale_Id"].Value =s;                
                Cmd.Parameters["@pro_Number"].Value =tempGrid.Items[i].Cells[1].Text.Trim();                
                Cmd.Parameters["@pro_Name"].Value =tempGrid.Items[i].Cells[2].Text.Trim();                
                Cmd.Parameters["@pro_Guige"].Value =tempGrid.Items[i].Cells[3].Text.Trim();                
                Cmd.Parameters["@pro_OldPrice"].Value =tempGrid.Items[i].Cells[4].Text.Trim();                
                Cmd.Parameters["@pro_OldSale"].Value =tempGrid.Items[i].Cells[5].Text.Trim();                
                Cmd.Parameters["@pro_BigSale"].Value =tempGrid.Items[i].Cells[6].Text.Trim();                
                Cmd.Parameters["@pro_BigSale1"].Value =tempGrid.Items[i].Cells[7].Text.Trim();                
                Cmd.Parameters["@gys_Rl"].Value =tempGrid.Items[i].Cells[8].Text.Trim();                
                Cmd.Parameters["@guess_Sale"].Value =tempGrid.Items[i].Cells[9].Text.Trim();                
                Cmd.Parameters["@pro_R"].Value =tempGrid.Items[i].Cells[10].Text.Trim();                               Cmd1.CommandText = "Sql1";
                Cmd1.ExecuteNonQuery();                Cmd.CommandText = "Sql";
                Cmd.ExecuteNonQuery();

}                    myTrans.Commit();
                    Response.Write("<script language=javascript>alert('保存成功！')</script>");
                    Response.Write("<script language=javascript>window.location.href='../bigsale/saleApply.aspx'</script>");
            }
            catch (Exception Ex)
            {
                    myTrans.Rollback();
                    Response.Write(Ex.ToString());
                    Response.Write("<script language=javascript>alert('保存失败！')</script>");
                    Response.Write("<script language=javascript>window.location.href='../bigsale/saleApply.aspx'</script>");
             }
             finally
                {
                  Conn1.Close();
                }
}

Answer 7

在事务提交前保持Connection的Open状态

Answer 8

 你把你        Cmd1.ExecuteNonQuery();  
 函数里的代码贴出来看看!  看看里面是不是被关闭了.

Answer 9

你的标题是向两个表插入数据，但是sql语句中却是向一个表（bigSaleList）插入数据。

Answer 10

你的数据连接在FOR外部打开
而在FOR内部却关闭了
第2次进入循环时肯定失败

Answer 11

LZ写错了，
finally
{
    Conn1.Close();
}
for循环的第一次执行完，你就把连接关闭了，第二次循环肯定就不能用了，所以报错连接关闭。

Answer 12

把这两句
 SqlConnection Conn1=new SqlConnection(Conn);
            Conn1.Open(); 
移到for内部试下

Answer 13

你这个是每次循环都关关闭了conn.close();在for的第二次是关闭的，自然要出异常，rollback()了，