Hello there..
the basic question is this - are there known issues with a sqldataadapter
update method not processing all of the deletes and / or updates listed in
the modified table?
Now - the long version so you know the background...
ok - the predicament of the night!
I have a windows app that is designed to run in a disconnected manner to a
sql server hosted on an Intranet. Basically, I press a button - which loads
all of the data off of the sql server in the database into a Dataset. From
there, I load each table in the dataset into an indivudal DataTable - worked
a little better with MDI forms.
The pp contains multiple tabs - which have a datagrid on them. The datagrid
loads data pulled from a DataView that is loaded by filling it from the
DataTable that was previously defined. Now, when I want to update the SQl
server database, I do the following.
1. Detect the change on the DataTable using the following code where
m_dtsystems is a DataTable.
Dim changeadd As DataTable = m_dtsystems.GetChanges(DataRowState.Added)
Dim changedelete As DataTable =
m_dtsystems.GetChanges(DataRowState.Deleted)
Dim changemodified As DataTable =
m_dtsystems.GetChanges(DataRowState.Modified)
' try statement is wrapped in UpdateTable procedure for easier use
Try
If Not (changeadd Is Nothing) Then
MsgBox("Add detected")
UpdateTable(changeadd)
changeadd.Dispose()
End If
If Not (changedelete Is Nothing) Then
MsgBox("Delete detected")
UpdateTable(changedelete)
changedelete.Dispose()
End If
If Not (changemodified Is Nothing) Then
MsgBox("Edit detected")
UpdateTable(changemodified)
changemodified.Dispose()
End If
catch ex as exception
end try
2. Depending on the value of one of the tables, I then, as you see, pass the
newly created DataTable (the one with modifications) to a function called
UpdateTable. This function is responsible for passing the changed data to the
webservice and the code is as follows:
Private Sub UpdateTable(ByVal dttable As DataTable)
Try
Dim myds As New DataSet
myds.Merge(dttable)
Dim ws As New localhost.SQLWorks
Dim mycount As Integer
mycount = ws.UpdateTable(myds)
MsgBox("Successful Save to Web Database! ... " & mycount & "
records affected!")
Catch ex As Exception
MsgBox("Error Saving to Web Database! - " & ex.ToString)
End Try
End Sub
3. This method calls the UpdateTable web method of my web service which
creates a new DataSet, takes the supplied DataTable and merges into the
dataset and then updates the sql database via the adapter using the modified
Dataset. The code:
<WebMethod()> Public Function UpdateTable(ByVal ds As DataSet) As Integer
Dim rowcount As Integer = 0
Dim tablename As String = ds.Tables(0).TableName.ToString
Try
' dataadapter opens and closes connection automatically
Dim connection As New SqlConnection("Initial catalog=sqldev01;"
& "Data Source=(local); User ID=testuser; password=test321")
Dim query As String = "SELECT * from " & tablename
Dim dbadapter As SqlDataAdapter = New SqlDataAdapter(query,
connection)
Dim cmdbldr As New SqlCommandBuilder(dbadapter)
dbadapter.InsertCommand = cmdbldr.GetInsertCommand
dbadapter.DeleteCommand = cmdbldr.GetDeleteCommand
dbadapter.UpdateCommand = cmdbldr.GetUpdateCommand
rowcount = dbadapter.Update(ds, tablename)
UpdateTable = rowcount
Catch ex As Exception
UpdateTable = 21000
End Try
End Function
*** THE PROBLEM***
This works great for all tables and all basic modifications to the tables.
The problem I have is when I attempt to delete or edit more than one record
in the DataTable the is stored on the client app. It throws an error! It will
allow me to Add as many records as I want and remove a single record. I can
also edit up to three records at a time. But I can't delete, say 4 records at
a time. The updateTable web service exception gets thrown.
I know this is long winded - but can ANYONE help me get through this. Been
on it for some 12 hours strasight.
thanks in advance.
-mike