problem with transactions in VB.NET using npgsql

Maybe someone here can figure it out. Everything updates fine with
this code, except where there's an exception, it's not rolling back
by the transaction. What I'm trying to do:

Begin a transaction
Do the update, insert, delete checks on each of the data tables,
using a different npgsqlcommandbuilder for each of the tables.
Commit
if any failure happens, roll back all the changes to the transaction beginning.

I assign the transaction object to each of the commands, but it seems
that some tables will get updated, even when I call rollback. Is
something I'm calling secretly calling "commit" somewhere?

My code follows. Thanks for checking it over. Sorry about the
length, but I wanted you to see that I'm updating multiple tables
with multiple dataadapters.

-Owen

Option Explicit On
Imports System.Windows.Forms
Imports npgsql
Imports System.Xml.Serialization
Imports System.IO
Imports System.Collections.Generic
Imports System.Configuration
' Note: some controls, in the forms designer, cover other controls,
i.e. CommUsageCB covers styleCB
Public Class ParcelDisplayFrm

Public Sub WriteAllData()
Dim trans As NpgsqlTransaction = Nothing
Dim cmd As NpgsqlCommandBuilder
Dim i As Integer
Dim success As Boolean

Try
If Not statusReadOnly Then
i = vbCancel
success = priceIt(Me, full_DataSet, True, True, pc)
dt = full_DataSet.Tables(currentSchema & ".parcel")

dt.Rows(0).EndEdit()
dt = full_DataSet.Tables(currentSchema & ".accounts")
dt.Rows(0).EndEdit()
dt = full_DataSet.Tables(currentSchema & ".bldg")
For i = 0 To dt.Rows.Count - 1
dt.Rows(i).EndEdit()
Next i
dt = full_DataSet.Tables(currentSchema & ".commcost")
For i = 0 To dt.Rows.Count - 1
dt.Rows(i).EndEdit()
Next i
dt = full_DataSet.Tables(currentSchema & ".outbuildings")
For i = 0 To dt.Rows.Count - 1
If dt.Rows(i).RowState = DataRowState.Added Then
dt.Rows(i).Item("maplot") = Form1.currentMapLot
End If
Debug.Print(dt.Rows.Count)
dt.Rows(i).EndEdit()
Next i
If Not dirtySketch And Not full_DataSet.HasChanges Then
Exit Sub ' Nothing to change
End If

Dim dg As New SaveChangesDlog
If dg.ShowDialog = Windows.Forms.DialogResult.Cancel
Then Exit Sub ' don't save
writeFinalize()
dt = full_DataSet.Tables(currentSchema & ".parcel")
m_SqlConnection.Open()
' create a transaction for the rest of all the changes

trans = m_SqlConnection.BeginTransaction

cmd = New NpgsqlCommandBuilder(parcel_DataAdapter)

Dim parcelchanges As DataTable =
dt.GetChanges(DataRowState.Modified)

If parcelchanges IsNot Nothing Then
parcel_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(0))
parcel_DataAdapter.UpdateCommand.Transaction = trans

parcel_DataAdapter.Update(parcelchanges)
End If
parcelchanges = dt.GetChanges(DataRowState.Deleted)
If parcelchanges IsNot Nothing Then
parcel_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
parcel_DataAdapter.DeleteCommand.Transaction = trans

parcel_DataAdapter.Update(parcelchanges)
End If
parcelchanges = dt.GetChanges(DataRowState.Added)
If parcelchanges IsNot Nothing Then
parcel_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(0))

parcel_DataAdapter.InsertCommand.Transaction = trans

parcel_DataAdapter.Update(parcelchanges)
End If

' accounts table
cmd = New NpgsqlCommandBuilder(accts_DataAdapter)
dt = full_DataSet.Tables(currentSchema & ".accounts")
Dim acctchanges As DataTable =
dt.GetChanges(DataRowState.Modified)

If acctchanges IsNot Nothing Then
accts_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(0))
accts_DataAdapter.UpdateCommand.Transaction = trans

accts_DataAdapter.Update(acctchanges)
End If
acctchanges = dt.GetChanges(DataRowState.Deleted)
If acctchanges IsNot Nothing Then
accts_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
accts_DataAdapter.DeleteCommand.Transaction = trans

accts_DataAdapter.Update(acctchanges)
End If
acctchanges = dt.GetChanges(DataRowState.Added)
If acctchanges IsNot Nothing Then
accts_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(0))

accts_DataAdapter.InsertCommand.Transaction = trans

accts_DataAdapter.Update(acctchanges)
End If

' do for every building
dt = full_DataSet.Tables(currentSchema & ".bldg")
If dt.Rows.Count > 0 Then
If dirtySketch Then
For i = currentBuilding To howManyBuildings - 1

returnSketchToDatabase(dt.Rows(0).Item("maplot"), i, trans, Me)
Next i
End If
cmd = New NpgsqlCommandBuilder(bldg_DataAdapter)

' add modified dates
addModDates(dt, "modified")

'Debug.Print(ZoningCode.DataBindings.BindableComponent)
Dim bldgchanges As DataTable =
dt.GetChanges(DataRowState.Deleted)
If bldgchanges IsNot Nothing Then
bldg_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
bldg_DataAdapter.DeleteCommand.Transaction = trans

bldg_DataAdapter.Update(bldgchanges)
End If
bldgchanges = dt.GetChanges(DataRowState.Modified)
If bldgchanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
bldg_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(j))
bldg_DataAdapter.UpdateCommand.Transaction = trans

bldg_DataAdapter.Update(bldgchanges)
End If
bldgchanges = dt.GetChanges(DataRowState.Added)
If bldgchanges IsNot Nothing Then
bldg_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(0))
bldg_DataAdapter.InsertCommand.Transaction = trans

bldg_DataAdapter.Update(bldgchanges)
End If
End If

dt = full_DataSet.Tables(currentSchema & ".commcost")
If dt.Rows.Count > 0 Then
cmd = New NpgsqlCommandBuilder(commbldg_DataAdapter)

'Debug.Print(ZoningCode.DataBindings.BindableComponent)
Dim commBldgChanges As DataTable
commBldgChanges = dt.GetChanges(DataRowState.Deleted)
If commBldgChanges IsNot Nothing Then
commbldg_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
commbldg_DataAdapter.DeleteCommand.Transaction = trans

commbldg_DataAdapter.Update(commBldgChanges)
End If
commBldgChanges = dt.GetChanges(DataRowState.Modified)
If commBldgChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
commbldg_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(j))
commbldg_DataAdapter.UpdateCommand.Transaction = trans

commbldg_DataAdapter.Update(commBldgChanges)
End If
commBldgChanges = dt.GetChanges(DataRowState.Added)
If commBldgChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
commbldg_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(j))
commbldg_DataAdapter.InsertCommand.Transaction = trans

commbldg_DataAdapter.Update(commBldgChanges)
End If
End If

dt = full_DataSet.Tables(currentSchema & ".outbuildings")
If dt.Rows.Count > 0 Then
cmd = New NpgsqlCommandBuilder(outbldg_DataAdapter)

For i = 0 To dt.Rows.Count - 1
If dt.Rows(i).RowState = DataRowState.Added Then
dt.Rows(i).Item("MapLot") =
full_DataSet.Tables(currentSchema & ".parcel").Rows(0).Item("MapLot")
End If
Next i
'Debug.Print(ZoningCode.DataBindings.BindableComponent)
Dim outchanges As DataTable
outchanges = dt.GetChanges(DataRowState.Deleted)
If outchanges IsNot Nothing Then
outbldg_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
outbldg_DataAdapter.DeleteCommand.Transaction = trans

outbldg_DataAdapter.Update(outchanges)
End If
outchanges = dt.GetChanges(DataRowState.Modified)
If outchanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
outbldg_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(j))
outbldg_DataAdapter.UpdateCommand.Transaction = trans

outbldg_DataAdapter.Update(outchanges)

End If
outchanges = dt.GetChanges(DataRowState.Added)
If outchanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
outbldg_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(j))
outbldg_DataAdapter.InsertCommand.Transaction = trans

outbldg_DataAdapter.Update(outchanges)
End If
End If
' write changes to sales tables
dt = full_DataSet.Tables(currentSchema & ".sales")
If dt.Rows.Count > 0 Then
cmd = New NpgsqlCommandBuilder(sales_DataAdapter)

'Debug.Print(ZoningCode.DataBindings.BindableComponent)
Dim salesChanges As DataTable
salesChanges = dt.GetChanges(DataRowState.Deleted)
If salesChanges IsNot Nothing Then
sales_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
sales_DataAdapter.DeleteCommand.Transaction = trans

sales_DataAdapter.Update(salesChanges)
End If
salesChanges = dt.GetChanges(DataRowState.Modified)
If salesChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
sales_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(j))
sales_DataAdapter.UpdateCommand.Transaction = trans

sales_DataAdapter.Update(salesChanges)
End If
salesChanges = dt.GetChanges(DataRowState.Added)
If salesChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
sales_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(j))
sales_DataAdapter.InsertCommand.Transaction = trans

sales_DataAdapter.Update(salesChanges)
End If
End If
' write changes to sales overflow table
dt = full_DataSet.Tables(currentSchema & ".salesovflowtype")
If dt.Rows.Count > 0 Then
cmd = New NpgsqlCommandBuilder(salesOF_DataAdapter)

'Debug.Print(ZoningCode.DataBindings.BindableComponent)
Dim salesOFChanges As DataTable
salesOFChanges = dt.GetChanges(DataRowState.Deleted)
If salesOFChanges IsNot Nothing Then
salesOF_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
salesOF_DataAdapter.DeleteCommand.Transaction = trans

salesOF_DataAdapter.Update(salesOFChanges)
End If
salesOFChanges = dt.GetChanges(DataRowState.Modified)
If salesOFChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
salesOF_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(j))
salesOF_DataAdapter.UpdateCommand.Transaction = trans

salesOF_DataAdapter.Update(salesOFChanges)
End If
salesOFChanges = dt.GetChanges(DataRowState.Added)
If salesOFChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
salesOF_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(j))
salesOF_DataAdapter.InsertCommand.Transaction = trans

salesOF_DataAdapter.Update(salesOFChanges)
End If
End If

trans.Commit()
m_SqlConnection.Close()
dirtySketch = False
BrowserPanel.Refresh()
End If
Catch ex As Exception
MsgBox(" error on writing data " & ex.Message,
MsgBoxStyle.AbortRetryIgnore)
If trans IsNot Nothing Then trans.Rollback()
If m_SqlConnection.State = ConnectionState.Open Then
m_SqlConnection.Close()
End Try
End Sub

End Class