datagridview中事务的问题，“事务不是与当前连接无关联，就是已完成”

Question

小弟在form里就放了一个datagridview和一个button
想利用datagridview实现对表的直接修改，但是现在只能添加行不能修改，修改就提示“事务不是与当前连接无关联，就是已完成”
public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        
        SqlDataAdapter adapter;
        DataSet ds = new DataSet();
        private void Form1_Load(object sender, EventArgs e)
        {
            SqlConnection sqlconn = new SqlConnection("Server=.;DataBase=user;uid=sa;pwd=sa");
            try
            {
                //select语句中一定要包含主键字段，否则无法更新
                adapter = new SqlDataAdapter("select stuno'学号',stuname'姓名' from stuinf", sqlconn);//表里就两个字段，stuno和stuname
                adapter.Fill(ds, "student");
                dataGridView1.AutoGenerateColumns = true;
                dataGridView1.DataSource = ds.Tables["student"]; 
                sqlconn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "异常错误!", MessageBoxButtons.OK, MessageBoxIcon.Error);            }
        }        private void btnUpdate_Click(object sender, EventArgs e)
        {
           
            try
            {
                SqlConnection conn = new SqlConnection("Server=.;DataBase=user;uid=sa;pwd=sa");
                SqlCommandBuilder sBuilder = new SqlCommandBuilder(adapter);
                adapter.UpdateCommand = sBuilder.GetUpdateCommand();
                conn.Open();
                SqlTransaction Trans = conn.BeginTransaction();                          
                try
                {
                    adapter.UpdateCommand.Transaction = Trans;
                    adapter.Update(ds, "student"); 
                    Trans.Commit();
                }
                catch (Exception ex)
                {
                    Trans.Rollback();
                    MessageBox.Show(this, ex.ToString(), "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                ds.Clear();
                adapter.Fill(ds, "student");
                dataGridView1.AutoGenerateColumns = true;
                dataGridView1.DataSource = ds.Tables["student"];
                MessageBox.Show(this, "确定", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "异常错误!", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }请各位告诉指教

Answer 1

private void btnUpdate_Click(object sender, EventArgs e) 
        { 
          
            try 
            { 
                SqlConnection conn = new SqlConnection("Server=.;DataBase=user;uid=sa;pwd=sa"); 
                //......
                    adapter.UpdateCommand.Transaction = Trans; 
                    adapter.Update(ds, "student"); 
                    Trans.Commit(); 
                } 
                } 你的adapter执行Update方法时，关联的Connection不是你新建的这个conn。所以在新建的这个conn上开的事务与adapter所使用的Connection没关联

Answer 2

那请问应该怎么修改呢？本来把这句SqlConnection sqlconn = new SqlConnection("Server=.;DataBase=user;uid=sa;pwd=sa"); 写在外面和SqlDataAdapter 一起定义的，下面
 private void btnUpdate_Click(object sender, EventArgs e)
        {
           
            try
            {
                sqlconn.Open();
                SqlCommandBuilder sBuilder = new SqlCommandBuilder(adapter);
                adapter.UpdateCommand = sBuilder.GetUpdateCommand();
                SqlTransaction Trans = sqlconn.BeginTransaction();                          
                try
                {
                    adapter.UpdateCommand.Transaction = Trans;
                    adapter.Update(ds, "student"); 
                    Trans.Commit();
                }
                catch (Exception ex)
                {
                    Trans.Rollback();
                    MessageBox.Show(this, ex.ToString(), "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                ds.Clear();
                adapter.Fill(ds, "student");
                dataGridView1.AutoGenerateColumns = true;
                dataGridView1.DataSource = ds.Tables["student"];
                MessageBox.Show(this, "确定", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "SQL 异常错误!", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
写成这样是可以修改了，但是添加又不能用了，提示“如果分配给命令的连接位于本地挂起事务中，ExecuteNonQuery 要求命令拥有事务。命令的 Transaction 属性尚未初始化。”

Answer 3

adapter.UpdateCommand = sBuilder.GetUpdateCommand(); 

Answer 4

 sqlconn.Open(); 
                SqlCommandBuilder sBuilder = new SqlCommandBuilder(adapter); 
                adapter.UpdateCommand = sBuilder.GetUpdateCommand(); 
                SqlTransaction Trans = sqlconn.BeginTransaction();                          
                try 
                { 
                    adapter.UpdateCommand.Transaction = Trans; 
adaper.UpdateCommand.Connection=sqlconn;
                    adapter.Update(ds, "student"); 
                    Trans.Commit(); 
                } 试试看.... 

Answer 5

SqlCommand中的方法ValidateCommand提示该项错误
private void ValidateCommand(string method, bool async)   if ((this._transaction != null) && (this._transaction.Connection == null))
    {
        this._transaction = null;
    }
    if (this._activeConnection.HasLocalTransactionFromAPI && (this._transaction == null))
    {
        throw ADP.TransactionRequired(method);
    }
    if ((this._transaction != null) && (this._activeConnection != this._transaction.Connection))
    {
        throw ADP.TransactionConnectionMismatch();
    }