XML Recordset persistence scenario

Article
09/14/2021

Applies to: Access 2013, Office 2013

In this scenario, you will create an Active Server Pages (ASP) application that saves the contents of a Recordset object directly to the ASP Response object.

Note

This scenario requires that your server have Internet Information Server 5.0 (IIS) or later installed.

The returned Recordset is displayed in Internet Explorer using an RDS.DataControl.

The following steps are necessary to create this scenario:

Set up the application.
Get the data.
Send the data.
Receive and display the data.

Step 1: Set up the application

Create an IIS virtual directory named XMLPersist with script permissions.
Create two new text files in the folder to which the virtual directory points, one named XMLResponse.asp, and the other named Default.htm.

Step 2: Get the data

In this step, you will write the code to open an ADO Recordset and prepare to send it to the client.

Open the file XMLResponse.asp with a text editor, such as Windows Notepad, and insert the following code:


     <%@ language="VBScript" %> 

     <!-- #include file='adovbs.inc' --> 

     <% 
     Dim strSQL, strCon 
     Dim adoRec  
     Dim adoCon  
     Dim xmlDoc  

     ' You will need to change "slqServer" below to the name of the SQL  
     ' server machine to which you want to connect. 
     strCon = "Provider=sqloledb;Data Source=sqlServer;Initial Catalog=Pubs;Integrated Security=SSPI;" 
     Set adoCon = server.createObject("ADODB.Connection") 
     adoCon.Open strCon 

     strSQL = "SELECT Title, Price FROM Titles ORDER BY Price" 
     Set adoRec = Server.CreateObject("ADODB.Recordset") 
     adoRec.Open strSQL, adoCon, adOpenStatic, adLockOptimistic, adCmdText

Be sure to change the value of the Data Source parameter in strCon to the name of your Microsoft SQL Server computer.
Keep the file open and go on to the next step.

Step 3: Send the data

Now that you have a Recordset, you need to send it to the client by saving it as XML to the ASP Response object.

Add the following code to the bottom of XMLResponse.asp:
```
 Response.ContentType = "text/xml" 
 Response.Expires = 0 
 Response.Buffer = False 


 Response.Write "<?xml version='1.0'?>" & vbNewLine 
 adoRec.save Response, adPersistXML 
 adoRec.Close 
 Set adoRec=Nothing 
 %> 
```
Notice that the ASP Response object is specified as the destination for the Recordset Save method. The destination of the Save method can be any object that supports the IStream interface, such as an ADO Stream object, or a file name that includes the complete path to which the Recordset is to be saved.
Save and close XMLResponse.asp before going to the next step. Also copy the adovbs.inc file from C:\Program Files\Common Files\System\Ado folder to the same folder where you have the XMLResponse.asp file.

Step 4: Receive and display the data

In this step, you will create an HTML file with an embedded RDS.DataControl object that points at the XMLResponse.asp file to get the Recordset.

Open default.htm with a text editor, such as Windows Notepad, and add the following code. Replace "sqlserver" in the URL with the name of your server computer.


 <HTML> 
 <HEAD><TITLE>ADO Recordset Persistence Sample</TITLE></HEAD> 
 <BODY> 

 <TABLE DATASRC="#RDC1" border="1"> 
 <TR> 
 <TD><SPAN DATAFLD="title"></SPAN></TD> 
 <TD><SPAN DATAFLD="price"></SPAN></TD> 
 </TR> 
 </TABLE> 

 <OBJECT CLASSID="clsid:BD96C556-65A3-11D0-983A-00C04FC29E33" ID="RDC1"> 
 <PARAM NAME="URL" VALUE="XMLResponse.asp"> 
 </OBJECT> 

 </BODY> 
 </HTML>

Close the default.htm file and save it to the same folder where you saved XMLResponse.asp.
Using Internet Explorer 4.0 or later, open the URL https://<sqlserver>/XMLPersist/default.htm and observe the results. The data is displayed in a bound DHTML table.
Now open the URL https://<sqlserver>/XMLPersist/XMLResponse.asp and observe the results. The XML is displayed.

XML Recordset persistence scenario

Step 1: Set up the application

Step 2: Get the data

Step 3: Send the data

Step 4: Receive and display the data

Additional resources