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.
Code:
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