DataSet update issues

[Vijay Balki]

I am fetching data in DataSet - myDataSet, from a remote database using a
Web Service in my VB.NET client..Once I fetch it I store the data in XML
file (myXMLFile) using the WriteXML method of the myDataSet. The connection
to the Web Service is closed at this point.

I update this XML file in my application, and when I am ready to send the
data back, I load the XML file back to a DataSet - mySendDataSet, using
ReadXML method.

Now I send this mySendDataSet back to the Web Service that has to update the
remote database. I am having problems in updating the data back into the
database, using the WebService.

<WebMethod(Description:="Update")> Public Function SendDetails(ByVal
mySendDataSet As DataSet) As String

Dim myError As String
Dim mySQLQuery As String

Dim myConnection As New SqlClient.SqlConnection

Dim Id As Integer

Dim Server As String
Dim Database As String
Dim UID As String
Dim Password As String

Dim ConnectString As String

Try

Server =
System.Configuration.ConfigurationSettings.AppSettings.Get("Server")
Database =
System.Configuration.ConfigurationSettings.AppSettings.Get("Database")
UID =
System.Configuration.ConfigurationSettings.AppSettings.Get("SqlUser")
Password =
System.Configuration.ConfigurationSettings.AppSettings.Get("SqlPassword")

ConnectString = "Persist Security Info=False; Server=" & Server
& _
";Connect Timeout=30;database=" & Database & _
";User ID=" & UID & ";Pwd=" & Password & ";"

myConnection.ConnectionString = ConnectString
myConnection.Open()

Dim myCommand As New SqlClient.SqlCommand

Id = mySendDataSet.Tables(0).Rows(0).Item("ID")

'Delete the row first
mySQLQuery = "delete from myTable "
mySQLQuery = mySQLQuery & "where ID = " & Id

With myCommand
.Connection = myConnection
.CommandText = mySQLQuery
End With

myCommand.ExecuteNonQuery()

''Now insert the new row
mySQLQuery = "select * from myTable "
mySQLQuery = mySQLQuery & "where ID = " & Id

Dim myDataAdapter As New SqlClient.SqlDataAdapter(mySQLQuery,
myConnection)
' Ensure that the primary key is set correctly.
Dim myDataSet As New DataSet

' Fill the DataTable.
myDataAdapter.Fill(myDataSet, "myTable")
myDataSet = mySendDataSet

' Create an auxiliary CommandBuilder object for this
DataAdapter.
Dim cmdBuilder As New SqlClient.SqlCommandBuilder(myDataAdapter)

' Use it to generate the three xxxCommand objects.
'Update Here now...
myDataAdapter.InsertCommand = cmdBuilder.GetInsertCommand
myDataAdapter.DeleteCommand = cmdBuilder.GetDeleteCommand
myDataAdapter.UpdateCommand = cmdBuilder.GetUpdateCommand

myDataAdapter.Update(myDataSet, "myTable")

SendDetails = ""
myConnection.Close()

Catch myex As Exception

myError = myex.ToString
SendDetails = myError
myConnection.Close()

End Try

Do I have to do this way, I mean delete, then use the command builder
everytime. Because if I try to do without the delete, The update method
inserts the row, rather than updating. I have a index on ID column in the
database for the table. Also I am not able to use transactions, so I can
rollback the changes, if there is any issues. Can anybody suggest a
efficient way of updating the data back, or correct any mistakes I have
made.

Note: This dataset being used refers to always only one table in the
database and will have only one row to update at a time. Due to my
application restrictions, I will have to work in this disconnected fashion
and one record at a time. We are not dealing with a large amount of data,
just maybe 100 records at a time maximum.

Thanks a lot for helping
VJ

 

[Roj]

I don't know if this is overkill in your situation, but have a look at this
for ideas on your problem:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q310350

Roj