Clone Method Example (ADO)


		Contents Index Topic Contents

Clone Method Example

This example uses the Clone method to create copies of a Recordset and then lets the user position the record pointer of each copy independently.

Public Sub CloneX()

	Dim arstStores(1 To 3) As ADODB.Recordset
	Dim intLoop As Integer
	Dim strSQL As String
	Dim strCnn As String
	Dim strMessage As String
	Dim strFind As String

	' Assign SQL statement and connection string to variables.
	strSQL = "SELECT stor_name FROM Stores " & _
		"ORDER BY stor_name"
	strCnn = "driver={SQL Server};server=srv;" & _
		"uid=sa;pwd=;database=pubs"

	' Open recordset as a static cursor type recordset.
	Set arstStores(1) = New ADODB.Recordset
	arstStores(1).CursorType = adOpenStatic
	arstStores(1).LockType = adLockBatchOptimistic
	arstStores(1).Open strSQL, strCnn, , , adCmdText

	' Create two clones of the original Recordset.
	Set arstStores(2) = arstStores(1).Clone
	Set arstStores(3) = arstStores(1).Clone

	Do While True

		' Loop through the array so that on each pass, 
		' the user is searching a different copy of the 
		' same Recordset.
		For intLoop = 1 To 3

			' Ask for search string while showing where 
			' the current record pointer is for each Recordset.
			strMessage = _
				"Recordsets from stores table:" & vbCr & _
				"  1 - Original - Record pointer at " & _
				arstStores(1)!stor_name & vbCr & _
				"  2 - Clone - Record pointer at " & _
				arstStores(2)!stor_name & vbCr & _
				"  3 - Clone - Record pointer at " & _
				arstStores(3)!stor_name & vbCr & _
				"Enter search string for #" & intLoop & ":"
			strFind = Trim(InputBox(strMessage))
			If strFind = "" Then Exit Do

			' Find the search string; if there's no 
			' match, jump to the last record.
			arstStores(intLoop).Filter = "stor_name >= '" & strFind & "'"
			If arstStores(intLoop).EOF Then
				arstStores(intLoop).Filter = adFilterNone
				arstStores(intLoop).MoveLast
			End If

		Next intLoop

	Loop

	arstStores(1).Close
	arstStores(2).Close
	arstStores(3).Close

End Sub

VBScript Version

The following is the same example written in VBScript to be used in an Active Server Page (ASP). To view this example, you need to create a system Data Source Name (DSN) called AdvWorks for the Microsoft Access database file AdvWorks.mdb, which is installed with Microsoft Internet Information Server and located at C:\InetPub\ASPSamp\AdvWorks. Locate the file adovbs.inc and place it in the directory you plan to use. Cut and paste the following code to Notepad or another text editor and save it as Clone.asp. You can view the result in any client browser.

To exercise the example, change the line RsCustomerList.Source = "Customers" to RsCustomerList.Source = "Products" to count a larger table.

<!-- #Include file="ADOVBS.INC" -->
<% Language = VBScript %>
<HTML><HEAD>
<TITLE>ADO 1.5 Clone Method</TITLE>
</HEAD><BODY> <Center>
<H3>ADO Clone Method</H3>
<!--- ADO Connection Object used to create recordset-->
<% 
'Create and open Connection object
Set OBJdbConnection = Server.CreateObject("ADODB.Connection") 
OBJdbConnection.Open "AdvWorks" 
'Create and open Recordset object
Set RsCustomerList = Server.CreateObject("ADODB.Recordset")
RsCustomerList.ActiveConnection = OBJdbConnection
RsCustomerList.CursorType = adOpenKeyset
RsCustomerList.LockType = adLockOptimistic
RsCustomerList.Source = "Customers"
RsCustomerList.Open
%>
<HR>
<!-- Loop through Customers Table adding 1 to the Counter variable each pass -->
<% 
	Set MyRecordset = RSCustomerList.Clone
	Counter = 0
	Do Until MyRecordset.EOF
		Counter = Counter + 1
		MyRecordset.MoveNext
	Loop
%>
<!-- Display Results -->
<H3>There Are <%=Counter %> Records in the Customers Table</H3>
<BR><HR>
<H4>Location of DSN Datbase</H4>
<%' Show location of DSN data source
Response.Write(OBJdbConnection)
%>
<HR></Center></BODY></HTML>