ecause ASP programming is all about database access to generate dynamic data, you've probably found yourself writing the same old routines to access and fetch data. The following sets of data access routines and auxiliary functions have become part of my data access arsenal. They are tried and tested and make ASP coding a lot easier. In addition, they allow you to write modular code, making maintenance and enhancements a snap.

Fetching a Recordset
The most common of data access routines deal with fetching a recordset of data using a SQL SELECT statement or a call to a stored procedure. You can use any combination of ADO Connection, Command, and Recordset objects to this. I have found that there are only two kinds of recordsets ever fetched back from a database for use in an ASP page—and only one kind should be used. I am talking about a disconnected recordset and its other variation: the connected recordset. All you need to fetch a recordset full of data is a valid connection string and SQL statement. Given these, the simplest kind of recordset you can fetch is by using these lines of code:

 ' - open a connection by using the valid connection string
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnString
' - Create a Recordset by Executing the SQL statement - strSQL
Set objRS = objConn.Execute (strSQL)

The above code results in a forward only, read only recordset that has an active connection still open to the database. When you are processing the recordset, the connection is still alive and open to the database. And when you have finished, you need to close both the recordset and the connection:

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

If you are using the above piece of code in several different ASP pages or locations, you can make your code modular and easy to read and maintain by converting all the code into a function call. Not only does this make it easier to write code (you need to write one line of code each time, rather than four or five) it is also easier to modify and enhance. I use this function:

'-----------------------------------------
'	Sub: GetConnectedRecordset
'-----------------------------------------
Sub  GetConnectedRecordSet(objConn, objRS, byval strConnString, byval strSQL)
	' - Returns a Connected Recordset within the variable objRS
	' - The connection object returned is objConn
	' - strConnString is the connection string to use 
	' - strSQL is the valid SQL statement to execute to 
' - obtain the recordset 

' - open a connection by using the valid connection string
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnString
' - Create a Recordset by Executing the SQL statement - strSQL
Set objRS = objConn.Execute (strSQL)


End Sub

Each time you have to fetch a recordset, you now need to provide only one line of code:

GetConnectedRecordSet objConn, objRS, strConnString, strSQL

Remember, you still have to close the Connection object yourself and set it to nothing because you have been provided a "connected Recordset."

The GetRecordSet Alternative

Introduction	Utilities for Creating Valid SQL Statements
The GetRecordSet Alternative