Connectionless Recordsets (1)

Connectionless recordsets, persistant recordsets, creatable recordsets, call it whatever you want, all these names refer to the same object and that is ADO Recordset object. The most important feature provided by the ADO is the introduction of the principle that recordsets are creatable objects. With ADO you can access any sort of structured data. Recordset is the most used object in the ADO object library, it is used to temporarily store the set of records (known as recordset) that is returned by a SQL query. Recordsets have a cursor that indicates the current pointer position within the recordset. Whenever you employ ADO, you are using the recordsets to carry data back and forth. Recordsets always contains data, but this data does not necessarily match a table’s records.

Note that connectionless recordset is not same as the disconnected recordset. Making the recordset structure externally creatable means that you can create a new recordset object anytime and anywhere in your code, and you can use it without a connection to a database. A disconnected recordset supports a static, client-side cursor that automates downloading the records on the client side. You can have disconnected recordsets with RDO but you can’t have connectionless recordsets.

A connectionless recordset is a recordset whose fields have been defined on the fly by the application to match the structure of the information you want it to manage. Previously this capability was reserved for the data object model such as ADO 1.x, RDO, or DAO.

For reader’s convenience, we are including here an example that demonstrates the display of data with a Recordset. There are two ways to do this, one is to create a connection object and then create a recordset object, and the other way is to create a recordset object without explicitly creating a connection object. Both ways are demonstrated below:

DISPLAYING DATA WITH A RECORDSET (USING A CONNECTION OBJECT)

Recordset is quite useful in real world applications. If anything, there are too many ways to do the same thing. The example below uses an explicit Connection object and is written to be used in Active Server Pages.

Set conn = server.createobject(“ADODB.Connection”)
Set objRec = server.createobject(“ADODB.Recordset”)
Conn.open “DSN=myDB;UID=sa;Password=;”
objRec.ActiceConnection = conn
objRec.open “select * from table1”
while not objRec.EOF
            Response.write objRec(“fname”) & “ ”
            Response.write objRec(“Address”)  & “<br>”
            ObjRec.MoveNext
Wend
ObjRec.Close
Conn.Close

DISPLAYING DATA WITH A RECORDSET (WITHOUT  A CONNECTION OBJECT)

StrConnect = “DSN=myDB;UID=sa;Password=;”
Set objRec = server.createobject(“ADODB.Recordset”)
objRec.Open “select * from table1”, strConnect, adopenkeyset, adlockoptimistic
while not objRec.EOF
            Response.write objRec(“fname”)
            Response.write objRec(“Address”) & “<br>”
            objRec.MoveNext
wend
objRec.Close

To use the above code in Visual Basic, simply change the syntax of the statement in which the objects are created like replace “server.createobject” with “createobject”, the above syntax is specific to the ASP only.