TRANSACTION In Using Statement

Hi!



I have a problem when I use Handling transaction. I basically combine
the handling Transaction with Handling Exception. However, I use the
Handling Transaction under "Using" Statement. This is the code that I
use:



Try

con = New SqlConnection(....)

con.open()

trans - con.beginTransaction



For Each row in Books.rows

Dim cell As TableCell = row.Cells(0)

Dim checkBox As HtmlInputCheckBox = cell.Controls(1)

Dim memberID As String = String.Empty

Dim sql as String = "SELECT * From Item WHERE ItemID =" + checkBox.Value

If checkBox IsNot Nothing And checkBox.Checked Then

Using cmd As New SqlCommand(sql, con, trans)

    memberID = cmd.ExecuteScalar()

    sqldatasource1.DeleteParameters("ItemID").DefaultValue = checkBox.Value

    sqldatasource1.Delete()

    sqldatasource2.DeleteParameters("memberID").DefaultValue = memberID


    sqldatasource2.Delete()

End Using

End IF

Next

Trans.Commit()Catch ex As Exception

If trans IsNot Nothing Then

    trans.Rollback()

End If

Return

End Try



I have a problem that when the error occurs, the transaction won't
rollback. I know this because if the error occurs in
sqldatasource2.deleteparameters, the sqldatasource1.deleteparameters
didn't rollback. How do I know it doesn't rollback? I open my database
table, and it shows that the record has been deleted by
sqldatasource1.deleteparameters.



I am using VWD and VB.net

many many thanks in advanced for your help !!