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.AppSett ings.Get("Server")
Database =
System.Configuration.ConfigurationSettings.AppSett ings.Get("Database")
UID =
System.Configuration.ConfigurationSettings.AppSett ings.Get("SqlUser")
Password =
System.Configuration.ConfigurationSettings.AppSett ings.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
