Updating the Data Source

Currently, the data we have been working with has been saved only to a local file. This data will still have to be saved to the original data source (the Northwind Traders database, in this case). To save the updated data, add another command button to the form called cmdSaveUpdate with a caption Save Update and add the following code to the click event handler of the command button cmdSaveUpdate:

  Private Sub cmdSaveUpdate_Click()
  Dim objNWRecordset As ADODB.Recordset
  Dim objNWConnection As ADODB.Connection
  Dim objXMLRecordset As ADODB.Recordset
  Dim lngFieldCounter As Long

  Set objNWRecordset = New ADODB.Recordset
  Set objXMLRecordset = New ADODB.Recordset
  Set objNWConnection = New ADODB.Connection


  objNWConnection.CursorLocation = adUseServer
  'You will need to replace IES-FUJI with the appropriate data
  'source in the following statement.
  objNWConnection.Open _
     "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
     "Persist Security Info=False;" & _
     "User ID=sa;Initial Catalog=Northwind;Data Source=IES-FUJI"
  objNWRecordset.CursorLocation = adUseServer
  objNWRecordset.CursorType = adOpenDynamic
  objNWRecordset.LockType = adLockPessimistic
  Set objNWRecordset.ActiveConnection = objNWConnection
  objXMLRecordset.Open "C:\ProductsUpdate.XML", Options:=adCmdFile
  objXMLRecordset.Filter = adFilterPendingRecords
  Do Until objXMLRecordset.EOF
     If objXMLRecordset.EditMode <> adEditAdd Then
        objNWRecordset.Open _
           "Select * From Products Where ProductID=" _
           & objXMLRecordset.Fields.Item("ProductID").OriginalValue
        If objXMLRecordset.EditMode = adEditDelete Then
           'Delete
           objNWRecordset.Delete
        Else
           'Edit
        For lngFieldCounter = 0 To objXMLRecordset.Fields.Count-1
              'Can Not Change Primary Key
           If UCase(objXMLRecordset.Fields.Item( _
              lngFieldCounter).Name) _
              <> "PRODUCTID" Then
              objNWRecordset.Fields.Item(lngFieldCounter).Value = _
                 objXMLRecordset.Fields.Item(lngFieldCounter).Value
           End If
        Next
        End If
     Else
        objNWRecordset.Open _
           "Select * From Products Where ProductID=" & 0
        objNWRecordset.AddNew
          'Add New
        For lngFieldCounter = 0 To objXMLRecordset.Fields.Count - 1
           'Auto Increment field for productID
           If UCase(objXMLRecordset.Fields.Item( _
              lngFieldCounter).Name) _
              <> "PRODUCTID" Then
              objNWRecordset.Fields.Item(lngFieldCounter).Value = _
                 objXMLRecordset.Fields.Item(lngFieldCounter).Value
           End If
        Next

     End If
     objNWRecordset.Update
     objNWRecordset.Close
     objXMLRecordset.MoveNext
     Loop
  End Sub

Once again, we create a Connection object called objNWConnection to connect to the Northwind Traders database, and a Recordset object called objNWRecordset to hold the data from the ProductsUpdate.xml file. You will need to configure the data source and change the connection string again, just like in the previous example. ObjNWRecordset is used to get a reference to the record that is being updated using a SELECT statement with a WHERE clause.

The second Recordset object called objXMLRecordset is used to retrieve the XML data, which contains the data that has been added, edited, or deleted. You can also get the XML data from an ADO data stream, which we'll cover in the section "Working With Streams" later in this chapter.

Once we have obtained the XML data stored in the objXMLRecordset recordset, we apply a filter so that the only visible records are the ones that have had changes done to them or are new records. We then move through each record in objXMLRecordset that is new or has been changed and retrieve that record from the database using objNWRecordset.

Once we have only the records that are about to be changed, we can perform the correct operations: AddNew, Delete, and Edit. We begin by checking the EditMode property of objXMLRecordset to find out which operation was being done on this record, and then perform the operation accordingly.

If you have worked with disconnected ADO recordsets before, you might have expected that we would use the UpdateBatch method of the ADO recordset. Unfortunately, the disconnected ADO recordset created using XML has no reference to the original table that was used to get the data. Thus, even though you can create an ADO connection to the correct database and set the recordset's ActiveConnection property to this connection, there is simply no way of connecting the recordset to the right table. Because the recordset cannot be connected to the correct table, the UpdateBatch method cannot work. As you can see from the above example, we have created two Recordset objects: ObjXMLRecordset and objNWRecordset.

NOTE
In order to keep the code simple, the example we have been working with does not include error handling. Remember that all production code should have error handlers. In this example, you would need to check the record that is about to be changed to make sure it actually exists in the database and has not been changed by someone else. You can check the status of the data by using the PreviousValue property of the Recordset object for each field. The PreviousValue property will give the value of the field before it was changed. We have used query strings containing table and field names in the code; however, in production code, we would use constants so that only the value of the constant would need to be changed if the table or field names changed.

[Previous] [Contents] [Next]