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.
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.