What we are going to discuss in this article is Recordset object but you might think why we have chosen to name the article as ActiveX Data Objects (ADO), this is because you must have an idea of ADO technology in order to understand the bits and bytes of Recordset object So, we will briefly describe the ADO technology before jumping into the details of Recordset object. The intended audience of this article includes VB as well as ASP programmers, you must bear it in your mind that you can get more detailed information about the ADO or any related technology on the Microsoft® site, the only purpose of this article is to describe the new technology in simple and plain English so that more people can access and read this article.
I have noticed that many new programmers prefer to search sites other than the Microsoft® site for the help on different newer technologies, may be because these articles are more easily available to the programmers and they don’t have to pay for these articles, moreover, the sample code is provided free of cost unlike Microsoft® where only registered users can access the code related to the newest technologies. So, instead of digging deep into the useless details of why this article was written, let’s see, what’s so important about the ADO technology. ActiveX® Data Objects ADO is the object-based interface that provides a logical set of objects you can access from code. These objects are:
Object
Functionality
Connection
Manages the connection with the data source
Command
Defines the commands that will be executed against the data source
Recordset
Contains the data that is retrieved from the data source
These objects present an interface in the form of properties and methods that can be queried and manipulated. ADO was specifically developed to be small, lightweight, fast, and feature complete - everything you need when you are programming either for the database applications or the Internet.
An important thing in ADO is that the objects in this model are not dependant on one another. This means that you can create instances of objects independently of one another, for example, you can create a Recordset object without creating a connection object. Unlike the older technologies, ADO is more flexible, ADO code is easier to write, read and maintain. ADO is built on top of OLE DB and is capable of accessing any sort of data that is wrapped and exposed by an appropriate OLE DB provider. Connectionless Recordsets
Connectionless recordsets, persistent 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.ActiveConnection = conn objRec.open "select * from table1"
while not objRec.EOF Response.write objRec("fname") & " " Response.write objRec("Address") & " " 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") & " " 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.
Now that you have seen the examples demonstrating the usage of Recordset objects with the database, let’s concentrate on the issue of connectionless recordsets or rather should I say "Creatable Recordsets". Below is shown the code that creates a brand new recordset that has no relationship to an OLE DB data source. The code generates a recordset that reads drive information through the FileSystem Scripting Object. So, you will learn not only how to create a new recordset (connectionless recordset) but also, how to use the FileSystem Scripting Object. The code shown below is written in VBScript. The ASP version is also provided with this article. See the related documents.
CODE
'============================================================ 'Name: ConnectionlessRS.vbs 'Description: Shows a connection less recordset with it’s own fields added. '============================================================
'Fills the recordset out with drive information for each drv in drives rst.AddNew if drv.isready then rst.Fields("Root").value = drv.DriveLetter rst.Fields("Volume").value = drv.VolumeName rst.fields("Type").value = drv.DriveType rst.Fields("FileSystem").value = drv.FileSystem rst.Fields("FreeSpace").value = drv.FreeSpace/1024 end if next
'Displays the recordset s = "" rst.movefirst while not rst.EOF for each fld in rst.Fields s = s & Pad(rst.Fields(fld.name),14) & vbtab next s = s & vbcrlf rst.MoveNext wend
msgbox s
'============================================================ 'Pad(str, numChars) 'Pads the specified string with the specified trailing blanks '============================================================ Function Pad(str, numChars) str = str & space(numChars) Pad= Left(str,numchars) end function
CODE DETAILS
Two objects are used in this code, one is the FileSystem Scripting Object and the other is the Recordset object. Drives property of the FileSystem Object is used to get the collection of all the drives in your computer. The next step is to create new fields of your recordset object. We have used the client side cursor during the process. Append property of the fields collection is used to add new fields in the recordset. Once the fields are appended, we scroll through the drives collection and add new record against each drive, each record contains information about the individual drive. Lastly, we display the drive information using the msgbox function. Trailing blanks are added to the strings simply to display the information more clearly on the screen. To display the same information in ASP, we use Response.Write method while navigating through the recordset. Also, note that to use the above code in ASP, you will have to create the objects using the "Server.CreateObject()" method.
Contact Us
Contact us for any help regarding the article or sample code, our email address is:
ASP Database Fundamentals (Part 2)
