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.
Working With Streams
In the above examples, we have been saving information to data files. It's likely that you will not want to do this for most of your applications. Instead, you can work directly with data streams that can pass the data into a DOM object or pass it back to the client in an ASP Response object. Now we'll look at an example that shows how we can use a stream to place the data directly into the DOM objects.
This example will load data from a text file and place it into an ADO Stream object. The data will then be loaded into a DOM object. Once you have the data in the DOM object, you can do almost anything you want to it, including transforming it with XSL. To place the data into an ADO Stream object, add another command button to the form. Name it cmdStream with a caption Stream and add the following code to the click event handler of the command button cmdStream:
Private Sub cmdStream _Click() Dim objNWRecordset As ADODB.Recordset Dim objADOStream As ADODB.Stream Dim objDOM As DOMDocument Dim strXML As String Dim objNWConnection As ADODB.Connection Set objADOStream = New ADODB.Stream Set objDOM = New DOMDocument Set objNWRecordset = New ADODB.Recordset Set objNWConnection = New ADODB.Connection objNWConnection.CursorLocation = adUseClient '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 = adUseClient objNWRecordset.CursorType = adOpenStatic Set objNWRecordset.ActiveConnection = objNWConnection objNWRecordset.Open "Products" objADOStream.Open objNWRecordset.Save objADOStream, adPersistXML strXML = objADOStream.ReadText objDOM.loadXML strXML End Sub |
Just as in our other examples, this code creates an ADO Connection object and a Recordset object. Once the connection to the Northwind Traders database has been made and the recordset contains the data from the Products table, the XML data is saved to an ADO Stream object called objADOStream. The information is passed from the ADO Stream object to a string variable called strXML, and then the XML data is placed into a DOM document object called objDOM using the loadXML method of the document object. In this way, we have opened data from a regular database and passed the data as XML into a DOM document object.
As you can see, the ADO recordset is versatile and can be used on both the client and the server to build powerful data access components in distributed applications.
EAN: 2147483647
Pages: 115