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.