How To Create ADO Disconnected Recordsets in ASP Using VBScript and JScript

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q289531

Expand all | Collapse all

SUMMARY

This article shows one technique to create ActiveX Data Objects (ADO) disconnected recordsets in Active Server Pages (ASP) when programming with Microsoft VBScript and Microsoft JScript. This article assumes that the reader is familiar with ADO and ASP.

Back to the top

MORE INFORMATION

One of the ASP guidelines is to release ADO objects as soon as possible in order to release the system resources used by these objects. The disconnected recordset feature of ADO allows recordsets to exist without having an active connection; this can save database server resources and improve scalability. ADO disconnected recordsets require client-side cursors to be used, which can be done by setting the CursorLocation property of the Connection object to adUseClient.

The following sample code uses the Microsoft OLEDB Provider for SQL Server (SQLOLEDB) to connect to the sample Northwind database that comes with the SQL Server installation. To run the samples, modify the connection string and SELECT statement to suit your environment. Also, set the correct path for the ADO constants Include files.

Disconnected Recordsets in VBScript

<%@Language="VBScript"%>
<!-- Include file for VBScript ADO Constants -->
<!--#include File="adovbs.inc"-->
<%
	' Connection string.
	strCon = "Provider=sqloledb;Data Source=myServer;Initial Catalog=Northwind;User Id=myUser;Password=myPassword"
		   
	' Create the required ADO objects.
	Set conn = Server.CreateObject("ADODB.Connection")
	Set rs = Server.CreateObject("ADODB.recordset")
		   
	' Open the connection.
	conn.Open strCon
	
	' Retrieve some records.
	strSQL = "Select * from Shippers"
	rs.CursorLocation = adUseClient
	rs.Open strSQL, conn, adOpenStatic, adLockOptimistic

	' Disconnect the recordset.
	Set rs.ActiveConnection = Nothing
		
	' Release the connection.
	conn.Close

	' Check the status of the connection.
	Response.Write("<BR> Connection.State = " & conn.State)

	Set conn = Nothing
	
	' Use the diconnected recordset here.

	' Release the recordset.
	rs.Close
	Set rs = Nothing
%>

Note that the recordset is disconnected by setting the ActiveConnection property to Nothing.

Disconnected Recordsets in JScript

<%@Language="JScript"%>
<!-- Include file for JScript ADO Constants -->
<!--#include File="adojavas.inc"-->
<%
	// Connection string.
	var strCon = "Provider=sqloledb;Data Source=myServer;Initial Catalog=Northwind;User Id=myUser;Password=myPassword";
		   
	// Create the required ADO objects.
	conn = Server.CreateObject("ADODB.Connection");
	rs = Server.CreateObject("ADODB.recordset");
		   
	// Open the connection.
	conn.Open(strCon);
	
	// Retrieve some records.
	var strSQL = "Select * from Shippers";
	rs.CursorLocation = adUseClient;
	rs.Open(strSQL, conn, adOpenStatic, adLockOptimistic);

	// Disconnect the recordset.
	DisconnectRecordset(rs);
	
	// Release the connection.
	conn.Close();

	// Check the status of the connection.
	Response.Write("<BR> Connection.State = " + conn.State);
	
	conn = null;
		
	// Use the diconnected recordset here.
	
	// Release the recordset.
	rs.Close();
	rs = null;
%>

<SCRIPT LANGUAGE="VBScript" RUNAT="SERVER">
Sub DisconnectRecordset(rs)
	Set rs.ActiveConnection = Nothing
End Sub
</SCRIPT>

NOTE: In the preceding code, you cannot replace the following line of code

	DisconnectRecordset(rs);

with one of the following lines of code to create a disconnected recordset:

	rs.ActiveConnection = null;

-or-

	delete(rs.ActiveConnection);

There is an alternate way to create a disconnected recordset. JScript does not have a keyword similar to the Nothing keyword in VBScript, which is used to release the ActiveX objects. To achieve this, you could use a technique similar to the one given at the following Web site:

http://www.netspace.net.au/~torrboy/code/jargutil (http://www.netspace.net.au/~torrboy/code/jargutil)

In this case, the sample code might resemble the following:

	var oUtil = Server.CreateObject("Torrboy.JArgUtility");
	rs.ActiveConnection = oUtil.Nothing;

Back to the top

REFERENCES

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:

184397 (http://support.microsoft.com/kb/184397/EN-US/ ) How To Create ADO Disconnected Recordsets in VBA/C++/Java

190717 (http://support.microsoft.com/kb/190717/EN-US/ ) INFO: Disconnected Recordsets with ADO or RDS

252482 (http://support.microsoft.com/kb/252482/EN-US/ ) BUG: ADO Disconnected Recordset That Uses Parameterized Query Is Not Disconnected by SQL Server

Back to the top

APPLIES TO

Microsoft ActiveX Data Objects 2.0
Microsoft ActiveX Data Objects 2.1
Microsoft ActiveX Data Objects 2.5
Microsoft ActiveX Data Objects 2.6
Microsoft Active Server Pages 4.0

Back to the top

kbdatabase kbhowto KB289531

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top