Help with Transactions and Data Adapters

[rwessels]

I wrote a little program to import XML data into a SQL Table. The XML tags are the names of the SQL Columns. It works fine except when I get an exception (i.e. foreign key contraint problem) halfway through the XML file, I end up with half my records processed. So Transactions should solve the problem, right? Well I am getting the error about The Transaction property of the command has not been initialized. It happens when the Adapter is being filled - even though the fill is only to get the column headers. Any help would be appreciated.

        Dim objSQLTransaction As SqlTransaction
        Dim objSQLConn As SqlConnection
        Try
            objSQLConn = New SqlConnection("SERVER=" & Me.txtServername.Text & ";UID=" & Me.txtUsername.Text & ";PWD=" & Me.txtPassword.Text & ";DATABASE=" & Me.txtAccount.Text & ";")
            Dim strTableName As String = Me.txtSQLTable.Text
            Dim objAdapter As SqlDataAdapter
            Dim objDataRow, objDBRow As DataRow
            Dim objDataColumn As DataColumn
            Dim objDSXML As New DataSet()
            Dim objDSDBTable As New DataSet(strTableName)
            Dim ObjCmdBuilder As SqlCommandBuilder
            objDSXML.ReadXml(Me.txtFilename.Text)
            objSQLConn.Open()
            objSQLTransaction = objSQLConn.BeginTransaction()
            objAdapter = New SqlDataAdapter("SELECT * FROM " & strTableName & " WHERE 1 = 2", objSQLConn)
            objAdapter.Fill(objDSDBTable, strTableName)
            For Each objDataRow In objDSXML.Tables(0).Rows
                With objDSDBTable.Tables(0)
                    objDBRow = .NewRow()
                    For Each objDataColumn In objDSXML.Tables(0).Columns
                        objDBRow(objDataColumn.ColumnName) = objDataRow(objDataColumn.ColumnName)
                     Next
                    .Rows.Add(objDBRow)
                End With
                objCmdBuilder = New SqlCommandBuilder(objAdapter)
                objAdapter.Update(objDSDBTable, strTableName)
            Next
            objSQLTransaction.Commit()
        Catch ex As Exception
            objSQLTransaction.Rollback()
        Finally
            objSQLConn.Close()
        End Try