Object reference not set to an instance of an object when trying to Update dataadapter.

[Andrew Gaskell]

Hi All
I want to synchronize a database table on SQL Server with MSDE on a
client laptop. I have a web service on the server which returns the
SQL table as XML. I save the XML to disk on the client and then load
it into a dataset. I then want to replace the entire contents of a
table on the local MSDE database with this dataset from the SQL
Server. The dataset seems ok - I can display the table in a datagrid
just fine. The problem is updating the MSDE table with my dataset. I
get the Object referece error when I run .update on the data adapter
(see the UpdateMSDE Sub below). Here is my client code:

'Instantiate web service
Dim SynchData As New GetData

'Collect XML from the web service
SynchData.InvokeWebService()

'Write out the XML to disk
SynchData.WriteOutData()

'Read the XML file into a dataset
SynchData.ReadDataIntoDataSet()

'Update the table in the MSDE db
SynchData.UpdateMSDE()

'*********CLASS STARTS********
Public Class GetData()
Private LocalDataSet As New DataSet


Public Sub InvokeWebService()
' Create a proxy object that is ready to invoke the XML Web service
method.
Dim svc As abcDataService.Service1 = New abcDataService.Service1

' Invoke the XML Web service method to get Item Details and Structure
data in XML format.
strXML = svc.GetItemDetailsandStructure()

End Sub

Public Sub WriteOutData()
'Write The xml string to an xml file
'TODO: ENCRYPT THIS LATER
Dim writer As New StreamWriter("D:\Somefolder\XMLDATAItemDetails.xml")
writer.Write(strXML)
writer.Close()
End Sub

Public Sub ReadDataIntoDataSet()
' Read the XML data into the local DataSet.
LocalDataSet.ReadXml("D:\Somefolder\XMLDATAItemDetails.xml")
LocalDataSet.AcceptChanges()
End Sub


Public Sub UpdateMSDE()
Dim LocalMSDECon As New
SqlConnection("Server=myworkstation\netsdk;Database=somedb;Integrated
Security=SSPI;")
'Declare a Transaction object
Dim SqlTrans As SqlTransaction
'Open the database
LocalMSDECon.Open()
'Delete all the rows in tables ItemDetails
Dim myCommandItemDetails As New SqlCommand
myCommandItemDetails.Connection = LocalMSDECon
myCommandItemDetails.CommandText = "TRUNCATE TABLE
ItemDetails"
myCommandItemDetails.ExecuteNonQuery()
'Create a DataAdapter object for every table I want to
update.
Dim ItemDetailsDataAdapter As New SqlDataAdapter("SELECT *
FROM ItemDetails", LocalMSDECon)

'create the command builders for each data adapter
Dim ItemDetailsCommand As New
SqlCommandBuilder(ItemDetailsDataAdapter)

'we must specify the transaction used by these adapter.
ItemDetailsDataAdapter.SelectCommand.Transaction =
SqlTrans

'update the database
ItemDetailsDataAdapter.Update(LocalDataSet, "ItemDetails")

'commit
SqlTrans.Commit()<-------FAILS HERE
End Sub
End Class

Any help is greatly appreciated,
Thanks
Andrew

 

[Scott Allen]

You need to explicitly create a SqlTransaction object, i.e.

SqlTrans = LocalMSDECon.BeginTransaction()