在vb.net中保存并编辑事务后出错

Question

在使用我的代码成功地保存了一个事务之后.我编辑了事务，然后save...then，我得到了以下错误消息：

当分配给命令的连接位于挂起的本地事务中时，ExecuteNonQuery要求命令具有事务。命令的事务属性尚未初始化。

这是我的代码：

Try
  sqlTrans = sqlCon_.BeginTransaction(IsolationLevel.RepeatableRead)
  sSQLAdapter_.UpdateBatchSize = 30
  sCommand_ = DirectCast(sSQLAdapter_.SelectCommand, SqlCommand)
  sCommand_.Connection = sqlCon_ 
  sCommand_.Transaction = sqlTrans 
  sSQLAdapter.SelectCommand.Transaction = sqlTrans

  sSQLAdapter_.Update(sDataSet.Tables(0))

  sqlTrans.Commit()
  sqlCon_.Close()

Catch ex As Exception
  sqlTrans.Rollback()
Finally
   sSQLAdapter.SelectCommand.Connection.Close()
   sSQLAdapter.SelectCommand.Connection = Nothing
   sSQLAdapter.SelectCommand.Transaction = Nothing
   sSQLAdapter.SelectCommand.CommandText = ""
   sSQLAdapter.SelectCommand.Dispose()

   sqlTrans.Dispose()
   sqlCon_.Close()
   sqlCon_.Dispose()
End Try

Answer 1

您需要为InsertCommand、UpdateCommand和SqlDataAdapter的DeleteCommand对象设置事务对象引用，因为您正在调用Update方法。

参考文件- 与DataAdapter一起使用事务

样本：

Using Cn As New SqlConnection(CnStr)
 Cn.Open()
 Using Trans As SqlTransaction = Cn.BeginTransaction
   Dim Adp As New SqlDataAdapter(selectQuery, Cn)
   Adp.SelectCommand.Transaction = Trans

   Dim cmb As New SqlCommandBuilder(Adp)
   Adp.DeleteCommand = cmb.GetDeleteCommand()
   Adp.InsertCommand = cmb.GetInsertCommand()
   Adp.UpdateCommand = cmb.GetUpdateCommand()

   Adp.InsertCommand.Transaction = Trans
   Adp.DeleteCommand.Transaction = Trans
   Adp.UpdateCommand.Transaction = Trans

   Dim dt As New DataTable
   Adp.Fill(dt)
   /* ------------- 

    Perform insert/delete/update on DataSet or DataTable 
    -------------- */ 
   Adp.Update(dt)
   Trans.Commit()
  End Using
 Cn.Close()
End Using