You are currently offline, waiting for your internet to reconnect

How to update parent-child data with an Identity column from a Windows Forms application by using a Web service in Visual Basic .NET

This article was previously published under Q310350
This step-by-step article describes how to retrieve a DataSet object from a Web service, how to send DiffGram updates to the Web service, and then how to merge the updated DiffGram back into the client DataSet. This process involves the following steps:
  1. The client application retrieves a DataSet with two DataTable objects in a parent-child relationship from the Web service. The parent DataTable has an Identity/Autonumber column as the Primary Key.
  2. In the client application, the user can add, delete, and modify both parent records and child records. New parent records receive a temporary primary key value that is generated locally.
  3. The client application sends changes back to the Web service as a DiffGram.
  4. The Web service updates the database, retrieves the actual primary key values for new parent records, and then propagates the changed key values to the child records.
  5. The client application receives the updated values from the Web service and then merges the updated values back into the local DataSet.
For more information about how to update a single table by using a Web service, click the following article number to view the article in the Microsoft Knowledge Base:
308056 How to update server data through a Web Service by using ADO.NET and Visual Basic .NET


The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:
  • Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
  • Microsoft Visual Studio .NET
This article assumes that you are familiar with the following topics:
  • Visual Studio .NET
  • ASP.NET fundamentals and syntax
  • ADO.NET fundamentals and syntax
The code samples in this article use http://localhost as the Web server. Additionally, the code samples use the Northwind database as the database. The Northwind database is included with Microsoft SQL Server.

Create a Web service

  1. Start Visual Studio .NET.
  2. Create a new Visual Basic ASP.NET Web Service project by following these steps:
    1. On the File menu, point to New, and then click Project.
    2. In the New Project dialog box, click Visual Basic Projects under Project Types, and then click ASP.NET Web Service under Templates.
    3. In the Location box, replace the existing address with the following address, and then clickOK.
  3. On the Service1.asmx.vb[Design] page, switch to Code view. The Code window for the Web service appears.
  4. At the top of the Code window, add the following Imports statements:
    Imports System.DataImports System.Data.SqlClient					
  5. After the following lines of code
    Public Class Service1           Inherits System.Web.Services						
    add the following code:
        <WebMethod()> Public Function GetData() As DataSet        Dim con As New SqlConnection("server=<SQLServer>;uid=<myid>;pwd=<mypwd>;database=northwind")        'Pull back the recent orders for the parent rows.        Dim daOrders As New SqlDataAdapter("SELECT * FROM Orders WHERE OrderDate >= '05/01/1998'", con)        'Get only the appropriate child rows for the preceding parent rows.        Dim daDetails As New SqlDataAdapter("SELECT * FROM [Order Details] WHERE OrderID in ( SELECT OrderID FROM Orders WHERE OrderDate >= '05/01/1998')", con)        Dim ds As New DataSet()        Dim dt As DataTable        Dim dc As DataColumn        Dim cb As SqlCommandBuilder        '        ' Fill DataSet and set DataRelation for navigating in the DataGrid.        '        con.Open()        daOrders.FillSchema(ds, SchemaType.Mapped, "Orders")        daOrders.Fill(ds, "Orders")        daDetails.FillSchema(ds, SchemaType.Mapped, "Details")        daDetails.Fill(ds, "Details")        ds.Relations.Add("OrdDetail", ds.Tables!Orders.Columns!OrderID, ds.Tables!Details.Columns!OrderID)        '        ' Set AutoNumber properties in the Orders DataTable.        '        dt = ds.Tables!Orders        dc = dt.Columns!OrderID        dc.AutoIncrement = True        dc.AutoIncrementSeed = -1        dc.AutoIncrementStep = -1        '        'Return the DataSet to the client.        '        GetData = ds    End Function    <WebMethod()> Public Function UpdateData(ByVal ds As DataSet) As DataSet        Dim con As New SqlConnection("server=<SQLServer>;uid=<myid>;pwd=<mypwd>;database=northwind")        Dim daOrders As New SqlDataAdapter("SELECT * FROM Orders WHERE OrderDate >= '05/01/1998'", con)        Dim daDetails As New SqlDataAdapter("SELECT * FROM [Order Details] WHERE OrderID in ( SELECT OrderID FROM Orders WHERE OrderDate >= '05/01/1998')", con)        '        ' Get commands for Orders table.        ' Reselect record after insert to get new Identity value.        ' Must get the schema, which you did in GetData(), before getting commands,         ' or the Command Builder will try to insert new rows, based         ' on the Identity column.        '        con.Open()        Dim cb As SqlCommandBuilder        cb = New SqlCommandBuilder(daOrders)        daOrders.UpdateCommand = cb.GetUpdateCommand        daOrders.DeleteCommand = cb.GetDeleteCommand        daOrders.InsertCommand = cb.GetInsertCommand        daOrders.InsertCommand.CommandText &= "; Select * From Orders Where OrderID = @@IDENTITY"        '        ' UpdateRowSource tells the DataAdapter that there will be a re-selected record.        '        daOrders.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord        cb = Nothing        '        ' Get commands for Order Details table.        '        ' Must set the QuotePrefix and QuoteSuffix or the CommandBuilder won't put [ ]        ' around the table name.        '        cb = New SqlCommandBuilder(daDetails)        cb.QuotePrefix = "["        cb.QuoteSuffix = "]"        daDetails.UpdateCommand = cb.GetUpdateCommand        daDetails.DeleteCommand = cb.GetDeleteCommand        daDetails.InsertCommand = cb.GetInsertCommand        cb = Nothing        '        ' Create a new DataAdapter based on the original one to prevent the        ' CommandBuilder from modifying the SQL statements,         ' specifically the custom InsertCommand.        '        ' You don't need this if you roll your own commands and parameters or use         ' the visual tools to do it.        '        Dim daOrd2 As New SqlDataAdapter()        daOrd2.DeleteCommand = daOrders.DeleteCommand        daOrd2.InsertCommand = daOrders.InsertCommand        daOrd2.UpdateCommand = daOrders.UpdateCommand        '        ' Use a delegate to prevent AcceptChanges from occurring on Deletes & Inserts.         ' This is for a limitation of the DataAdapter; see Q313540.         '        AddHandler daOrd2.RowUpdated, AddressOf daOrd2_MyRowUpdated					   '        AddHandler daDetails.RowUpdated, AddressOf dadetails_MyRowUpdated        '        'See Q313483 INFO: Roadmap for ADO.NET DataAdapter Objects.         daDetails.Update(GetDeletedRows(ds.Tables("details")))        daOrd2.Update(GetDeletedRows(ds.Tables("orders")))        daOrd2.Update(ds.Tables("orders").Select("", "", DataViewRowState.ModifiedCurrent))        daDetails.Update(ds.Tables("details").Select("", "", DataViewRowState.ModifiedCurrent))        daOrd2.Update(ds.Tables("orders").Select("", "", DataViewRowState.Added)) ' Otherwise, you see an integrity violation, because of the parent row's        ' orphaned child rows. You get an orphaned child row temporarily, because        ' you store the original pseudo foreign key back to the child row and issue        ' an AcceptChanges to force it to the OriginalValue property. You then restore        ' the actual foreign key value back to the row without AcceptChanges(). This        ' puts the child row in the correct state to be merged with the original DataSet        ' on the client. All of this functionality is handled in the RowUpdated delegate        ' of the Order Details DataAdapter (see the daOrd2_MyRowUpdated procedure, which follows this procedure).        ds.EnforceConstraints = False        daDetails.Update(ds.Tables("details").Select("", "", DataViewRowState.Added))        'Turn the integrity checking back on, because you turned it off earlier.        ds.EnforceConstraints = True        con.Close()        'Send the DataSet back to the client to have the DataSet merged back in.        UpdateData = ds    End Function    Private Sub daOrd2_MyRowUpdated(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs)        If e.StatementType = StatementType.Insert Then e.Status = UpdateStatus.SkipCurrentRow        If e.StatementType = StatementType.Delete Then e.Status = UpdateStatus.SkipCurrentRow    End Sub    Private Sub dadetails_MyRowUpdated(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs)  ' When the primary key propagates down to the child row's foreign key field, the field        ' does not receive an OriginalValue with pseudo key value and a CurrentValue with the         ' actual key value. Therefore, when the merge occurs, this row is  appended to the DataSet        ' on the client tier, instead of being merged with the original row that was added.        If e.StatementType = StatementType.Insert Then            'Don't allow the AcceptChanges to occur on this row.            e.Status = UpdateStatus.SkipCurrentRow            ' Get the Current actual primary key value, so you can plug it back            ' in after you get the correct original value that was generated for the child row.            Dim currentkey As Integer = e.Row("OrderID") '.GetParentRow("OrdDetail")("OrderID", DataRowVersion.Current)            ' This is where you get a correct original value key stored to the child row. You yank            ' the original pseudo key value from the parent, plug it in as the child row's primary key            ' field, and accept changes on it. Specifically, this is why you turned off EnforceConstraints.            e.Row!OrderID = e.Row.GetParentRow("OrdDetail")("OrderID", DataRowVersion.Original)            e.Row.AcceptChanges()            ' Now store the actual primary key value back into the foreign key column of the child row.            e.Row!OrderID = currentkey        End If        If e.StatementType = StatementType.Delete Then e.Status = UpdateStatus.SkipCurrentRow    End Sub Private Function GetDeletedRows(ByVal dt As DataTable) As DataRow()        Dim Rows() As DataRow        If dt Is Nothing Then Return Rows        Rows = dt.Select("", "", DataViewRowState.Deleted)        If Rows.Length = 0 OrElse Not (Rows(0) Is Nothing) Then Return Rows        '        ' Workaround:        ' With a remoted DataSet, Select returns the array elements        ' filled with Nothing/null, instead of DataRow objects.        '        Dim r As DataRow, I As Integer = 0        For Each r In dt.Rows            If r.RowState = DataRowState.Deleted Then                Rows(I) = r                I += 1            End If        Next        Return Rows    End FunctionEnd Class      
  6. Modify the SqlConnection string to correctly connect to the server that is running SQL Server.

Test the Web service

  1. Press F5 to compile and to run the Web service. A Web page is returned. With this Web page, you can interact with the Web service in Microsoft Internet Explorer. Note that the URL of the returned page is http://localhost/vbUpdateData/Service1.asmx.
  2. On the Service1 Web page, click GetData. A Web page is returned that displays details about the GetData Web method.
  3. Close the Web pages.

Create a client application

  1. In Visual Studio .NET, create a new Visual Basic Windows Application project. Form1 is added to the project, by default.
  2. Add one Button control and one DataGrid control to Form1. Button1 and DataGrid1 are added to the project by default.
  3. Change the Name property of Button1 to btnSave, and then change the Text property of Button1 to Save.
  4. On the Project menu, click Add Web Reference. Type the URL for your Web service (in this case, type http://localhost/vbUpdateData/Service1.asmx), press ENTER, and then click Add Reference. The newly-added Web reference appears in Solution Explorer.
  5. To declare a form level member for the DataSet, add the following code after the Inherits statement:
    Public ds as Dataset
  6. Add the following code to the Form Load method:
            Dim MyWS As localhost.Service1        MyWS = New localhost.Service1()        ds = MyWS.GetData()        DataGrid1.DataSource = ds        DataGrid1.DataMember = "Orders"					
  7. Switch to Form view.
  8. Open the Code window for the Save button. Paste the following code into the BtnSave_Click (Save) event procedure:
            Dim myWS As localhost.Service1        myWS = New localhost.Service1()        Dim MyChangedRows As DataSet        DataGrid1.DataMember = ""        DataGrid1.DataSource = Nothing        'Pull out only what you must send over the wire.        MyChangedRows = ds.GetChanges        MyChangedRows = myWS.UpdateData(MyChangedRows)        'You must accept changes on the Dataset because of a known problem. See KB 313540.        ds.AcceptChanges()        ds.EnforceConstraints = False        'Merge in the parent rows first, and then the child rows.        ds.Merge(MyChangedRows.Tables!Orders, False, MissingSchemaAction.Ignore)        ds.Merge(MyChangedRows.Tables!details, False, MissingSchemaAction.Ignore)        'AcceptChanges that you have made to the DataSet.        ds.AcceptChanges()        'Turn on the integrity constraints that you turned off earlier.        ds.EnforceConstraints = True        DataGrid1.DataSource = ds        DataGrid1.DataMember = "Orders"					

Test the client application

  1. Press F5 to compile and to run the client application.
  2. In DataGrid1, modify some of the data, and then click Save.

    NOTE: Do not change the key field. If you change the key field, you receive an error message that states that you are breaking referential integrity on the server.
  3. Add a child row to an existing parent row, and then note that the child row automatically receives the correct foreign key value.
  4. Add the following values for new parent rows and child rows, and then click Save. Note the key values before and after you click Save:
    • Orders.CustomerID=RATTC
    • Orders.EmployeeID=1
    • Orders.OrderDate=2/2/2002
    • Details.ProductID=1
    • Details.UnitPrice=18
    • Details.Quantity=1
    • Details.Discount=0


If you use Integrated Security in your database connection string, you mustmake sure that the Web service account has rights to the database. If the Web service account does not have rights to the database, you receive an HTTP 500 error when you test the Web service in the browser.

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
308056 How to update server data through a Web service by using ADO.NET and Visual Basic .NET
313483 Roadmap for ADO.NET DataAdapter objects

Article ID: 310350 - Last Review: 03/28/2005 23:21:05 - Revision: 4.2

Microsoft Visual Basic .NET 2002 Standard Edition, Microsoft Visual Basic .NET 2003 Standard Edition, Microsoft ASP.NET (included with the .NET Framework), Microsoft ASP.NET 1.1, Microsoft ADO.NET (included with the .NET Framework), Microsoft ADO.NET 1.1

  • kbhowtomaster KB310350