請問ASP.Net 2.0 的Transaction問題

[lily]

我參考MSDN "SqlDataSource.Update 方法" 裡面的範例程式碼, 做Transaction.
按下存檔按鈕後, 執行 Me.sqldsABC.Insert() (sqldsABC是SqlDataSource物件.)
另外寫了 Sub sqldsABC_Inserting 和 Sub sqldsABC_Inserted 兩個事件處理.
以及一個 Function UpdateTBAAA (更新另外一個Table, 且要在同一個transaction內).

我所遇到的問題是:
在逐步trace時發現, 執行 Function UpdateTBAAA 時,
"Catch ex As Exception" 這一行的 ex 游標指過去時有顯示錯誤訊息,
但卻沒有跑進這兩行: "errmsg = ex.Message" 和 "UpdateTBAAA = False".
所以 Function UpdateTBAAA return TRUE.
在 Sub sqldsABC_Inserted 裡面就執行 "transaction.commit()".
但是在回到 Sub btnSave_Click 時, 就跑進 Catch 裡面去.
原因是 Me.sqldsABC.Insert() 本身的發生錯誤. (原因為not null欄位沒有給值).

結果就是, TBAAA 的 update 有成功, 但是 sqldsABC的 insert 沒有成功.
這樣的 transaction 沒有作用.

想請各位高手幫我看一下是哪裡寫錯了.
感謝~~

==========以下為程式碼==========

Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnSave.Click
Try
Me.sqldsABC.Insert()
Catch ex As Exception
Me.Literal1.Text = ex.Message
End Try
End Sub

Protected Sub sqldsABC_Inserting(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles
sqldsABC.Inserting
Dim command As DbCommand
Dim connection As DbConnection
Dim transaction As DbTransaction
Try
command = e.Command
connection = command.Connection
connection.Open()
transaction = connection.BeginTransaction()
command.Transaction = transaction
Catch ex As Exception
Me.Literal1.Text = ex.Message
End Try
End Sub

Protected Sub sqldsABC_Inserted(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles
sqldsABC.Inserted
Dim command As DbCommand
Dim transaction As DbTransaction
Dim connection As DbConnection
Dim OtherProcessSucceeded As Boolean
Dim errmsg As String = ""
Try
command = e.Command
connection = command.Connection
transaction = command.Transaction

If UpdateTBAAA(connection, transaction, errmsg) Then 'Update
TB[TBAAA]的欄位 FD[Status]
transaction.Commit()
Else
transaction.Rollback()
Me.Literal1.Text = "Rollback. " & errmsg
End If
Catch ex As Exception
Me.Literal1.Text = ex.Message
End Try
End Sub

Function UpdateTBAAA(ByRef cn As DbConnection, ByRef trn As
DbTransaction, ByRef errmsg As String) As Boolean
Dim cmd As New SqlCommand
Try
cmd.Connection = cn
cmd.Transaction = trn
cmd.CommandType = Data.CommandType.Text
cmd.CommandText = "Update TBAAA Set Status=1 Where No='" &
Me.hidNo.Value & "'"
cmd.ExecuteNonQuery()
errmsg = ""
UpdateTBAAA = True
Catch ex As Exception
errmsg = ex.Message
UpdateTBAAA = False
End Try
End Function

[lily]

找到原因了, Inserted事件要稍微改寫成這樣 :

Protected Sub sqldsABC_Inserted(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles
sqldsABC.Inserted
Dim command As DbCommand
Dim transaction As DbTransaction
Dim connection As DbConnection
Dim OtherProcessSucceeded As Boolean
Dim errmsg As String = ""
Try
command = e.Command
connection = command.Connection
transaction = command.Transaction

If UpdateTBAAA(connection, transaction, errmsg) Then 'Update
TB[TBAAA]的欄位 FD[Status]
If e.AffectedRows > 0 Then
transaction.Commit()
Else
transaction.Rollback()
Me.Literal1.Text = "Rollback. " & e.Exception.Message
End If
Else
transaction.Rollback()
Me.Literal1.Text = "Rollback. " & errmsg
End If
Catch ex As Exception
Me.Literal1.Text = ex.Message
End Try
End Sub

[unknown]

ADO.NET 2.0 多了 TransactionScope + Using
超好用......可以讓transaction變得更好寫....
自己去研究看看唄

"lily" 來函：

Post by lily
Protected Sub sqldsABC_Inserted(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles
sqldsABC.Inserted
Dim command As DbCommand
Dim transaction As DbTransaction
Dim connection As DbConnection
Dim OtherProcessSucceeded As Boolean
Dim errmsg As String = ""
Try
command = e.Command
connection = command.Connection
transaction = command.Transaction
If UpdateTBAAA(connection, transaction, errmsg) Then 'Update
TB[TBAAA]的欄位 FD[Status]
If e.AffectedRows > 0 Then
transaction.Commit()
Else
transaction.Rollback()
Me.Literal1.Text = "Rollback. " & e.Exception.Message
End If
Else
transaction.Rollback()
Me.Literal1.Text = "Rollback. " & errmsg
End If
Catch ex As Exception
Me.Literal1.Text = ex.Message
End Try
End Sub