Managing Recordsets Using XML Stream Objects


After completing the preceding XML example, I started asking questions and found a few answers that really opened up a whole new paradigm using XML. While you might be tired by now of XML, I think you'll find this next sample to be well worth the time it takes to study it.

The preceding example used an ASP that generates a parameter-driven Recordset passed back as a formatted XML HTML table. The ASP page expected the client to be a browser-based Web page that captured the name parameter and maximum number of rows to pass. In contrast, this next example shows how to use Visual Basic as your frontend (hardly a radical concept) to execute the ASP code. But in this case, we create an updatable Recordset, make changes to it, and send the disjoint Recordset back to an ASP to be posted to the database using the UpdateBatch method. The tricky part (if that's not tricky enough) is to manage the errors. These errors are (or could be) generated at every phase—when we open a connection, run the initial query, construct the XML, send and receive the XML, post the changes back to IIS, and get the results of the UpdateBatch. The architecture of this new sample is shown in Figure 9-2.

click to expand
Figure 9-2: Returning XML from an ASP via URL reference to pass a Recordset

The approach here is similar in many respects to other architectures that we have discussed. It leverages code executed on another platform—IIS in this case. However, unlike executing compiled stored procedures on SQL Server, which are written (just) in TSQL, this code is interpreted on the fly, which runs considerably slower than compiled stored procedures. On the other hand, VBScript is more flexible than TSQL, so there are advantages that might (just might) outweigh the lack of speed. One of these differences is the ASP code's capability to execute binary code developed in Visual Basic, C, or any other language. This binary code can also execute the same stored procedures you've been using all along—and so can the ASP VBScript code.

But there's still another issue here. While stored procedures communicate with the client (or component) through Tabular Data Stream (TDS) (through remote procedure call (RPC) protocol), our IIS ASP code sends us (and expects to receive) HTTP-formatted structures. This means a lot more packets are sent over the wire. But, as Edward (my technical editor) pointed out to me, this comparison

is not exactly apple vs. apple. The bottom line is that this newly emerging paradigm is very different, but far more flexible in many respects than those we have used and depended on for decades.

Examining the Visual Basic to ASP Code Example

Let's step through the three code modules one at a time. First, we examine the Visual Basic client application which captures our query filter to be passed as a parameter to the WHERE clause of the ASP that executes the query for us.

Basically, the Visual Basic client program works like this. We start the application and launch an initial query using the current value in the query criteria TextBox (txtQueryParm). To run the query, we simply use the Recordset Open method to address and run the query ASP (recordsetxml.asp), passing the query argument in the URL. The ASP returns XML, which ADO converts directly into a Recordset on the client (a feature implemented in ADO 2.1).

This is where the first little trick comes in. ADO expects a Recordset to be returned by the ASP, not an error message, but because this is not a Web page, we can't just execute a Response.Write on the ASP to tell the user what went wrong.So, to pass back the error number and description, the ASP constructs a new Recordset and adds a row to it containing the error and its details. Cool? When the application gets a click event on the MSHFlexGrid control, it populates a set of TextBox controls that are used to validate any changes made.

After the user clicks the Update command button, we use another technique to send back the Recordset to the ASP that will do the UpdateBatch. This code uses the MSXML object to send and receive XML from the ASP—a new feature of ADO 2.5. This object not only provides a way to pass our Recordset (saved to a Stream containing the XML equivalent of the Recordset), but a way to retrieve the response from the ASP. This became very handy when I needed to pass back error information as comments embedded in the XML, because the MSXML object exposes the ResponseText as well as the ResponseXML.

If errors do occur (and errors do occur), the user is given an option to retry the operation or simply force through their changes. Included in the source code is a skeleton treatment of the errors that can occur. We set the Update Criteria property to force the changes through—or simply resubmit the query.

Incidentally, because this program is provided in source on the CD, I won't bore you here with many of the piddling details—I'll just show the highlights unique to this approach. You'll recognize many of the enumerations and other common techniques we have discussed before, so I won't go over them again.


I found that it was easier to develop my prototype ASP code in the Visual Basic 6.0 IDE. Once it's working in Visual Basic, transporting the working code to the ASP was a snap.

The Client-side Visual Basic Code

We start by creating a constant that points to the IIS server and our Web root. Nope, that server is not on the WWW, so don't bother looking. The txtField TextBox is populated when we get a click event on the MSHFlexGrid control.

 Const WebHost As String = "http://betav7/testxml/myhtml/" 

The following code runs when the user changes the criteria and clicks the Requery command button. It's also run when the application first starts. It invokes the ASP and passes the current criteria with the URL. The Recordset Open is either passed a rowset from the ASP or a new Recordset containing the error type (connection or query), number, and description. If the first Field is named EType, then we know we have an error embedded in the XML stream.

However, if there's no error, there's a rowset. In this case, we simply fill the grid with the Recordset data.

 Private Sub cmdRequery_Click() On Error GoTo cmdEH If rs.State = adStateOpen Then rs.Close rs.Open WebHost & "recordsetxml.asp?Query=" & txtQueryParm If InStr(rs.Fields(0).Name, "EType") Then     ' The connection or query did not work.     The XML has the error messages... not the rows     Select Case rs.Fields("EType")            ' Error Type         Case enuError.Connection             MsgBox "The active server page could not connect to the server. "_             & vbCrLf & _ rs.Fields("Description"), _             vbCritical, "Error Connecting"     ' Description field         Case enuError.OpenRecordset             MsgBox "The active server page could not execute the query. " _             & vbCrLf & rs.Fields("Description"), vbCritical, _             "Error Opening"    ' Description field         Case Else             'huh             MsgBox "Unrecognized error type. Programming error", vbCritical     End Select  Else Set MSHFlexGrid1.Recordset = rs End If Quit:     Exit Sub 

The next section of the code runs when the user clicks the Update command button. We construct an MSXML object to pass a Stream object containing the Recordset (in XML) to the ASP where the UpdateBatch method will be applied.

 Private Sub cmdUpdate_Click() Dim strMsg As String Dim stm As ADODB.Stream ' Microsoft XML Version 2.0 Dim xml As MSXML.XMLHTTPRequest Dim rsLocal As Recordset Dim fld As Field Dim stmXML As Stream On Error GoTo cmdUpdateEH Set xml = New MSXML.XMLHTTPRequest Set rsLocal = New Recordset rsLocal.CursorLocation = adUseClient rsLocal.Properties("Update Criteria") = adCriteriaAllCols RetryUpdate: Set stm = New ADODB.Stream rs.Save stm, adPersistXML           ' Save global RS to Stream ' ' Hit the page that can do the UpdateBatch we could send ' a flag to request either a rowset or an error ' xml.Open "POST", WebHost & "UpdateXMLRS.ASP", False xml.send stm.ReadText 

The ASP tries to update the database, but stuff (usually) happens, so you can occasionally expect to get back an XML stream containing the "updated" (but with pending rows) Recordset to see which rows did not update.

After the Recordset arrives (remember, it only comes back if there were collision errors), we can use the Filter property to deselect the rows that were not affected. The Recordset still has all of the rows in it, so if you simply pass this filtered Recordset to the grid, you'll see them all. That's why I used another routine to fill the grid manually. The error number and description values are passed back from the update ASP through the comments tag in the XML. When we look at the update ASP code, we'll see how that's done.

 If xml.responseText <> "" Then              ' Test for error text or rowset     rsLocal.Open xml.responseXML            ' Open rowset     rsLocal.Filter = adFilterPendingRecords     If rsLocal.RecordCount > 0 Then         DumpRsToGrid rsLocal     If InStr(xml.responseText, "RSUpdate error") Then     ' Deal with the errors… 

The Server-side ASP Query Code

This code illustrates a couple of new techniques. Sure, I leveraged the code used in the first XML example in this chapter, but in this case, we can't send back error information via HTML—we can only respond with a Recordset. That's because we're using the ADO Recordset Open method to fetch the rows. If something goes wrong with the connection or query, we have to report that fact to the client. Of course, we could just return nothing to the client and have it use the ADO "GuessWhatWentWrong" function, but that has not worked for me since I installed Age of Empires on my system. Again, for brevity, I'll leave off some of the code we already discussed.

Just as with earlier ASP pages, we construct the ADO objects by executing Server.CreateObject. We use a separate Connection object to make it easier to handle Connection errors. We also create a Recordset and a Stream object to pass the data from layer to layer.

 ' Initialize objects: On error resume next set cn = server.CreateObject ("ADODB.Connection") Set rs = Server.CreateObject("ADODB.Recordset") Set stm = Server.CreateObject("ADODB.Stream") 

The next code builds a ConnectionString, and if the connection opens, it opens a Recordset based on the parameter concatenated to the end of the URL. We have no way to construct an HTML Form object in Visual Basic, so we have to set the Form Method attribute to Get". We also make sure the Recordset is constructed with optimistic batch locks so ADO can pass it back to the client in read-write mode. Remember, the default mode is RO.

 cn.ConnectionString = "file name=c:\biblio.udl" cn.CursorLocation = aduseclient cn.Open if err then     BuildErrorRecord 0, err, err.Description else     Parm = Request.QueryString("Query")     rs.Open "select Au_ID, Author, Year_Born " _     & " from authors where author like '" & Parm & "%'" Order By Author", _         cn, adOpenKeyset, adLockBatchOptimistic, adCmdText     if err then         BuildErrorRecord 1, err, err.Description 'type 1 = OpenRecordset     end if End if 

Okay, the Recordset is ready to be sent back. This next code persists the Recordset to the local Stream object in XML format. It then writes the Unicode-format information back to the sending "page" (our Visual Basic application). Yup, that's a lot of bits. Note that we didn't set the Recordset ActiveConnection property to Nothing as we did when sending back Recordset objects in other middle-tier designs. Why? BHOM, but it works.

 rs.Save stm, adPersistXML Response.ContentType = "text/xml" Response.Write stm.ReadText 

If something goes wrong when opening the connection or running the query, we construct a new Recordset to save the error information. The following routine is executed when there are errors to report back to the Visual Basic layer. We construct a raw Recordset object with three fields to hold the error type (connection or query), the error number, and the error description. Remember, when building ADO Recordset Field objects, if the Field is not large enough to hold the value passed to it, ADO ignores the whole value. For example, if you create a 100-byte string field, and try to set its Value property to a 101-byte string, ADO simply ignores the operation without making a peep. I would have expected it to truncate at 100 bytes, but no, it just ignores the whole string. How rude!

 Sub BuildErrorRecord (intType, intErr, strDescription)     set rs = server.CreateObject ("adodb.Recordset")     With rs         .Fields.Append "EType", adInteger         .Fields.Append "Error", adInteger         .Fields.Append "Description",adVarChar,255         .open            ' Create new Recordset just for errors         .addnew                ' Add a new row         .fields("EType") = intType         .fields("Error") = intErr         .fields("Description") = strDescription         .UpdateBatch     End With End Sub 

The Server-side ASP Update Code

The last of our three routines has one responsibility—post the changes made at the client to the database. As with the other ASP pages we've seen so far, we use On Error Resume Next to disable the VBScript error handlers.


ADO is missing a key element here. Too many times I wanted to pass error or user-defined status information back with the Recordset. It seems to me that there should be an easier way to define additional Properties for the Recordset that are marshaled along with the rowset.

 <!—#include File=""—> <% Dim rs, stm, srtoption, ErrorMessage on error resume next Set rs = Server.CreateObject ("ADODB.Recordset") Set stm = Server.CreateObject("ADODB.Stream") 

This ASP page is fairly simple. We only need a Recordset object and a Stream. We reconstruct the Recordset object from the Stream passed to the ASP. Note the Open method using the Request object as the Source argument. We then reassociate the connection string and tell ADO to post the changes to the database.

 with rs     .CursorLocation = adUseClient     .Open Request     .ActiveConnection = "dsn=LocalServer;uid=TestASP;pwd=Secret"     .UpdateBatch end with 

If nothing goes wrong, the ASP ends and returns control to the Visual Basic app that invoked it—it sends nothing back. I guess we could send back some XML comments providing records affected or similar feedback.

However, if something does go wrong, then we have to construct an XML string that contains the error information and the Recordset—it now has new Status information for those rows that did not get updated for some reason. Simply sticking a comment into the XML stream, which gets returned in the MSXML ResponseText property does this.

 if err then         ErrorMessage = "RSUpdate error: " & err.Description_         & "[" & err.Number & "]"         rs.Save stm,adPersistXML ' See the tip below     ' Set content type to xml, specify XSL for formatting,     ' then push XML stream text to calling component:         Response.ContentType = "text/xml"         'The following is treated as a comment         Response.Write "<!— " & ErrorMessage & " —>"         Response.Write stm.ReadText end if 


In Windows 2000 (thus ADO 2.5 and later) you can simply save directly to the Response object instead of saving to a stream first. Thus:

 rs.Save Response, adPersistXML 

As this example illustrates, there are a lot of innovative techniques to pass data between layers—between client and server, between Web client and IIS, and between middle-tier components and the rest of the layers. I expect several of these techniques will stabilize and grow easier to use as we get closer to shipping Visual Basic 7.0—when we'll start all over again.


ADO Examples and Best Practices
Ado Examples and Best Practices
ISBN: 189311516X
EAN: 2147483647
Year: 2000
Pages: 106

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: