The GetRecordSet Alternative
An alternative to the GetConnectedRecordSet function returns a disconnected recordset (one without an active connection). The function opens a connection, fetches a recordset, and immediately closes the connection. This lets you take your time processing the recordset without having to worry about tying up precious database connections. Use this code to obtain a disconnected recordset. Note: Because this is my standard technique for returning a recordset in an ASP page, I do not qualify the name "disconnected" within the function name. I call it "GetRecordSet"

'-----------------------------------------
'	Function: GetRecordSet
'-----------------------------------------
Function GetRecordSet(byval strConnectionString, byval strSQL)
	' -- given a valid SQL statement, returns a recordset with values
	
	const adUseClient = 3
	const adOpenForwardOnly = 0
	const adLockBatchOptimistic = 4

	
	' Create instance of connection object and then open the
	' connection.
	Dim objConn, objRS

	Set objConn = Server.CreateObject("ADODB.Connection")
	objConn.Open strConnectionString

	' Create instance of recordset object and open the
	' recordset object against a table.
	Set objRS = Server.CreateObject("ADODB.Recordset")

	' Setting the cursor location to client side is important
	' to get a disconnected recordset.
	objRS.CursorLocation = adUseClient
	objRS.Open strSQL, _
         objConn, _
         adOpenForwardOnly, _
         adLockBatchOptimistic

	' Disconnect the recordset.
	Set objRS.ActiveConnection = Nothing

	' -- Close the connection
	objConn.Close

	set objConn = Nothing
	
	' -- return the recordset
	Set GetRecordSet = objRS

End Function

As you can see, this function is simple to code and use. To call this function, provide a valid connection string and a valid SQL statement and it returns a recordset object. Here's how to use this code in your ASP page:

set objRS = GetRecordSet(strConnString, strSQL)

Executing a Query
Sometimes you may be accessing a database and executing a valid SQL statement that does not return a recordset. These are usually the action queries that begin with INSERT, UPDATE, or DELETE. In this case, you don't want to return a recordset, you want to execute a query. For this I rely on another sub routine: ExecQuery. As its name suggests, all it does is execute a query. This is the code:

'-----------------------------------------
'	Sub: ExecQuery
'-----------------------------------------
Sub ExecQuery(byval strConnectionString, byval strSQL)
	' -- given a valid Action SQL statement, execute it
	
	' Create instance of connection object and then open the
	' connection.
	Dim objConn

	Set objConn = Server.CreateObject("ADODB.Connection")
	objConn.Open strConnectionString

	' -- Execute the action query
	objConn.Execute strSQL
	
	' -- Close the connection
	objConn.Close
	set objConn = Nothing
	
End Sub

To call this function to execute an action query, you would use:

ExecQuery strConnString, strSQL

Introduction

Utilities for Creating Valid SQL Statements

Introduction	Utilities for Creating Valid SQL Statements
The GetRecordSet Alternative