472,374 Members | 1,568 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,374 software developers and data experts.

DataSet update issuse

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


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

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


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

Dim myDataAdapter As New SqlClient.SqlDataAdapter(mySQLQuery,
' 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
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 = ""

Catch myex As Exception

myError = myex.ToString
SendDetails = myError

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

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
Nov 20 '05 #1
0 4844

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Babu Mannaravalappil | last post by:
Can somebody please help me figure out why the following method exceptions out? Execution at the line marked with ********** hangs for about 15 seconds and then I get an error that says an...
by: Mojtaba Faridzad | last post by:
Hi, Please check these lines: DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet, "mytable"); DataRow row; row = dataSet.Tables.Rows; row.BeginEdit(); row = "555";
by: JIM.H. | last post by:
Hello, Can I send a dataset as a parameter into stored procedure and import data to a table in the stored procedure? Thanks, Jim.
by: jay | last post by:
I am using the dataset object to add a row to a sql server database in vb.net code, as follows: dim drow as DataRow dim cmdBld as new SqlCommandBuilder(mySqlDataAdapter) ds.tables(0).NewRow()...
by: jaYPee | last post by:
I have search a lot of thread in google newsgroup and read a lot of articles but still i don't know how to update the dataset that has 3 tables. my 3 tables looks like the 3 tables from...
by: Dan Sikorsky | last post by:
How do you iterate thru a dataset to change money fields to a different value? Here's what I have. My dataset is filled directly from a stored procedure. ' Create Instance of Connection and...
by: Graham Blandford | last post by:
Hi all, Would someone be able to tell me the most 'graceful' way of removing unwanted rows from a dataset based on a condition prior to update? OR, resetting the rows all to unchanged after they...
by: Peter Proost | last post by:
Hello group, what would be the best way to do the next thing: I've got a grid form with about 15000 records, when I double click a row a detail form is opened and the user can modify and save...
by: A_PK | last post by:
I have problem databinding the DataGrid with DataView/DataSet after the filter... I create the following proceudre in order for user to filter as many as they want, but the following code is only...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.
by: F22F35 | last post by:
I am a newbie to Access (most programming for that matter). I need help in creating an Access database that keeps the history of each user in a database. For example, a user might have lesson 1 sent...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.